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. -- ============================================================================ 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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs