Re: BUG #4783: new syntax in tablefunction - not output cells

Hello

it's not bug. It's is behave of plpgsql, where variables has higher
priority than SQL identifiers. Columns in returned table definition
create implicit variables.

Use qualifiers:

 CREATE OR REPLACE FUNCTION
 test2 () RETURNS TABLE(id BIGINT,   vl DOUBLE PRECISION) AS
 $body$
  SELECT
    tst.id,
    tst.vl
  FROM tst;
 END
 $body$
 LANGUAGE 'sql'
 IMMUTABLE
 CALLED ON NULL INPUT
 SECURITY DEFINER
 COST 1000 ROWS 1000;

regards
Pavel Stehule

2009/4/26 Alex <oktogen@mail.ru>:
>
> The following bug has been logged online:
>
> Bug reference: =C2=A0 =C2=A0 =C2=A04783
> Logged by: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Alex
> Email address: =C2=A0 =C2=A0 =C2=A0oktogen@mail.ru
> PostgreSQL version: 8.4
> Operating system: =C2=A0 WinXP
> Description: =C2=A0 =C2=A0 =C2=A0 =C2=A0new syntax in tablefunction - not=
 output cells
> Details:
>
> CREATE TABLE tst (
> =C2=A0"id" BIGSERIAL,
> =C2=A0"vl" DOUBLE PRECISION DEFAULT 0 NOT NULL,
> =C2=A0CONSTRAINT "tst_pkey" PRIMARY KEY("id")
> ) WITHOUT OIDS;
>
> INSERT INTO =C2=A0 tst(id)
> VALUES =C2=A0(1),(2),(2),(2),(2),(2),(2),(2),(2),(2),
> (2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(=
2),
> (2),(2),(2),(2),(2),
> (2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),
> (2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),
> (2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),
> (2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2);
>
>
> CREATE OR REPLACE FUNCTION
> test1 () RETURNS TABLE(id BIGINT, =C2=A0 vl DOUBLE PRECISION) AS
> $body$
> BEGIN
> =C2=A0RETURN QUERY
> =C2=A0SELECT
> =C2=A0 =C2=A0id,
> =C2=A0 =C2=A0vl
> =C2=A0FROM tst;
> END
> $body$
> LANGUAGE 'plpgsql'
> IMMUTABLE
> CALLED ON NULL INPUT
> SECURITY DEFINER
> COST 1000 ROWS 1000;
>
> CREATE OR REPLACE FUNCTION
> test2 () RETURNS TABLE(id BIGINT, =C2=A0 vl DOUBLE PRECISION) AS
> $body$
> =C2=A0SELECT
> =C2=A0 =C2=A0id,
> =C2=A0 =C2=A0vl
> =C2=A0FROM tst;
> END
> $body$
> LANGUAGE 'sql'
> IMMUTABLE
> CALLED ON NULL INPUT
> SECURITY DEFINER
> COST 1000 ROWS 1000;
>
> query SELECT * FROM test1(); return NULL's if
> name columns in "RETURN QUERY" =3D name columns =C2=A0in
> "RETURNS TABLE".
>
> was tested with languages: plpgsql and sql
> this is bag or feature?
> thank's
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>