Re: RETURNS TABLE returns NULL set when called by another RETURNS TABLE

2010/2/24 Josh Berkus <josh@agliodbs.com>:
> Pavel, all:
>
> Apparently if you use one returns table function to call a 2nd returns
> table function, it returns a recordset which consists entirely of nulls.
>
> Here's the test case:
>
> create table srf_data ( id serial, cat int, val text );
> insert into srf_data ( cat, val ) values
> ( 1, 'josh' ),
> ( 1, 'selena' ),
> ( 2, 'bruce' ),
> ( 2, 'josh' ),
> ( 3, 'robert' );
>
> create or replace =C2=A0function srf1 ( this_cat int )
> returns table (
> =C2=A0 =C2=A0 =C2=A0 =C2=A0id1 int,
> =C2=A0 =C2=A0 =C2=A0 =C2=A0val1 text )
> language sql as $f$
> select id, val from srf_data where cat =3D $1;
> $f$;
>
> create or replace function srf2 ( )
> returns table (
> =C2=A0 =C2=A0 =C2=A0 =C2=A0id1 int,
> =C2=A0 =C2=A0 =C2=A0 =C2=A0val1 text )
> language plpgsql as $f$
> begin
> return query
> select id1, val1 from srf1(1);
> return;
> end;
> $f$;
>

there is identifier's conflict - try to use alias

create or replace function srf2() returns table(id1 int, val1 text)
language plpgsql as $$
begin
  return query select s.id1, s.val1 from srf(1) s;
  return;
end;
$$

Regards
Pavel Stehule

Pavel

> select * from srf2();
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>