Message-ID: <4A966932.6050602@gmx.net>
Date: Thu, 27 Aug 2009 13:08:34 +0200
From: Andreas <maps.on@gmx.net>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Howto automatically define collumn names for a function result.
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit

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.