How to return multiple rows from PL/pgSQL function?

Question

I have spent good amount of time trying to figure it out and I haven't been able to resolve it. So, I need your help please.

I am trying to write a PL/pgSQL function that returns multiple rows. The function I wrote is shown below. But it is not working.

CREATE OR REPLACE FUNCTION get_object_fields()
RETURNS SETOF RECORD
AS 
$$
DECLARE result_record keyMetrics;
BEGIN
    return QUERY SELECT department_id into result_record.visits 
    from fact_department_daily 
    where report_date='2013-06-07';
    --return result_record;
END

$$ LANGUAGE plpgsql; 

SELECT * FROM get_object_fields;

It is returning this error:

ERROR: RETURN cannot have a parameter in function returning set;
use RETURN NEXT at or near "QUERY"

Answer 1

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;