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

Pavel,

As far as I'm aware, SETOF return creates whole result in memory, what
has some implications.

My intention was to create a temporary view in session, which can be
used in other processing. Of course, I've found another solution, but
creating view with parameter dynamically could be a good feature.

Andrey



2010/12/14 Pavel Stehule <pavel.stehule@gmail.com>:
> 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)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0returns set=
of foo as $$
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 sel=
ect * from foo where a =3D $1;
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0$$ language=
 sql immutable;
> CREATE FUNCTION
> postgres=3D# select * from parametrized_view(10);
> =C2=A0a
> ----
> =C2=A010
> (1 row)
>
> postgres=3D# explain select * from parametrized_view(10);
> =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=A0QUERY PLAN
> --------------------------------------------------------------
> =C2=A0Index Scan using aa on foo =C2=A0(cost=3D0.00..8.27 rows=3D1 width=
=3D4)
> =C2=A0 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> wrot=
e:
>>> >
>>> > 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 pa=
rameter 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
>>
>>
>