Returning set of rows from plpgsql function.

Question

I would like to return table from plpgsql function.

Here is my code.

    CREATE FUNCTION test() RETURNS my_table AS
    $BODY$DECLARE
        q4 my_table;
    BEGIN
        q4 := SELECT * FROM my_table;
        RETURN q4;
    END;$BODY$
    LANGUAGE sql;

I am getting following error:

    Error: ERROR:  syntax error at or near "SELECT"
    LINE 5:  q4 := SELECT * FROM my_table;

I started from this questions/tutorials. https://dba.stackexchange.com/questions/35721/declare-variable-of-table-type-in-pl-pgsql && http://postgres.cz/wiki/PL/pgSQL_%28en%29

The idea is that I need to assign this query to a variable. This is only small part of function that I would like to create.

The second problem will be how to iterate through that set and make some mathematical operations and assigning value to some field of that table. However firstly I would like to solve this problem.

Answer 1

PostgreSQL has not table variables. So you cannot to return table via any variable. When you create any table, then PostgreSQL creates composite type with same name. But it isn't table type - it is composite type - record.

CREATE TABLE xx(a int, b int);

CREATE OR REPLACE FUNCTION foo()
RETURNS xx AS $$
DECLARE v xx;
BEGIN
  v := (10,20);
  RETURN v;
END;
$$ LANGUAGE plpgsql;

Function foo returns composite value - it is not a table. But you can write some function that returns set of composite values - it is table.

CREATE OR REPLACE FUNCTION foo(a int)
RETURNS SETOF xx AS $$
DECLARE v xx;
BEGIN
  FOR i IN 1..a LOOP
    v.a := i; v.b := i+1;
    RETURN NEXT v;
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;

postgres=# SELECT * FROM foo(3);
┌───┬───┐
│ a │ b │
╞═══╪═══╡
│ 1 │ 2 │
│ 2 │ 3 │
│ 3 │ 4 │
└───┴───┘
(3 rows)

When the result is based on query, you can use RETURN QUERY. It is little bit faster, shorter, more readable than FOR IN SELECT and RETURN NEXT:

CREATE OR REPLACE FUNCTION foo2(a int)
RETURNS SETOF xx AS $$
BEGIN
  RETURN QUERY SELECT * FROM xx
                  WHERE xx.a = foo2.a;
  RETURN;
END;
$$ LANGUAGE plpgsql;

Use these functions carefully. They are a black box for optimizer, so optimizer cannot to optimize query in function and outer query together, it must to optimize these queries separately, and this shouldn't be effective for some complex queries. When outer query is simple, then it should not be a problem.

Answer 2

CREATE FUNCTION test() 
RETURNS my_table AS
$BODY$
DECLARE
    q4 my_table;
BEGIN
    -- add brackets to get a value 
    -- select row as one value, as q4 is of the type my_table
    -- and limit result to one row
    q4 := (SELECT my_table FROM my_table ORDER BY 1 LIMIT 1);
    RETURN q4;
END;$BODY$
-- change language to plpgsql
LANGUAGE plpgsql;

• You cannot use variables in sql functions, use plpgsql.
• You can assign single value to a variable, while select query returns set of rows.
• You have to select a row as one value, as the variable is of composite type.

---

Example of using a loop:

DROP FUNCTION test();
CREATE FUNCTION test() 
-- change to SETOF to return set of rows, not a single row
RETURNS SETOF my_table AS
$BODY$
DECLARE
    q4 my_table;
BEGIN
    FOR q4 in
        SELECT * FROM my_table
    LOOP
        RETURN NEXT q4;
    END LOOP;
END;$BODY$
LANGUAGE plpgsql;

SELECT * FROM test();

Read the documentation about Returning From a Function