Re: Table functions say "no destination for result data."

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(at)megazone23(dot)bigpanda(dot)com]
> Enviado el: viernes, 06 de diciembre de 2002 18:20
> Para: Fernando Papa
> CC: pgsql-general(at)postgresql(dot)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.
>
>