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