Declare a variable of a table type in postgres

Question

I need to write a stored procudere like the following:

CREATE OR REPLACE FUNCTION foo() RETURNS TABLE(user_id integer, count bigint) AS $$
    some_array integer[];
    ret_val __WHAT_TYPE_;
BEGIN
    FOR i IN 1 .. array_upper(some_array, 1)
    LOOP
        //modify the ret_val
    END LOOP;
    RETURN ret_val;
END $$
LANGUAGE plpgsql;

But I don't know what type of ret_val I should declare?

I think you need to declare it as record

user330315
– user330315

2016-01-25 09:17:37 +00:00
Commented Jan 25, 2016 at 9:17 — user330315
– user330315, Commented Jan 25, 2016 at 9:17

klin · Accepted Answer · 2016-01-25 09:32:27Z

6

In a function returning a table you do not need a variable for returned value. The columns of the table are treated as OUT parameters. You can assign values to them and use RETURN NEXT, e.g.:

CREATE OR REPLACE FUNCTION foo() 
RETURNS TABLE(user_id integer, counts bigint) AS $$
DECLARE
    i integer;
BEGIN
    FOR i IN 1..4
    LOOP
        user_id = i;
        counts = i* i;
        RETURN NEXT;
    END LOOP;
END $$
LANGUAGE plpgsql;

SELECT * FROM foo();

 user_id | counts 
---------+--------
       1 |      1
       2 |      4
       3 |      9
       4 |     16
(4 rows)

answered Jan 25, 2016 at 9:32

klin

123k15 gold badges240 silver badges262 bronze badges

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

2 Comments

St.Antario Over a year ago

Very interesting, thanks much. But could'nt you explain what OUT parameters are in a nutshell? I just haven't come across with that term before...

klin Over a year ago

Read about OUT parameters here

Collectives™ on Stack Overflow

Declare a variable of a table type in postgres

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