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=# 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
>
>