Postgresql: Is there a way to return both table data and output parameters from a stored procedure as in Microsoft SQL Server

Question

In Microsoft Sql Server, both output parameters and select results can be used in cohesion to return data back to the client. This comes handy in use-cases like paging, where you need to return a main set along with associated meta-data like total page count. All the examples I have seen so far in Postgres either return data via output parameters or via table results. I am unable to create a procedure with both together as it fails with the following error: ERROR: OUT and INOUT arguments aren't allowed in TABLE functions

Here is what I tried:

CREATE OR REPLACE FUNCTION fn_Test(out p_count int)
RETURNS TABLE (CustomerId int, CustomerName varchar(50))
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN

    INSERT INTO Customers(CustomerName, Address, Area, Phonenumber, Email, communicationpreference)
    VALUES ('Julie Yellow', 'JY Ad', 'JY Ar', 'JV0987654', '[email protected]', 1);

    SELECT COUNT(*) FROM CUSTOMERS INTO  p_count;
    SELECT CustomerId, CustomerName FROM Customers;
EXCEPTION WHEN OTHERS THEN
    RAISE exception '% %', SQLERRM, SQLSTATE;
END;
$BODY$

Can't they go together? Is there any alternative approach in Postgresql?

Please advice

Vao Tsun · Accepted Answer · 2017-12-13 12:22:06Z

3

The answer is no - either you create function returns something or that something is defined by OUT parameters.

https://www.postgresql.org/docs/current/static/plpgsql-control-structures.html

If you declared the function with output parameters, write just RETURN with no expression. The current values of the output parameter variables will be returned.

ephasis mine.

https://www.postgresql.org/docs/current/static/sql-createfunction.html

Also OUT and INOUT arguments cannot be used together with the RETURNS TABLE notation

You can play with raise info or notify channel though

answered Dec 13, 2017 at 12:22

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

Comments

score 2 · Accepted Answer · 2017-12-13 12:35:42Z

You can simply include the count in the final query.

But to return a query result from PL/pgSQL you need to use return query

CREATE OR REPLACE FUNCTION fn_test()
   RETURNS TABLE (customerid int, customername varchar(50), newcount int)
AS $BODY$
BEGIN

    INSERT INTO customers(customername, address, area, phonenumber, email, communicationpreference)
    VALUES ('Julie Yellow', 'JY Ad', 'JY Ar', 'JV0987654', '[email protected]', 1);

    return query
      SELECT customerid, customername, count(*) over () as newcount
      FROM customers;
EXCEPTION WHEN OTHERS THEN
    RAISE exception '% %', SQLERRM, SQLSTATE;
END;
$BODY$
LANGUAGE plpgsql;

The language name is an identifier, do not put it into single quotes.

Alternatively you could return multiple results as refcursors from your function. But this is a bit clumsy to use with most SQL clients:

CREATE OR REPLACE FUNCTION fn_test()
   returns setof refcursor
AS $BODY$
DECLARE
   c1 refcursor;
   c2 refcursor;
BEGIN

    INSERT INTO customers(customername, address, area, phonenumber, email, communicationpreference)
    VALUES ('Julie Yellow', 'JY Ad', 'JY Ar', 'JV0987654', '[email protected]', 1);

    open c1 for 
      select count(*) from customers;
    return next c1;

    open c2 for 
      SELECT customerid, customername
      FROM customers;
    return next c2;

EXCEPTION WHEN OTHERS THEN
    RAISE exception '% %', SQLERRM, SQLSTATE;
END;
$BODY$
LANGUAGE plpgsql;

vbilopav · Accepted Answer · 2021-02-26 12:13:44Z

What works for me in situations like this is to use JSON as a return type. Your example:

CREATE OR REPLACE FUNCTION fn_Test()
RETURNS JSON
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN

    INSERT INTO Customers(CustomerName, Address, Area, Phonenumber, Email, communicationpreference)
    VALUES ('Julie Yellow', 'JY Ad', 'JY Ar', 'JV0987654', '[email protected]', 1);

    SELECT CustomerId, CustomerName FROM Customers;

    RETURN json_build_object(
        'count', (SELECT COUNT(*) FROM CUSTOMERS),
        'customers', (
            SELECT json_agg(
                json_build_object('customerId', CustomerId, 'customerName', CustomerName)
            ) 
            FROM Customers
        )
    );
EXCEPTION WHEN OTHERS THEN
    RAISE exception '% %', SQLERRM, SQLSTATE;
END;
$BODY$

Collectives™ on Stack Overflow

Postgresql: Is there a way to return both table data and output parameters from a stored procedure as in Microsoft SQL Server

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related