Skip site navigation (1) Skip section navigation (2)

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

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 08:58:03
Message-ID: AANLkTimw_WKjziiJgfwMtV2-LeJsJLVnadonWyDnVQxH@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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
>
>

In response to

Responses

pgsql-bugs by date

Next:From: Mikael KrantzDate: 2010-12-14 09:14:36
Subject: Re: index corruption on composite primary key indexes
Previous:From: Ng, StanDate: 2010-12-14 02:18:33
Subject: index corruption on composite primary key indexes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group