Problem with functions.

From: "Jonas Lindholm" <jonas(dot)lindholm(at)omgroup(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Problem with functions.
Date: 2003-12-16 18:55:24
Message-ID: brnkes$7ej$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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.

Browse pgsql-sql by date

  From Date Subject
Next Message Preeti Ambardar 2003-12-17 06:26:52 SQL Optimization
Previous Message Alessandro Depase 2003-12-16 16:23:41 Re: failed to build any 5-way joins