Skip site navigation (1) Skip section navigation (2)

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

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: RETURNS TABLE returns NULL set when called by another RETURNS TABLE
Date: 2010-02-24 07:03:02
Message-ID: 162867791002232303h422097d8s13b28d4915724635@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
2010/2/24 Josh Berkus <josh(at)agliodbs(dot)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

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

In response to

pgsql-bugs by date

Next:From: Pavel StehuleDate: 2010-02-24 07:06:39
Subject: Re: RETURNS TABLE returns NULL set when called by another RETURNS TABLE
Previous:From: Robert HaasDate: 2010-02-24 03:55:47
Subject: Re: BUG #5344: pg_restore some foreign keys missing

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group