From: | Arulalan Narayanasamy <arulalan(dot)narayanasamy(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Postgres function with output parameters and resultset |
Date: | 2018-07-20 21:19:35 |
Message-ID: | CA+Ux585jj_8q-3LeD3TP6T7wXTCi0CZszRyNcCAuvcQ6hpDgVQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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!!
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;
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2018-07-20 21:36:46 | Re: Postgres function with output parameters and resultset |
Previous Message | Praveen Kumar | 2018-07-20 19:42:26 | In certain cases, can UPDATE transactions fail rather than block waiting for “FOR UPDATE lock”? |