Обсуждение: RETURNS TABLE returns NULL set when called by another RETURNS TABLE

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  function srf1 ( this_cat int )
returns table (
        id1 int,
        val1 text )
language sql as $f$
select id, val from srf_data where cat = $1;
$f$;

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

select * from srf2();

Josh Berkus <josh@agliodbs.com> writes:
> Apparently if you use one returns table function to call a 2nd returns
> table function, it returns a recordset which consists entirely of nulls.

In HEAD that example fails with

psql:josh.sql:30: ERROR:  column reference "id1" is ambiguous
LINE 1: select id1, val1 from srf1(1)
               ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  select id1, val1 from srf1(1)
CONTEXT:  PL/pgSQL function "srf2" line 2 at RETURN QUERY

val1 is just as ambiguous.  I think you got bit by the name collision;
the output parameters would start out NULLs and thus lead to the
described behavior, in versions before 9.0.

            regards, tom lane

> val1 is just as ambiguous.  I think you got bit by the name collision;
> the output parameters would start out NULLs and thus lead to the
> described behavior, in versions before 9.0.

Aha, yeah, that's probably it.  Take this example as the reason we had
to change the behavior ...

--Josh Berkus

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
>

2010/2/24 Josh Berkus <josh@agliodbs.com>:
>
>> val1 is just as ambiguous. =C2=A0I think you got bit by the name collisi=
on;
>> the output parameters would start out NULLs and thus lead to the
>> described behavior, in versions before 9.0.
>
> Aha, yeah, that's probably it. =C2=A0Take this example as the reason we h=
ad
> to change the behavior ...

yes - I am very happy with this change. It is the biggest change in
plpgsql over 10 years.

Pavel

>
> --Josh Berkus
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>