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. <andv...@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: 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 <robertmh...@gmail.com>
>>
>> On Tue, Nov 30, 2010 at 4:43 PM, Andrey Galkin <andv...@gmail.com> wrote:
>> >
>> > The following bug has been logged online:
>> >
>> > Bug reference: 5776
>> > Logged by: Andrey Galkin
>> > Email address: andv...@gmail.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
>
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
