It seems that there is a problem when executing a dynamic commands containing a transaction...
Here's a simple example...
CREATE OR REPLACE FUNCTION fct_trig2()
RETURNS TRIGGER AS '
BEGIN
EXECUTE ''BEGIN;''
|| ''CREATE TABLE t2_'' || NEW.id::TEXT || ''(''
|| '' CONSTRAINT pkt2_'' || NEW.id::TEXT || '' PRIMARY KEY (id)''
|| '') INHERITS (t1);''
|| ''COMMIT;'';
RETURN NEW;
END;'
LANGUAGE 'plpgsql';CREATE TABLE t1 (id INT PRIMARY KEY);
CREATE TABLE t2 (id INT PRIMARY KEY); CREATE TRIGGER trig2 AFTER INSERT ON t2 FOR EACH ROW EXECUTE PROCEDURE fct_trig2();
test=# INSERT INTO t2 VALUES (1); ERROR: unexpected error -8 in EXECUTE of query "BEGIN;CREATE TABLE t2_1( CONSTRAINT pkt2_1 PRIMARY KEY (id)) INHERITS (t1);COMMIT;" CONTEXT: PL/pgSQL function "fct_trig2" line 2 at execute statement
If I don't use a transaction in the executed statement, there is no problem. Remark: In this example a transaction is not needed, but in my real usage of this I'm creating a table and granting permissions, and maybe also creating a view. So a transaction is preferable.
I've tested this on "PostgreSQL 7.4.1 on i386-pc-linux-gnu, compiled by GCC i386-linux-gcc (GCC) 3.3.2 (Debian)", Debian Sarge.
And also on "PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4", Debian Woody. On that system, the function fct_trig2() needs to return OPAQUE instead of TRIGGER.
Thanks, Pascal
PS: As I'm not subscribed to this ml, please keep me in the reply.
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
