How to return multiple rows from PL/pgSQL function?

After fixing the bugs @Pavel pointed out, also define your return type properly, or you have to provide a column definition list with every call.

This call:

SELECT * FROM get_object_fields()

... assumes that Postgres knows how to expand *. Since you are returning anonymous records, you get an exception:

ERROR:  a column definition list is required for functions returning "record"

One way (of several) to fix this is with RETURNS TABLE (Postgres 8.4+):

CREATE OR REPLACE FUNCTION get_object_fields()
  RETURNS TABLE (department_id int) AS 
$func$
BEGIN
   RETURN QUERY
   SELECT department_id
   FROM   fact_department_daily 
   WHERE  report_date = '2013-06-07';
END
$func$ LANGUAGE plpgsql;

Works for SQL functions just the same.

Related:

  • PostgreSQL: ERROR: 42601: a column definition list is required for functions returning "record"

I see more bugs:

first, a SET RETURNING FUNCTIONS call has following syntax

SELECT * FROM get_object_fields()

second - RETURN QUERY forwards query result to output directly. You cannot store this result to variable - it is not possible ever in PostgreSQL now. 

BEGIN
  RETURN QUERY SELECT ....; -- result is forwarded to output directly
  RETURN;   -- there will not be any next result, finish execution
END;

third - these simple functions is better to implement in SQL languages

CREATE OR REPLACE FUNCTION get_object_fields()
RETURNS SETOF RECORD AS $$
SELECT department_id WHERE ...
$$ LANGUAGE sql STABLE;

Tags:

Postgresql

Plpgsql

Recent Posts