Обсуждение: Postgres function with output parameters and resultset
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;
On Friday, July 20, 2018, Arulalan Narayanasamy <arulalan.narayanasamy@gmail.com> 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!!
A function can return a single two dimensional table - so, not directly. Though what you describe here seems like over-engineering. If you really want the count you'd need to add it to the table but the client can count the records in the table easily enough. Errors can be done separately via RAISE and likewise let the client deal with that as usual.
Otherwise I've found JSON to be useful for non-tabular results.
David J.
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