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 <[email protected]>:
>
> The following bug has been logged online:
>
> Bug reference: 4783
> Logged by: Alex
> Email address: [email protected]
> 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 ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
--
Sent via pgsql-bugs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
