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