Re: Postgres function with output parameters and resultset

On 07/20/2018 02:19 PM, Arulalan Narayanasamy wrote:
> Hi,
> I need to create a function which should return resultset and output 
> parameters. For example, I need to retrieve all the records from EMP 
> table whose Grade is 'A' as resultset and total number of matched 
> records, Success or Failure flag & Error message as output parameters. 
> Is there a way in Postgres to achieve this? Kindly help!!

Just thinking out loud. Wonder if you could use the multiple cursor 
example shown just above here:

https://www.postgresql.org/docs/10/static/plpgsql-cursors.html#PLPGSQL-CURSOR-FOR-LOOP

Use the one cursor for the result set(NOTE: I did not see where this was 
restricted to 'A' grade)

This is the part I am not sure of. Still here it goes:

1) Create temp table for the message values.

2) Insert the message values in this table.

3) Open a cursor over the table and return it.



> 
> CREATE OR REPLACE FUNCTION TESTFN
> (IN GRADE CHARACTER(01)
> ,OUT EMP_CNT INT
> ,OUT SUCCESS_FG CHARACTER(01)
> ,OUT SQLCD CHARACTER(05)
> ,OUT ERROR_MSG CHARACTER(10))
> RETURNS RECORD
> AS $$
> DECLARE REFCUR REFCURSOR;
> BEGIN
> IF (GRADE IS NULL OR GRADE = '') THEN
> SUCCESS_FG := 'E';
> SQLCD:= '';
> ERROR_MSG := 'GRADE IS NULL OR BLANK';
> RETURN;
> END IF;
> 
> EMP_CNT := SELECT COUNT(*) FROM EMP WHERE GRADE_CD = GRADE;
> 
> EXCEPTION
> WHEN OTHERS THEN
> SUCCESS_FG := 'E';
> SQLCD:= SQLSTATE;
> ERROR_MSG := 'SP EXECUTION ABORTED 1';
> RETURN;
> 
> OPEN REFCUR FOR
> SELECT NAME, AGE, BRANCH, TITLE FROM EMP WHERE GRADE_CD = GRADE;
> 
> EXCEPTION
> WHEN OTHERS THEN
> SUCCESS_FG := 'E';
> SQLCD:= SQLSTATE;
> ERROR_MSG := 'SP EXECUTION ABORTED 2';
> RETURN;
> 
> RETURN REFCUR;
> END;
> $$ LANGUAGE plpgsql;


-- 
Adrian Klaver
adrian.klaver@aklaver.com