This article is half-done without your Comment! *** Please share your thoughts via Comment ***
You can use the RAISE Statements for the report messages and raise errors.
Different level of RAISE statements are INFO, NOTICE, and EXCEPTION.
By default, NOTICE is always returning to the client only. We should use RAISE INFO for our internal query or function debugging.
We should break down our code into smaller parts and add RAISE statement with clock_timestamp().
We can compare the execution time difference between the code blocks and can find a slow running code block.
We can also add parameter and variable to the RAISE statement. We can print the current value of our parameter and variable.
Few examples:
|
1 2 3 4 |
RAISE INFO 'Hello World !'; RAISE NOTICE '%', variable_name; RAISE NOTICE 'Current value of parameter (%)', my_var; RAISE EXCEPTION '% cannot have null salary', EmpName; |
One Practical Demonstration:
Create a table with Sample Data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
CREATE TABLE Employee ( EmpID SERIAL ,EmpName CHARACTER VARYING(50) ,Gender CHAR(1) ,AGE SMALLINT ); INSERT INTO Employee ( EmpName ,Gender ,AGE ) VALUES ('Anvesh','M',27) ,('Mohan','M',30) ,('Roy','M',31) ,('Meera','F',27) ,('Richa','F',26) ,('Martin','M',35) ,('Mahesh','M',38) ,('Paresh','M',22) ,('Alina','F',21) ,('Alex','M',24); |
Sample function for Custome Paging with the use of RAISE:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
CREATE OR REPLACE FUNCTION fn_GetEmployeeData ( Paging_PageSize INTEGER = NULL ,Paging_PageNumber INTEGER = NULL ) RETURNS TABLE ( outEmpID INTEGER ,outEmpName CHARACTER VARYING ,outGender CHAR(1) ,outAge SMALLINT ) AS $BODY$ DECLARE PageNumber BIGINT; DECLARE TempINT INTEGER; BEGIN /* *************************************************************** Construct Custom paging parameter... **************************************************************** */ IF (paging_pagesize IS NOT NULL AND paging_pagenumber IS NOT NULL) THEN PageNumber := (Paging_PageSize * (Paging_PageNumber-1)); END IF; RAISE INFO '%','Construction of Custom Paging Parameter - DONE '|| clock_timestamp(); /* ************************************************ Custome paging SQL Query construction....... ************************************************ */ TempINT := 10000; WHILE (TempINT > 0) LOOP TempINT = TempINT - 1; RAISE INFO '%','The current value of TempINT ' || TempINT; END LOOP; RETURN QUERY SELECT EmpID ,EmpName ,Gender ,Age FROM public.Employee ORDER BY EmpID LIMIT Paging_PageSize OFFSET PageNumber; RAISE INFO '%','Final result set of main query - DONE ' || clock_timestamp(); EXCEPTION WHEN OTHERS THEN RAISE; END; $BODY$ LANGUAGE 'plpgsql'; |
Execute this function and check the query message window:
You will find list of RAISE INFO messages that we mentioned in above function.
|
1 |
SELECT *FROM public.fn_GetEmployeeData(4,2); |
