Postgres function with output parameters and resultset

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;

Responses

Browse pgsql-general by date

  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”?