Thread: DDL+SQL in PL/pgSQL EXECUTE

Is it a bug or by design? I could not find what behaviour is correct for 
these statements in PL/pgSQL:

This function just executes a string.
CREATE OR REPLACE FUNCTION _EXEC(query VARCHAR)
RETURNS VOID AS $$
BEGIN EXECUTE query;
END;
$$ LANGUAGE 'plpgsql';

1. Works ok.
BEGIN WORK;
SELECT _EXEC('CREATE TABLE T(ID INTEGER); CREATE INDEX T_IDX ON T(ID)');
ROLLBACK;

2. Works ok.
BEGIN WORK;
SELECT _EXEC('CREATE TABLE T(ID INTEGER); ALTER TABLE T ADD COLUMN ID2 
INTEGER; CREATE INDEX T_IDX2 ON T(ID2)');
ROLLBACK;

3. ERROR:  relation "t" does not exist
SELECT _EXEC('CREATE TABLE T(ID INTEGER); INSERT INTO T(ID) VALUES (1)');

4. Inserts NULL value into ID column instead of default 10.
BEGIN WORK;
CREATE TABLE T(ID INTEGER);
SELECT _EXEC('ALTER TABLE T ALTER COLUMN ID SET DEFAULT(10); INSERT INTO 
T DEFAULT VALUES');
SELECT * FROM T;

Vlad Arkhipov <arhipov@dc.baikal.ru> writes:
> 3. ERROR:  relation "t" does not exist
> SELECT _EXEC('CREATE TABLE T(ID INTEGER); INSERT INTO T(ID) VALUES (1)');

> 4. Inserts NULL value into ID column instead of default 10.
> BEGIN WORK;
> CREATE TABLE T(ID INTEGER);
> SELECT _EXEC('ALTER TABLE T ALTER COLUMN ID SET DEFAULT(10); INSERT INTO 
> T DEFAULT VALUES');

Commands submitted in a single string are typically parsed and planned
before they are executed (though the behavior probably depends on
context and which PG version you're talking about).  My advice is
don't do that.

If we were to do anything about it, it'd probably be to ban
multi-statement EXECUTE on security grounds ...
        regards, tom lane