Обсуждение: Table functions say "no destination for result data."
Hi everybody! I'mt playing with new table functions on a fresh postgresql 7.3 over Solaris... I want a function who return several rows, so I define that: -- Function: public.matcheo_cupido_tf(int8) CREATE FUNCTION public.match_tf(int8) RETURNS public.vw_match AS ' DECLARE vid ALIAS FOR $1; result int8; vnick varchar; vsex varchar; vdesde int8; vhasta int8; BEGIN select into vnick,vsex,vdesde,vhasta par.nick,par.sexo,pb.edaddesde,pb.edadhasta,pb.pais from participantes par, perfilesbusqueda pb where par.identificador = vid and pb.participante = par.identificador; select pp.participante,par.nick,pp.sex,pp.edad,pp.desc from perfilespropios pp, participantes par where pp.sex <> vsex and pp.edad >= vdesde and pp.edad <= vhasta and par.identificador = pp.participante ; return; END; ' LANGUAGE 'plpgsql' VOLATILE; So, I pass to the function a number. Inside the function, first I get some information (select into) about the search profile, and then I do the select who want to return (vw_match it's a view who have the same structure of the second select). The problem is, when I try to execute this function (in PHP) I get this message: SELECT match_tf(132); Warning: pg_query() query failed: ERROR: SELECT query has no destination for result data. If you want to discard the results, use PERFORM instead. in /usr/local/apache/htdocs/postgres/ap_tf.php on line 17 I see this in postgresql logfile: 2002-12-06 17:00:13 ERROR: SELECT query has no destination for result data. If you want to discard the results, use PERFORM instead. 2002-12-06 17:00:13 WARNING: Error occurred while executing PL/pgSQL function match_tf The same error gave me if I try on psql: cont=# select match_tf(132); WARNING: Error occurred while executing PL/pgSQL function match_tf WARNING: line 21 at SQL statement ERROR: SELECT query has no destination for result data. If you want to discard the results, use PERFORM instead. cont=# What is the problem? Is this a good use of table function? Thanks in advance! -- Fernando O. Papa
On Fri, 6 Dec 2002, Fernando Papa wrote: > > Hi everybody! > > I'mt playing with new table functions on a fresh postgresql 7.3 over > Solaris... I want a function who return several rows, so I define that: > > -- Function: public.matcheo_cupido_tf(int8) > CREATE FUNCTION public.match_tf(int8) RETURNS public.vw_match AS ' If you want to return multiple rows you want RETURNS SETOF public.vw_match > DECLARE > vid ALIAS FOR $1; > result int8; > vnick varchar; > vsex varchar; > vdesde int8; > vhasta int8; (add another local, see below) r record; > BEGIN > > select into vnick,vsex,vdesde,vhasta > par.nick,par.sexo,pb.edaddesde,pb.edadhasta,pb.pais > from participantes par, > perfilesbusqueda pb > where par.identificador = vid and > pb.participante = par.identificador; > > select pp.participante,par.nick,pp.sex,pp.edad,pp.desc > from perfilespropios pp, > participantes par > where pp.sex <> vsex and > pp.edad >= vdesde and > pp.edad <= vhasta and > par.identificador = pp.participante > ; You want something like: for r in select ... loop return next r; end loop; return; I believe.
Fernando Papa wrote: > I'mt playing with new table functions on a fresh postgresql 7.3 over > Solaris... I want a function who return several rows, so I define that: You need to re-read the manual on this. See (at least): http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html Basically you need to select into a record type variable in a loop, and use RETURN NEXT. Here's an unrelated working example you can study: CREATE TABLE payments (r_date_payment TIMESTAMP, r_description VARCHAR(50), r_value numeric (12,2)); INSERT INTO payments VALUES(CURRENT_TIMESTAMP, 'a', '12.50'); INSERT INTO payments VALUES(CURRENT_TIMESTAMP, 'b', '11.75'); INSERT INTO payments VALUES(CURRENT_TIMESTAMP, 'c', '-99.99'); CREATE OR REPLACE FUNCTION my_proc(TIMESTAMP) RETURNS SETOF payments AS ' DECLARE rec RECORD; BEGIN FOR rec IN SELECT * FROM payments WHERE r_date_payment BETWEEN $1 AND CURRENT_TIMESTAMP LOOP IF rec.r_value < 0 THEN rec.r_value = rec.r_value*-1; END IF; RETURN NEXT rec; /* Each RETURN NEXT command returns a row */ END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; test=# select * from payments; r_date_payment | r_description | r_value ----------------------------+---------------+--------- 2002-10-22 10:27:38.086554 | a | 12.50 2002-10-22 10:27:38.172964 | b | 11.75 2002-10-22 10:27:38.177543 | c | -99.99 (3 rows) test=# SELECT * FROM my_proc('01/01/2002'); r_date_payment | r_description | r_value ----------------------------+---------------+--------- 2002-10-22 10:27:38.086554 | a | 12.50 2002-10-22 10:27:38.172964 | b | 11.75 2002-10-22 10:27:38.177543 | c | 99.99 (3 rows) Here's a slightly different approach: CREATE OR REPLACE FUNCTION show_group(text) RETURNS SETOF text AS ' DECLARE loginname text; low int; high int; BEGIN SELECT INTO low replace(split_part(array_dims(grolist),'':'',1),''['','''')::int FROM pg_group WHERE groname = $1; SELECT INTO high replace(split_part(array_dims(grolist),'':'',2),'']'','''')::int FROM pg_group WHERE groname = $1; FOR i IN low..high LOOP SELECT INTO loginname s.usename FROM pg_shadow s join pg_group g on s.usesysid = g.grolist[i]; RETURN NEXT loginname; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; regression=# select * from show_group('grp1'); show_group ------------ postgres testuser robot (3 rows) HTH, Joe
Hi Stephan! I read your comments: > If you want to return multiple rows you want > RETURNS SETOF public.vw_match I don't know why pgAdmin doesn't show RETURNS SETOF... I put it, but when I try to see DDL on pgAdmn I see without "SETOF"... maybe a bug? > for r in select ... loop > return next r; > end loop; I put this thing (it's really a new concept... I'm coming from oracle and I never seen things like that) on function. I make a very simple version of these function just for familiarize about the way to do this: CREATE FUNCTION public.match_tf(int8) RETURNS SETOF public.vw_matcheo AS ' DECLARE vid ALIAS FOR $1; vcursor refcursor; r record; BEGIN for r in select pp.participante,par.nick,pp.sexo,pp.edad,pp.pais,pp.descripcionbreve from perfilespropios pp, participantes par where par.identificador = pp.participante limit 5 loop return next r; end loop; return; END; ' LANGUAGE 'plpgsql' And I get another error this time: 2002-12-09 12:04:40 ERROR: Set-valued function called in context that cannot accept a set 2002-12-09 12:04:40 WARNING: Error occurred while executing PL/pgSQL function match_tf 2002-12-09 12:04:40 WARNING: line 31 at return next I called the function: contenedor=# select match_tf(132); WARNING: Error occurred while executing PL/pgSQL function match_tf WARNING: line 13 at return next ERROR: Set-valued function called in context that cannot accept a set could be the problem the view? Thanks a lot! -- Fernando O. Papa > -----Mensaje original----- > De: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com] > Enviado el: viernes, 06 de diciembre de 2002 18:20 > Para: Fernando Papa > CC: pgsql-general@postgresql.org > Asunto: Re: [GENERAL] Table functions say "no destination for > result data." > > > > On Fri, 6 Dec 2002, Fernando Papa wrote: > > > > > Hi everybody! > > > > I'mt playing with new table functions on a fresh postgresql > 7.3 over > > Solaris... I want a function who return several rows, so I define > > that: > > > > -- Function: public.matcheo_cupido_tf(int8) > > CREATE FUNCTION public.match_tf(int8) RETURNS public.vw_match AS ' > > If you want to return multiple rows you want > RETURNS SETOF public.vw_match > > > DECLARE > > vid ALIAS FOR $1; > > result int8; > > vnick varchar; > > vsex varchar; > > vdesde int8; > > vhasta int8; > (add another local, see below) > r record; > > > BEGIN > > > > select into vnick,vsex,vdesde,vhasta > > par.nick,par.sexo,pb.edaddesde,pb.edadhasta,pb.pais > > from participantes par, > > perfilesbusqueda pb > > where par.identificador = vid and > > pb.participante = par.identificador; > > > > > > select pp.participante,par.nick,pp.sex,pp.edad,pp.desc > > from perfilespropios pp, > > participantes par > > where pp.sex <> vsex and > > pp.edad >= vdesde and > > pp.edad <= vhasta and > > par.identificador = pp.participante > > ; > > You want something like: > > for r in select ... loop > return next r; > end loop; > > return; > > I believe. > >
Sorry Stefan and all listers... I made this: contenedor=# select match_tf(132); but I must do this: contenedor=# select * from match_tf(132); -- Fernando O. Papa DBA