Postgres function: return multiple tables

Question

Is it possible to return several result sets of different types from postgres function?

Something like:

CREATE OR REPLACE FUNCTION getUserById()
RETURNS setof ???
AS
$$
BEGIN

return query select id, name /* and other columns */ from users where id = 1;
return query select id, phone_number from user_phones where user_id = 1

END
$$
LANGUAGE plpgsql;

I don't want to use joins because several phones for user are possible. Also it would be great to avoid using cursors. It's possible in MS SQL and I want to do the same thing in postgres.

Looks like this is in line what you ask for. http://stackoverflow.com/questions/756689/postgresql-function-returning-multiple-result-sets — Janis S., Apr 19 '16 at 11:41
@JanisS. there is one difference in those examples - all solutions return multiple sets of one type. In my case I have two different data types — user1820686, Apr 19 '16 at 12:19

Janis S. · Accepted Answer · 2021-03-22T07:51:26.490

9

After the seek, could not find any better solutions as use of cursors.

CREATE FUNCTION load_page(_session INT) RETURNS setof refcursor AS
$$
DECLARE c_top_items refcursor;
DECLARE c_shopping_cart refcursor;
BEGIN
    OPEN c_top_items FOR
        SELECT t.name, t.description
        FROM top_item t
        ORDER BY t.popularity DESC
        LIMIT 10;
    RETURN NEXT c_top_items;
    OPEN c_shopping_cart FOR
        SELECT c.product_id, c.product_name, c.quantity
        FROM shopping_cart c
        WHERE c.session_id = _session
        ORDER BY c.id;
    RETURN NEXT c_shopping_cart;
END;
$$ LANGUAGE plpgsql;

And calling:

BEGIN;
SELECT load_page(mySession);
FETCH ALL IN "<server cursor 1>";
FETCH ALL IN "<server cursor 2>";
COMMIT;

edited Mar 22 '21 at 07:51

answered Apr 22 '16 at 12:17

Janis S.

2,526
22
32

Yes, I've seen this article. Anyway, thanks for your researching :) – user1820686 Apr 25 '16 at 11:43
1

The link mentioned in the article is dead – Blip Mar 19 '21 at 09:22

score 2 · Answer 2 · answered Feb 09 '22 at 14:10

this works

CREATE OR REPLACE FUNCTION public.func_test(id integer ,ref1 refcursor,ref2 refcursor)
 RETURNS SETOF refcursor
 LANGUAGE plpgsql
AS $function$
BEGIN
    OPEN ref1 FOR SELECT * FROM table1;
    RETURN NEXT ref1;
    OPEN ref2 FOR SELECT * FROM table2;
    RETURN NEXT ref2;
END;
$function$
;

execute in sql

BEGIN;
    SELECT func_test(69, 'Ref1', 'Ref2');
    FETCH ALL IN "Ref1";
    FETCH ALL IN "Ref2";
COMMIT;

execute in python

import psycopg2

conn = psycopg2.connect(database = "name", user = "user", password = "pass", host = "127.0.0.1", port = "5432")
cur = conn.cursor()
cur.execute("SELECT * FROM func_test(69, 'Ref1', 'Ref2');")
cur.execute('FETCH ALL IN "Ref1";')
tbl1 = cur.fetchall()
print(tbl1)
cur.execute('FETCH ALL IN "Ref1";')
tbl2 = cur.fetchall()
print(tbl2)

Cheers!

سلامتی کپی کارا

jpmc26 · Answer 3 · 2017-02-15T20:40:02.783

I don't want to use joins because several phones for user are possible.

This is not a reason to avoid JOINs in PostgreSQL. At all.

PostgreSQL allows you to aggregate the phone numbers into an array:

CREATE OR REPLACE FUNCTION getUserById()
RETURNS TABLE (
    id INTEGER,
    name TEXT,
    /* and other columns */
    phone_numbers TEXT[]
)
AS
$$
    select
         users.id,
         users.name,
         /* and other columns */
         -- Remove NULL because you get an array containing just NULL
         -- if user_phones doesn't contain any matching rows.
         array_remove(array_agg(user_phones.phone_number), NULL) as phone_numbers
    from users
    left join user_phones on user_phones.user_id = users.id
    where users.id = 1
    -- Note that grouping by a table's primary key allows you to use
    -- any column from that table in the select in PostgreSQL
    group by users.id
    ;
$$
LANGUAGE SQL
STABLE
;

This is much simpler and more intuitive.

You can switch to an inner join if it's okay to give back zero rows for a user without a phone number. In that case, you could drop the array_remove call.

I also added the STABLE specification to the function (since it doesn't modify any table data) and switched it to SQL instead of PGPLSQL (since it's just a single query). This will allow PG to optimize better; in particular, it can inline the query and push filters down in some cases. You may not even need a function, actually.

Mayur Patel · Answer 4 · 2021-08-04T10:44:08.720

CREATE OR REPLACE FUNCTION public.TestReturnMultipleTales
( 
 param_coid integer, 
 ref1 refcursor,
 ref2 refcursor
)
RETURNS SETOF refcursor 
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
ROWS 1000

AS $BODY$
DECLARE
            
BEGIN
  OPEN ref1 FOR SELECT * FROM dbo.tbl1 WHERE coid = param_coid;
  RETURN NEXT ref1;

  OPEN ref2 FOR SELECT * FROM dbo.tbl2 LIMIT 5;
  RETURN NEXT ref2;
END;
$BODY$;

BEGIN;
    SELECT football_players.show_cities_multiple(123456, 'Ref1', 'Ref2');
    FETCH ALL IN "Ref1";
    FETCH ALL IN "Ref2";
COMMIT;

ALSO, CONTACT ME IF WANT TO IMPLEMENT IT INTO .NET WITH C# I RESOLVED THE ISSUE.

score 0 · Answer 5 · answered Dec 14 '21 at 11:59

You can also use string_agg then no need to worry about the null case.

CREATE OR REPLACE FUNCTION getUserById()
RETURNS table (___id int, ___name text, __phone_numbers text)
AS
    $$
    BEGIN
    return query 
        select id, name, STRING_AGG (phone_number,',') phone_numbers 
        from users u  join user_phones up on u.id = up.user_id 
        where u.id = 1
        group by 1,2;
    END
    $$
LANGUAGE plpgsql;

Postgres function: return multiple tables

5 Answers5