RETURNS TABLE returns NULL set when called by another RETURNS TABLE

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  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$;

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

2010/2/24 Josh Berkus <josh@agliodbs.com>:

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

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

Pavel