Обсуждение: Returning data from function

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

Sharon,

> 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.

This functionality does not currently work in PostgreSQL.  Some form of
rowset-returning functions are expected for ver. 7.2 (any week now,
really ...) but I'm not certain they will do waht you want.

For more discussion, check the archives of the PGSQL-SQL list, where
this topic has surfaced multiple times.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

"Josh Berkus" <josh@agliodbs.com> writes:
>> I need to recieve parameters and return a resultset.

> This functionality does not currently work in PostgreSQL.

It's not so much that "it doesn't work" as that "plpgsql doesn't support
it".  Returning rowsets does work for SQL-language functions; not that
that's much help if you need a procedural language to do the
computations.  It's also possible to do it in C-coded functions
(a fortiori, since after all the whole system is in C).  But again that
might not be your preferred development medium.

FWIW, I've recently updated the description of SQL functions to clarify
(I hope) what works and does not work in this area.  See the devel docs
at http://candle.pha.pa.us/main/writings/pgsql/sgml/xfunc-sql.html.
The docs are devel, but the features described have all been there since
Berkeley days...

            regards, tom lane