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@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.
--
============================================================================
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@gmail.com> wrote:
The following bug has been logged online:
Bug reference: 5776
Logged by: Andrey Galkin
Email address: andvgal@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
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@gmail.com>
Show quoted text
On Tue, Nov 30, 2010 at 4:43 PM, Andrey Galkin <andvgal@gmail.com> wrote:
The following bug has been logged online:
Bug reference: 5776
Logged by: Andrey Galkin
Email address: andvgal@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
Attachments:
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@gmail.com>:
Show quoted text
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 statementAndrey
2010/12/13 Robert Haas <robertmhaas@gmail.com>
On Tue, Nov 30, 2010 at 4:43 PM, Andrey Galkin <andvgal@gmail.com> wrote:
The following bug has been logged online:
Bug reference: 5776
Logged by: Andrey Galkin
Email address: andvgal@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
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.stehule@gmail.com>:
Show quoted text
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@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 statementAndrey
2010/12/13 Robert Haas <robertmhaas@gmail.com>
On Tue, Nov 30, 2010 at 4:43 PM, Andrey Galkin <andvgal@gmail.com> wrote:
The following bug has been logged online:
Bug reference: 5776
Logged by: Andrey Galkin
Email address: andvgal@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
Hello
2010/12/14 Andrey G. <andvgal@gmail.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
Show quoted text
Andrey
2010/12/14 Pavel Stehule <pavel.stehule@gmail.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@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 statementAndrey
2010/12/13 Robert Haas <robertmhaas@gmail.com>
On Tue, Nov 30, 2010 at 4:43 PM, Andrey Galkin <andvgal@gmail.com> wrote:
The following bug has been logged online:
Bug reference: 5776
Logged by: Andrey Galkin
Email address: andvgal@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
OK.
Thanks,
Andrey.
2010/12/14 Pavel Stehule <pavel.stehule@gmail.com>:
Show quoted text
Hello
2010/12/14 Andrey G. <andvgal@gmail.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.stehule@gmail.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@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 statementAndrey
2010/12/13 Robert Haas <robertmhaas@gmail.com>
On Tue, Nov 30, 2010 at 4:43 PM, Andrey Galkin <andvgal@gmail.com> wrote:
The following bug has been logged online:
Bug reference: 5776
Logged by: Andrey Galkin
Email address: andvgal@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
On Mon, Dec 13, 2010 at 4:28 PM, Andrey G. <andvgal@gmail.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@gmail.com>:
Show quoted text
On Mon, Dec 13, 2010 at 4:28 PM, Andrey G. <andvgal@gmail.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 statementYeah, 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@gmail.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