Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 5D04B63303E for ; Thu, 27 Aug 2009 08:26:45 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 68952-05 for ; Thu, 27 Aug 2009 11:26:25 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-yw0-f202.google.com (mail-yw0-f202.google.com [209.85.211.202]) by mail.postgresql.org (Postfix) with ESMTP id 31791632EEC for ; Thu, 27 Aug 2009 08:26:32 -0300 (ADT) Received: by ywh40 with SMTP id 40so1251787ywh.14 for ; Thu, 27 Aug 2009 04:26:30 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :from:date:message-id:subject:to:cc:content-type; bh=F7sBFUbOYxutIQqhrXwivNMtxTtU2/7xsCXskAqOEkg=; b=mHpI6LIuXm0IgZF7tps5uAGgDdpJRYdVajupGoU7gEM5cPwUH7Z41ACk6CUy1ns6ax e4zMnp/zF9E582JWO5D98IM8tS5ELxFg1yGcl6gQkXm7KZNP7owcthi3V/XehfEaxoBo WJPwtHh03PawQirUzJos9w5oLvAWcY3hRgL3E= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; b=XaJBAM452iSzkpOM9eEoiInrNJp2EnpPX9n8MPkcE4En1K8WOUwVGmyIJaHD+ss0mU SYPgIUXyZIHFkV1H1rICvLDvlUJ4YsIAfN7YRv60lDhFC/PpaxA0H+Onwc9qER6oi5f0 0X4P9p5crQoWVMLfBMlv4/1WwbmokbDaC78xg= MIME-Version: 1.0 Received: by 10.101.183.1 with SMTP id k1mr9542226anp.18.1251372369147; Thu, 27 Aug 2009 04:26:09 -0700 (PDT) In-Reply-To: <4A966932.6050602@gmx.net> References: <4A966932.6050602@gmx.net> From: Jorge Godoy Date: Thu, 27 Aug 2009 08:25:49 -0300 Message-ID: <175c742d0908270425t748fe0f7v8cf9d15c3727031a@mail.gmail.com> Subject: Re: Howto automatically define collumn names for a function result. To: Andreas Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=0016368e29318b76e304721dd526 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200908/136 X-Sequence-Number: 33045 --0016368e29318b76e304721dd526 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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 On Thu, Aug 27, 2009 at 08:08, Andreas 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 > --0016368e29318b76e304721dd526 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable 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
$_$
=A0=A0 SELECT date AS output_date, $1= +2 AS next_record FROM table WHERE id =3D $1 AND start_date >=3D $2;
$_$ LANGUAGE SQL;


(Just an example code, I haven&#= 39;t tried it myself.=A0 I know it works for plpgsql functions, not sure fo= r sql functions).


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


On Thu, Aug 27, 2009 at 08:08, Andreas <= span dir=3D"ltr"><maps.on@gmx.net= > wrote:
Hi,
wouldn't it be great to have functions return =A0"setof something&= quot; as result where "something" was determined out of the resul= t of a SELECT within the function?
like

CREATE OR REPLACE FUNCTION coffee(integer, timestamp, timestamp)
=A0RETURNS SETOF
AS
$BODY$
=A0 SELECT staff_id, name, room, COUNT(coffee_id) AS cupcount
=A0 =A0FROM staff =A0JOIN coffee_log ON staff_fk =3D staff_id
=A0 =A0WHERE (staff_id =3D $1) AND (coffee_time BETWEEN $2 AND $3)
=A0GROUP BY staff_id, name, room
=A0ORDER BY name;
$BODY$
=A0LANGUAGE 'sql' STABLE

There the SELECT dumps a constant set of collumns where as far as I know ha= ve 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 o= n 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

--0016368e29318b76e304721dd526--