[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    Re: [SQL] Howto automatically define collumn names for a function
From:       Jorge Godoy <jgodoy () gmail ! com>
Date:       2009-08-27 11:25:49
Message-ID: 175c742d0908270425t748fe0f7v8cf9d15c3727031a () mail ! gmail ! com
[Download RAW message or body]

Have you tried returning SETOF RECORD[] and using the OUT specification?

CREATE OR REPLACE FUNCTION bla(integer, date, OUT date, OUT integer)
RETURNS SETOF RECORD[] AS
$_$
   SELECT date AS output_date, $1+2 AS next_record FROM table WHERE id = $1
AND start_date >= $2;
$_$ LANGUAGE SQL;


(Just an example code, I haven't tried it myself.  I know it works for
plpgsql functions, not sure for sql functions).


Regards,
--
Jorge Godoy     <jgodoy@gmail.com>


On Thu, Aug 27, 2009 at 08:08, Andreas <maps.on@gmx.net> wrote:

> 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.
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

[Attachment #3 (text/html)]

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+2 \
AS next_record FROM table WHERE id = $1 AND start_date &gt;= $2;<br>

$_$ LANGUAGE SQL;<br clear="all"><br><br>(Just an example code, I haven&#39;t tried \
it myself.  I know it works for plpgsql functions, not sure for sql \
functions).<br><br><br>Regards,<br>--<br>Jorge Godoy     &lt;<a \
href="mailto:jgodoy@gmail.com">jgodoy@gmail.com</a>&gt;<br>


<br><br><div class="gmail_quote">On Thu, Aug 27, 2009 at 08:08, Andreas <span \
dir="ltr">&lt;<a href="mailto:maps.on@gmx.net">maps.on@gmx.net</a>&gt;</span> \
wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, \
204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">

Hi,<br>
wouldn&#39;t it be great to have functions return  &quot;setof something&quot; as \
result where &quot;something&quot; was determined out of the result of a SELECT \
within the function?<br> like<br>
<br>
CREATE OR REPLACE FUNCTION coffee(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 &#39;sql&#39; STABLE<br>
<br>
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.<br> <br>
Actually this is a pretty simple example of some reports I need to produce.<br>
They have around 60 collumns and there is also an aggregate and filtering on an id as \
well as 2 timestamps.<br> Since the aggregate depends on id and timestamps too, 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>



[prev in list] [next in list] [prev in thread] [next in thread]