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
Список
Дерево обсуждения
BUG #5776: Unable to create view with parameter in PL/pgsql "Andrey Galkin" <andvgal@gmail.com>
Re: BUG #5776: Unable to create view with parameter in PL/pgsql Robert Haas <robertmhaas@gmail.com>
Re: BUG #5776: Unable to create view with parameter in PL/pgsql "Andrey G." <andvgal@gmail.com>
Re: BUG #5776: Unable to create view with parameter in PL/pgsql Robert Haas <robertmhaas@gmail.com>
Re: BUG #5776: Unable to create view with parameter in PL/pgsql "Andrey G." <andvgal@gmail.com>
Re: BUG #5776: Unable to create view with parameter in PL/pgsql Robert Haas <robertmhaas@gmail.com>
Re: BUG #5776: Unable to create view with parameter in PL/pgsql Pavel Stehule <pavel.stehule@gmail.com>
Re: BUG #5776: Unable to create view with parameter in PL/pgsql "Andrey G." <andvgal@gmail.com>
Re: BUG #5776: Unable to create view with parameter in PL/pgsql Pavel Stehule <pavel.stehule@gmail.com>
Re: BUG #5776: Unable to create view with parameter in PL/pgsql "Andrey G." <andvgal@gmail.com>
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 по дате отправления
От: Peter Eisentraut
Дата:
От: Nate Carson
Дата:
FAQ