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
>