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@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.
>
>