From: "Jonas Lindholm" <jonas.lindholm@omgroup.com>
Subject: Problem with functions.
Date: Tue, 16 Dec 2003 13:55:24 -0500
Organization: OM Technology
Lines: 77
Message-ID: <brnkes$7ej$1@news.hub.org>
To: pgsql-sql@postgresql.org

Hi,

I'n trying to create a function with two input values and returning a SETOF.

When I only have one input value everything is working ok.
If I instead create the same function with two input values the query fail
with:

ERROR:  a column definition list is required for functions returning
"record"

The two functions are returning exactly the same type of data.

This is the definition of the function with one input value that works ok:

CREATE FUNCTION getlosts (timestamp) RETURNS SETOF userlog AS '
       DECLARE
 beg ALIAS FOR $1;
 rec RECORD;

       BEGIN

 FOR rec IN SELECT * FROM userlog WHERE login > beg LOOP
     RETURN NEXT rec;
 END LOOP;
 RETURN;

       END;
' LANGUAGE 'plpgsql';


This is the definition of the function that fail:
(note that I only added the secondary timestamp used to limit the upper
value of the select)

CREATE FUNCTION getlosts (timestamp, timestamp) RETURNS SETOF userlog AS '
       DECLARE
 beg ALIAS FOR $1;
 ending ALIAS FOR $2;
 rec RECORD;

       BEGIN

 FOR rec IN SELECT * FROM userlog WHERE login > beg AND login < ending LOOP
     RETURN NEXT rec;
 END LOOP;
 RETURN;

       END;
' LANGUAGE 'plpgsql';


This is the definition of table userlog:


             Table "public.userlog"
  Column  |           Type           | Modifiers
----------+--------------------------+-----------
 login    | timestamp with time zone |
 logout   | timestamp with time zone |
 reason   | character varying(20)    |
 source   | character varying(32)    |
 username | character varying(64)    |
 gw       | character varying(32)    |
 ident    | character varying(64)    |
 id       | integer                  |


I'm running Postgres version 7.4.

Thanks
/Jonas L.