Thread: Howto automatically define collumn names for a function result.

Hi,
wouldn't it be great to have functions return  "setof something" as 
result where "something" was determined out of the result of a SELECT 
within the function?
like

CREATE OR REPLACE FUNCTION coffee(integer, timestamp, timestamp) RETURNS SETOF
AS
$BODY$   SELECT staff_id, name, room, COUNT(coffee_id) AS cupcount    FROM staff  JOIN coffee_log ON staff_fk =
staff_id   WHERE (staff_id = $1) AND (coffee_time BETWEEN $2 AND $3) GROUP BY staff_id, name, room ORDER BY name;
 
$BODY$ LANGUAGE 'sql' STABLE

There the SELECT dumps a constant set of collumns where as far as I know 
have to be defined as a type to make SETOF happy or define the names 
whenever I call the function which would be tedious.

Actually this is a pretty simple example of some reports I need to produce.
They have around 60 collumns and there is also an aggregate and 
filtering on an id as well as 2 timestamps.
Since the aggregate depends on id and timestamps too, it is no solution 
to build a view and select from that within the function.

Have you tried returning SETOF RECORD[] and using the OUT specification?<br /><br />CREATE OR REPLACE FUNCTION
bla(integer,date, OUT date, OUT integer)<br />RETURNS SETOF RECORD[] AS<br />$_$<br />   SELECT date AS output_date,
$1+2AS next_record FROM table WHERE id = $1 AND start_date >= $2;<br /> $_$ LANGUAGE SQL;<br clear="all" /><br /><br
/>(Justan example code, I haven't tried it myself.  I know it works for plpgsql functions, not sure for sql
functions).<br/><br /><br />Regards,<br />--<br />Jorge Godoy     <<a
href="mailto:jgodoy@gmail.com">jgodoy@gmail.com</a>><br/><br /><br /><div class="gmail_quote">On Thu, Aug 27, 2009
at08:08, Andreas <span dir="ltr"><<a href="mailto:maps.on@gmx.net">maps.on@gmx.net</a>></span> wrote:<br
/><blockquoteclass="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex;
padding-left:1ex;"> Hi,<br /> wouldn't it be great to have functions return  "setof something" as result where
"something"was determined out of the result of a SELECT within the function?<br /> like<br /><br /> CREATE OR REPLACE
FUNCTIONcoffee(integer, timestamp, timestamp)<br />  RETURNS SETOF<br /> AS<br /> $BODY$<br />   SELECT staff_id, name,
room,COUNT(coffee_id) AS cupcount<br />    FROM staff  JOIN coffee_log ON staff_fk = staff_id<br />    WHERE (staff_id
=$1) AND (coffee_time BETWEEN $2 AND $3)<br />  GROUP BY staff_id, name, room<br />  ORDER BY name;<br /> $BODY$<br />
 LANGUAGE'sql' STABLE<br /><br /> There the SELECT dumps a constant set of collumns where as far as I know have to be
definedas a type to make SETOF happy or define the names whenever I call the function which would be tedious.<br /><br
/>Actually this is a pretty simple example of some reports I need to produce.<br /> They have around 60 collumns and
thereis also an aggregate and filtering on an id as well as 2 timestamps.<br /> Since the aggregate depends on id and
timestampstoo, it is no solution to build a view and select from that within the function.<br /><font
color="#888888"><br/> -- <br /> Sent via pgsql-sql mailing list (<a href="mailto:pgsql-sql@postgresql.org"
target="_blank">pgsql-sql@postgresql.org</a>)<br/> To make changes to your subscription:<br /><a
href="http://www.postgresql.org/mailpref/pgsql-sql"target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br
/></font></blockquote></div><br/>