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?

Answer 1

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)