From: | "Andrey Galkin" <andvgal(at)gmail(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #5776: Unable to create view with parameter in PL/pgsql |
Date: | 2010-11-30 21:43:56 |
Message-ID: | 201011302143.oAULhuNl048717@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Nate Carson | 2010-12-01 02:46:07 | BUG #5777: Server crash with function out params returned as record |
Previous Message | Peter Eisentraut | 2010-11-30 19:21:43 | Re: BUG #5763: pg_hba.conf not honored |