pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedplpgsql function returning SETOF RECORD Question
2+ messages / 2 participants
[nested] [flat]
* plpgsql function returning SETOF RECORD Question
@ 2004-02-23 13:21 jbduffy@ntlworld.com
0 siblings, 1 reply; 2+ messages in thread
From: jbduffy@ntlworld.com @ 2004-02-23 13:21 UTC (permalink / raw)
To: pgsql-sql
Hi
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';
PostgreSQL 7.3.2-3 on Red Hat 9.
Regards
John Duffy
-----------------------------------------
Email provided by http://www.ntlhome.com/
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: plpgsql function returning SETOF RECORD Question
@ 2004-02-29 23:32 Joe Conway <mail@joeconway.com>
parent: jbduffy@ntlworld.com
0 siblings, 0 replies; 2+ messages in thread
From: Joe Conway @ 2004-02-29 23:32 UTC (permalink / raw)
To: jbduffy@ntlworld.com; +Cc: pgsql-sql
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
useful for functions that return result sets, but any function can be
used.) This acts as though its output were created as 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's composite return type. If the function has been defined as
returning the record data type, then an alias or the key word AS must
be present, followed by a column definition list in the form
( column_name data_type [, ... ] ). The column 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 * FROM distributors WHERE did = $1;
' LANGUAGE SQL;
SELECT * FROM distributors_2(111) AS (f1 int, f2 text);
f1 | f2
-----+-------------
111 | Walt Disney
HTH,
Joe
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-02-29 23:32 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-23 13:21 plpgsql function returning SETOF RECORD Question jbduffy@ntlworld.com
2004-02-29 23:32 ` Joe Conway <mail@joeconway.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox