Re: BUG #5776: Unable to create view with parameter in PL/pgsql - Mailing list pgsql-bugs

OK.

Thanks,
Andrey.



2010/12/14 Pavel Stehule <pavel.stehule@gmail.com>:
> Hello
>
> 2010/12/14 Andrey G. <andvgal@gmail.com>:
>> Pavel,
>>
>> As far as I'm aware, SETOF return creates whole result in memory, what
>> has some implications.
>>
>
> it's not true for immutable SQL function - look on EXPLAIN
>
>> 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.
>>
>
> It's done - SQL immutable function works exactly like you need.
>
> Regards
>
> Pavel Stehule
>
>
>> 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 s=
etof foo as $$
>>> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 s=
elect * from foo where a =3D $1;
>>> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0$$ langua=
ge 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 widt=
h=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> wr=
ote:
>>>>> >
>>>>> > 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 =
parameter 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
>>>>
>>>>
>>>
>>
>