BUG #5776: Unable to create view with parameter in PL/pgsql
От | Andrey Galkin |
---|---|
Тема | BUG #5776: Unable to create view with parameter in PL/pgsql |
Дата | |
Msg-id | 201011302143.oAULhuNl048717@wwwmaster.postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #5776: Unable to create view with parameter in PL/pgsql
|
Список | pgsql-bugs |
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
В списке pgsql-bugs по дате отправления: