Обсуждение: BUG #4783: new syntax in tablefunction - not output cells

The following bug has been logged online:

Bug reference:      4783
Logged by:          Alex
Email address:      oktogen@mail.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

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
>