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(at)mail(dot)ru>:
>
> The following bug has been logged online:
>
> Bug reference:      4783
> Logged by:          Alex
> Email address:      oktogen(at)mail(dot)ru
> PostgreSQL version: 8.4
> Operating system:   WinXP
> Description:        new syntax in tablefunction - not output cells
> Details:
>
> CREATE TABLE tst (
>  "id" BIGSERIAL,
>  "vl" DOUBLE PRECISION DEFAULT 0 NOT NULL,
>  CONSTRAINT "tst_pkey" PRIMARY KEY("id")
> ) WITHOUT OIDS;
>
> INSERT INTO   tst(id)
> VALUES  (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,   vl DOUBLE PRECISION) AS
> $body$
> BEGIN
>  RETURN QUERY
>  SELECT
>    id,
>    vl
>  FROM 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,   vl DOUBLE PRECISION) AS
> $body$
>  SELECT
>    id,
>    vl
>  FROM 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" = name columns  in
> "RETURNS TABLE".
>
> was tested with languages: plpgsql and sql
> this is bag or feature?
> thank's
>
> --
> 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
>