How to return a table along with 2 varchar values from a Postgres function

Question

Currently migrating Oracle database to Postgres and I have come across an use case where I am stuck. In Oracle I have a procedure which returns 3 values

The resultset from a SELECT query (This is an Oracle ref_cursor)
A message
A status

This is the procedure in Oracle

CREATE OR REPLACE PROCEDURE ORA_TEST (
        I_EMP_ACTIVE IN VARCHAR2,
        O_RESULTSET OUT SYS_REFCURSOR
        O_MESSAGE OUT VARCHAR2
        O_STATUS OUT VARCHAR2)
AS
        V_COUNT NUMBER;
BEGIN
        SELECT COUNT(*) INTO V_COUNT FROM EMPLOYEE WHERE EMP_ACTIVE=I_EMP_ACTIVE;
                IF V_COUNT>0
                        OPEN O_RESULTSET FOR SELECT * FROM EMPLOYEE WHERE EMP_ACTIVE=I_EMP_ACTIVE;
                        O_STATUS := 'SUCCESS';
                        O_MESSAGE := 'Search Results found';
                ELSE
                        O_STATUS := 'FAILURE';
                        O_MESSAGE := 'No Search Results found';
                END IF;
END;

Now when I try to create this Procedure in Postgres as a Function, I can return the resultset as a TABLE or SETOF RECORD, but I am not able to return the O_MESSAGE and O_STATUS along with the resultset.

Please note that I cannot return the resultset using a Postgres ref_cursor (This is because we have some issues accessing ref_cursor from APIs, which is why we return the resultset using TABLE, SETOF RECORD or SETOF TYPE).

If there any way to achieve this type of resultset (with a TABLE as well as 2 varchar columns)?

Functions and procedures are two different things. Functions are supposed to return a single value, and used in a situation to assign that single value to something. — EdStevens
– EdStevens, Commented May 21, 2021 at 16:01
Yes I do understand that, but is there any way I can get the desired output? I dont have any issues with either Functions or Procedures, but AFAIK Postgres Procedures cannot have recordset of complex data types as OUT parameters — Goutam Sahoo
– Goutam Sahoo, Commented May 21, 2021 at 16:18

Akhilesh Mishra · Accepted Answer · 2021-05-21 16:05:03Z

0

You can convert it like below...(I am not sure whether it will work with your API or not...)

create or replace function ORA_TEST(I_EMP_ACTIVE varchar) 
returns table(O_RESULTSET refcursor, O_MESSAGE varchar, O_STATUS varchar) as

$$
declare
V_COUNT int;
ref refcursor := 'mycursor';
begin
SELECT COUNT(*) INTO V_COUNT FROM EMPLOYEE WHERE EMP_ACTIVE=I_EMP_ACTIVE;
                IF V_COUNT>0 then 
                        OPEN ref FOR SELECT * FROM EMPLOYEE WHERE EMP_ACTIVE=I_EMP_ACTIVE;
                        O_STATUS := 'SUCCESS';
                        O_MESSAGE := 'Search Results found';
                ELSE
                        O_STATUS := 'FAILURE';
                        O_MESSAGE := 'No Search Results found';
                END IF;
                
return query

select ref, O_STATUS, O_MESSAGE;

end;
$$
language plpgsql;

And call it like below:

BEGIN;
select * from ora_test('1');
FETCH ALL from "mycursor";
COMMIT;

DEMO

edited May 21, 2021 at 16:05

answered May 21, 2021 at 15:59

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Goutam Sahoo Over a year ago

Yes, this would serve my purpose. The only problem is I cannot use refcursor in Postgres as there is a driver limitation at API end and they are not able to get the resultset in the proper format. Because of that reason I need to use either TABLE, SETOF RECORD or SETOF TYPE to return the resultset.

Akhilesh Mishra Over a year ago

AFAIK that is not possible in postgres.

Collectives™ on Stack Overflow

How to return a table along with 2 varchar values from a Postgres function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related