Skip site navigation (1) Skip section navigation (2)

Returning data from function

From: "Sharon Cowling" <sharon(dot)cowling(at)sslnz(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Returning data from function
Date: 2001-10-31 20:49:22
Message-ID: 001701c1624d$859ac0c0$f902a8c0@fiji.sslnz.com (view raw or flat)
Thread:
Lists: pgsql-novice
I've mainly worked with Oracle in the past, so I need your help with this:
I'm creating the code for web based reports for a client.  I need to recieve
parameters
and return a resultset.
For example I need to recieve the start date and the end date, select all
rows that match those
dates and output them.  This is the code I have written that does not work.
I've been trying various ways of doing this:

CREATE FUNCTION my_test(DATE,DATE) RETURNS setof record AS '
DECLARE
	v_permit_from ALIAS FOR $1;
	v_permit_to ALIAS FOR $2;
	rs record;
BEGIN
SELECT INTO rs t.permit_id, t.date_from, t.date_to, p.person_id,
p.firstname, p.lastname
FROM permit t, person p
WHERE t.person_id = p.person_id
AND t.date_from = v_permit_from
AND t.date_to = v_permit_to
ORDER BY t.issue_date, t.date_from;
return rs;
END;
' LANGUAGE 'plpgsql'

Message when I run code above:
NOTICE:  ProcedureCreate: return type 'record' is only a shell
CREATE

user=> select my_test('25/10/2001','08/11/2001');
ERROR:  fmgr_info: function 0: cache lookup failed

I'm pretty sure that I cannot use the return type of record, but when I try
text I get no
notice when it is created but the following:

taupo=> select my_test('25/10/2001','08/11/2001');
ERROR:  Attribute 'rs' not found

I have researched both setof and PERFORM SELECT but I can't seem to find
anymore than a couple of
lines which don't help me.  I notice that when you use setof you can return
a table or a row, but
I cannot find anything on returning a resultset.

Your ideas and suggestions will be most appreciated.

Sharon Cowling


Responses

pgsql-novice by date

Next:From: Josh BerkusDate: 2001-10-31 21:48:47
Subject: Re: Returning data from function
Previous:From: Josh BerkusDate: 2001-10-31 15:52:36
Subject: Re: DECODE

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group