How to get a function to return a resultset?
От | Sharon Cowling |
---|---|
Тема | How to get a function to return a resultset? |
Дата | |
Msg-id | 000201c16176$88df8630$f902a8c0@fiji.sslnz.com обсуждение исходный текст |
Список | pgsql-general |
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 help and ideas will be most appreciated. Sharon Cowling
В списке pgsql-general по дате отправления: