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

From: vibhuti nataraj <vvnataraj(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Unable to CREATE SCHEMA and INSERT data in table in that schema in same EXECUTE
Date: 2013-08-29 21:59:23
Message-ID: CAMFwpEs0WAzed-0pFDEDKGnBKsC01ZABYEsg7xfKJY+J1rdnTA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jeff Davis 2013-08-30 01:23:09 Re: Why is NULL = unbounded for rangetypes?
Previous Message Tom Lane 2013-08-29 20:39:09 Re: question about age()