Обсуждение: 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