Thread: plpgsql function returning SETOF RECORD Question

Hi

When I run the function below I recieve an error message saying that column definitions need to be specified for a
functionreturing a type RECORD.
 

I was under the impression that the FOR row IN SELECT... loop would assign a column structure to the RECORD type. Am I
wrongabout this?
 

CREATE FUNCTION test() RETURNS SETOF RECORD AS '
DECLARE row  RECORD;
BEGIN FOR row IN SELECT * FROM dates LOOP   RETURN NEXT row; END LOOP;
 RETURN;
END;
' LANGUAGE 'plpgsql';

PostgreSQL 7.3.2-3 on Red Hat 9.

Regards

John Duffy

-----------------------------------------
Email provided by http://www.ntlhome.com/

jbduffy@ntlworld.com wrote:
> When I run the function below I recieve an error message saying that
> column definitions need to be specified for a function returing a
> type RECORD.
> 
> I was under the impression that the FOR row IN SELECT... loop would
> assign a column structure to the RECORD type. Am I wrong about this?
> 
> CREATE FUNCTION test() RETURNS SETOF RECORD AS ' DECLARE row  RECORD;
>  BEGIN FOR row IN SELECT * FROM dates LOOP RETURN NEXT row; END LOOP;
> RETURN; END; ' LANGUAGE 'plpgsql';

Please look at the docs:  http://www.postgresql.org/docs/current/static/sql-select.html

Specifically, the latter half of this paragraph:  "Function calls can appear in the FROM clause. (This is especially
usefulfor functions that return result sets, but any function can be  used.) This acts as though its output were
createdas a temporary  table for the duration of this single SELECT command. An alias may  also be used. If an alias is
written,a column alias list can also be  written to provide substitute names for one or more attributes of the
function'scomposite return type. If the function has been defined as  returning the record data type, then an alias or
thekey word AS must  be present, followed by a column definition list in the form  ( column_name data_type [, ... ] ).
Thecolumn definition list must  match the actual number and types of columns returned by the  function."
 

and the example further down the page:  CREATE FUNCTION distributors_2(int) RETURNS SETOF record AS '       SELECT *
FROMdistributors WHERE did = $1;  ' LANGUAGE SQL;
 
  SELECT * FROM distributors_2(111) AS (f1 int, f2 text);   f1  |     f2  -----+-------------   111 | Walt Disney

HTH,

Joe