Re: BUG #5776: Unable to create view with parameter in PL/pgsql

Hello

View must not has a parameter in PostgreSQL. You can use a SRF function:

postgres=3D# create or replace function parametrized_view(a int)
                  returns setof foo as $$
                     select * from foo where a =3D $1;
                  $$ language sql immutable;
CREATE FUNCTION
postgres=3D# select * from parametrized_view(10);
 a
----
 10
(1 row)

postgres=3D# explain select * from parametrized_view(10);
                          QUERY PLAN
--------------------------------------------------------------
 Index Scan using aa on foo  (cost=3D0.00..8.27 rows=3D1 width=3D4)
   Index Cond: (a =3D 10)
(2 rows)

Regards

Pavel Stehule


2010/12/13 Andrey G. <andvgal@gmail.com>:
> It seems my original test, which also includes the EXECUTE approach,
> has not come to you in full. EXECUTE statement also fails with
> parameter: The test is attached in file.
>
> psql -q < db/db/pgbug_5776.sql
> ERROR:=C2=A0 there is no parameter $1
> LINE 1: CREATE VIEW v4 AS SELECT * FROM t1 WHERE some_field =3D $1
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ^
> QUERY:=C2=A0 CREATE VIEW v4 AS SELECT * FROM t1 WHERE some_field =3D $1
> CONTEXT:=C2=A0 PL/pgSQL function "bug_create_tmp_view_exec_test" line 3 at
> EXECUTE statement
>
> Andrey
>
>
> 2010/12/13 Robert Haas <robertmhaas@gmail.com>
>>
>> On Tue, Nov 30, 2010 at 4:43 PM, Andrey Galkin <andvgal@gmail.com> wrote:
>> >
>> > The following bug has been logged online:
>> >
>> > Bug reference: =C2=A0 =C2=A0 =C2=A05776
>> > Logged by: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Andrey Galkin
>> > Email address: =C2=A0 =C2=A0 =C2=A0andvgal@gmail.com
>> > PostgreSQL version: 9.0.1
>> > Operating system: =C2=A0 Debian unstable
>> > Description: =C2=A0 =C2=A0 =C2=A0 =C2=A0Unable to create view with par=
ameter in PL/pgsql
>> > Details:
>> >
>> > Below is simple test case. Perhaps, I'm doing something wrong.
>>
>> You can accomplish what you're trying to do using EXECUTE.
>>
>> --
>> Robert Haas
>> EnterpriseDB: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
>