Thread: Unable to CREATE SCHEMA and INSERT data in table in that schema in same EXECUTE
Unable to CREATE SCHEMA and INSERT data in table in that schema in same EXECUTE
From
vibhuti nataraj
Date:
Hi,
I am using server 9.0.4, I am trying to
1. Create a new schema,
2. Create a table under that schema, and
3. Insert data in that schema,
In the same EXECUTE and its failing. If I try
CREATE OR REPLACE FUNCTION pg_temp.test( )
RETURNS void LANGUAGE plpgsql AS
$BODY$
BEGIN
EXECUTE 'CREATE SCHEMA test; CREATE TABLE test.t (id integer ); INSERT INTO test.t (id) VALUES (0);';
END;
$BODY$;
select pg_temp.test( );
I get a failure with the following error.
ERROR: schema "test" does not exist
LINE 1: ...t; CREATE TABLE test.t (id integer ); INSERT INTO test.t (id...
^
However, the same thing works if use two EXECUTEs in the same transaction.
CREATE OR REPLACE FUNCTION pg_temp.test( )
RETURNS void LANGUAGE plpgsql AS
$BODY$
BEGIN
EXECUTE 'CREATE SCHEMA test; CREATE TABLE test.t (id integer );';
EXECUTE 'INSERT INTO test.t (id) VALUES (0);';
END;
$BODY$;
select pg_temp.test( );
Unable to understand the difference between the two. Will appreciate if someone can help me here.
Thanks.
Best regards,
Vibhuti
Re: Unable to CREATE SCHEMA and INSERT data in table in that schema in same EXECUTE
From
Tom Lane
Date:
vibhuti nataraj <vvnataraj@gmail.com> writes:
> EXECUTE 'CREATE SCHEMA test; CREATE TABLE test.t (id integer ); INSERT INTO
> test.t (id) VALUES (0);';
That's not going to work because the whole string is parsed before any of
it is executed. In particular, parse analysis of the INSERT is going to
spit up because neither the table nor even the schema exist yet.
> EXECUTE 'CREATE SCHEMA test; CREATE TABLE test.t (id integer );';
> EXECUTE 'INSERT INTO test.t (id) VALUES (0);';
The reason this happens to work is that CREATE TABLE is a utility
statement, which doesn't do any parse analysis to speak of, in
particular it doesn't notice at parse time whether the mentioned
schema exists. However, that's an implementation detail that
could change from release to release. By and large, trying to
EXECUTE multiple statements in one query string is something best
avoided, especially if any of them are DDL.
regards, tom lane