[GENERAL] Unable to CREATE SCHEMA and INSERT data in table in that schema in same EXECUTE

[vibhuti nataraj]

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