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