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@xxxxxxxxxxx
