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!!
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;