From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | "Andrey G(dot)" <andvgal(at)gmail(dot)com> |
Cc: | Robert Haas <robertmhaas(at)gmail(dot)com>, pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #5776: Unable to create view with parameter in PL/pgsql |
Date: | 2010-12-14 11:19:48 |
Message-ID: | AANLkTin7J-sAHFfGYN0h8sEemiD3FaSvfzymyB4=w9nw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Hello
2010/12/14 Andrey G. <andvgal(at)gmail(dot)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(dot)stehule(at)gmail(dot)com>:
>> Hello
>>
>> View must not has a parameter in PostgreSQL. You can use a SRF function:
>>
>> postgres=# create or replace function parametrized_view(a int)
>> returns setof foo as $$
>> select * from foo where a = $1;
>> $$ language sql immutable;
>> CREATE FUNCTION
>> postgres=# select * from parametrized_view(10);
>> a
>> ----
>> 10
>> (1 row)
>>
>> postgres=# explain select * from parametrized_view(10);
>> QUERY PLAN
>> --------------------------------------------------------------
>> Index Scan using aa on foo (cost=0.00..8.27 rows=1 width=4)
>> Index Cond: (a = 10)
>> (2 rows)
>>
>> Regards
>>
>> Pavel Stehule
>>
>>
>> 2010/12/13 Andrey G. <andvgal(at)gmail(dot)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: there is no parameter $1
>>> LINE 1: CREATE VIEW v4 AS SELECT * FROM t1 WHERE some_field = $1
>>> ^
>>> QUERY: CREATE VIEW v4 AS SELECT * FROM t1 WHERE some_field = $1
>>> CONTEXT: PL/pgSQL function "bug_create_tmp_view_exec_test" line 3 at
>>> EXECUTE statement
>>>
>>> Andrey
>>>
>>>
>>> 2010/12/13 Robert Haas <robertmhaas(at)gmail(dot)com>
>>>>
>>>> On Tue, Nov 30, 2010 at 4:43 PM, Andrey Galkin <andvgal(at)gmail(dot)com> wrote:
>>>> >
>>>> > The following bug has been logged online:
>>>> >
>>>> > Bug reference: 5776
>>>> > Logged by: Andrey Galkin
>>>> > Email address: andvgal(at)gmail(dot)com
>>>> > PostgreSQL version: 9.0.1
>>>> > Operating system: Debian unstable
>>>> > Description: Unable 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(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-bugs
>>>
>>>
>>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2010-12-14 12:47:20 | Re: index corruption on composite primary key indexes |
Previous Message | Andrey G. | 2010-12-14 10:51:53 | Re: BUG #5776: Unable to create view with parameter in PL/pgsql |