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

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.

--
============================================================================

START TRANSACTION;

CREATE TABLE t1 (
some_field INT
);

--
-- WORKS
--
CREATE FUNCTION Bug_create_tmp_view_test()
RETURNS VOID
AS $$
BEGIN
CREATE TEMPORARY VIEW v1 AS
SELECT * FROM t1 WHERE some_field = 1;
END;
$$
LANGUAGE plpgsql;

--
-- FAILS: ERROR: column "v_some_field" does not exist
--
CREATE FUNCTION Bug_create_tmp_view_test( v_some_field INT4 )
RETURNS VOID
AS $$
BEGIN
CREATE TEMPORARY VIEW v2 AS
SELECT * FROM t1 WHERE some_field = v_some_field;
END;
$$
LANGUAGE plpgsql;

--
-- WORKS
--
CREATE FUNCTION Bug_create_tmp_view_exec_test()
RETURNS VOID
AS $$
BEGIN
EXECUTE 'CREATE VIEW v3 AS SELECT * FROM t1 WHERE some_field = 1';
END;
$$
LANGUAGE plpgsql;

--
-- FAILS
--
CREATE FUNCTION Bug_create_tmp_view_exec_test( v_some_field INT4 )
RETURNS VOID
AS $$
BEGIN
EXECUTE 'INSERT INTO t1 (some_field) VALUES ( $1 )' USING v_some_field;
EXECUTE 'CREATE VIEW v4 AS SELECT * FROM t1 WHERE some_field = $1' USING
v_some_field;
END;
$$
LANGUAGE plpgsql;

--

-- Comment the line below to trigger the second form of the/related bug
SELECT Bug_create_tmp_view_test(), Bug_create_tmp_view_test( 1 );
SELECT Bug_create_tmp_view_exec_test(), Bug_create_tmp_view_exec_test( 1 );

ROLLBACK;
--
============================================================================

$ psql -q < pgbug_create_statement.sql


ERROR: column "v_some_field" does not exist



LINE 2: SELECT * FROM t1 WHERE some_field = v_some_field



^



QUERY: CREATE TEMPORARY VIEW v2 AS



SELECT * FROM t1 WHERE some_field = v_some_field



CONTEXT: PL/pgSQL function "bug_create_tmp_view_test" line 2 at SQL
statement

$ psql -q < pgbug_create_statement.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

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

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

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

Pavel,

As far as I'm aware, SETOF return creates whole result in memory, what
has some implications.

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.

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

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

OK.

Thanks,
Andrey.

2010/12/14 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> 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
>>>>
>>>>
>>>
>>
>

On Mon, Dec 13, 2010 at 4:28 PM, Andrey G. <andvgal(at)gmail(dot)com> wrote:
> 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

Yeah, parameter substitution doesn't work in this case. You could
however build up a string with quote_identifier() and then EXECUTE the
resulting string.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert,

Yes, you've read my mind about quote_literal() ;). The bug report is
rising exactly the parameter substitution issue.

Andrey

2010/12/14 Robert Haas <robertmhaas(at)gmail(dot)com>:
> On Mon, Dec 13, 2010 at 4:28 PM, Andrey G. <andvgal(at)gmail(dot)com> wrote:
>> 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
>
> Yeah, parameter substitution doesn't work in this case.  You could
> however build up a string with quote_identifier() and then EXECUTE the
> resulting string.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

On Tue, Dec 14, 2010 at 4:03 PM, Andrey G. <andvgal(at)gmail(dot)com> wrote:
> Yes, you've read my mind about quote_literal() ;). The bug report is
> rising exactly the parameter substitution issue.

Yeah. I don't think it's exactly a bug so much as a known limitation,
but of course I agree it would be convenient if it worked differently.
Nobody's felt the urge to do the work necessary to remove that
limitation yet, though...

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company