Обсуждение: CREATE TABLE from inside a function...
Is there a specific reason why one can not do a CREATE TABLE from inside a pl/pgsql function? I've been trying to get some triggers working, and they all keep erroring with the same error... The following schema: CREATE TABLE "spares_dbs" ( "dbs_id" serial, "dbs_name" character varying(20), "dbs_desc" text ); And the following trigger: CREATE FUNCTION spares_trig_insert_dbs() RETURNS opaque AS ' BEGIN IF NEW.dbs_name ISNULL THEN RAISE EXCEPTION ''% cannot be NULL'', NEW.dbs_name; END IF; CREATE TABLE NEW.dbs_name ( id int, oem text, price float ); RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER spares_test BEFORE INSERT ON spares_dbs FOR EACH ROW EXECUTE PROCEDURE spares_trig_insert_dbs(); spares=# INSERT INTO spares_dbs VALUES ( NULL, 'test', 'Test table' ); ERROR: parser: parse error at or near "$1" Looking at the postmaster log (-d 6), reveals the following: StartTransactionCommand query: INSERT INTO spares_dbs VALUES ( NULL, 'test', 'Test table' ); parser outputs: ... snip parser output ... ProcessQuery query: SELECT $1 ISNULL parser outputs: ... snip more parser output ... query: CREATE TABLE $1 ( id int, oem text, price float ) ERROR: parser: parse error at or near "$1" DEBUG: Last error occured while executing PL/pgSQL function spares_trig_insert_dbs DEBUG: line 5 at SQL statement AbortCurrentTransaction My understand was it should have replaced "NEW.dbs_name" with "test" (according to the INSERT data)... -- Dominic J. Eidson "Baruk Khazad! Khazad ai-menu!" - Gimli ------------------------------------------------------------------------------- http://www.the-infinite.org/ http://www.the-infinite.org/~dominic/
Actually there are two problems here, one IIRC you cannot use commands like CREATE TABLE (etc) from PL/PGSQL (You'll get a cannot copy node) Two, PL/PGSQL wouldn't probably support substituting a variable for the table name anyway (since it also doesn't work for select * from <variable>). You might be able to do this in pl/tcl, but I'm not sure since I don't know tcl. Stephan Szabo sszabo@bigpanda.com On Wed, 16 Aug 2000, Dominic J. Eidson wrote: > > Is there a specific reason why one can not do a CREATE TABLE from inside a > pl/pgsql function? I've been trying to get some triggers working, and they > all keep erroring with the same error... > > The following schema: > CREATE TABLE "spares_dbs" ( > "dbs_id" serial, > "dbs_name" character varying(20), > "dbs_desc" text > ); > > And the following trigger: > > CREATE FUNCTION spares_trig_insert_dbs() RETURNS opaque AS ' > BEGIN > IF NEW.dbs_name ISNULL THEN > RAISE EXCEPTION ''% cannot be NULL'', NEW.dbs_name; > END IF; > > CREATE TABLE NEW.dbs_name ( id int, oem text, price float ); > RETURN NEW; > END; > ' LANGUAGE 'plpgsql'; > > CREATE TRIGGER spares_test BEFORE INSERT ON spares_dbs FOR EACH ROW > EXECUTE PROCEDURE spares_trig_insert_dbs(); > > spares=# INSERT INTO spares_dbs VALUES ( NULL, 'test', 'Test table' ); > ERROR: parser: parse error at or near "$1" > > Looking at the postmaster log (-d 6), reveals the following: > > StartTransactionCommand > query: INSERT INTO spares_dbs VALUES ( NULL, 'test', 'Test table' ); > parser outputs: > > ... snip parser output ... > > ProcessQuery > query: SELECT $1 ISNULL > parser outputs: > > ... snip more parser output ... > > query: CREATE TABLE $1 ( id int, oem text, price float ) > ERROR: parser: parse error at or near "$1" > DEBUG: Last error occured while executing PL/pgSQL function > spares_trig_insert_dbs > DEBUG: line 5 at SQL statement > AbortCurrentTransaction > > My understand was it should have replaced "NEW.dbs_name" with "test" > (according to the INSERT data)...
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > IIRC you cannot use commands > like CREATE TABLE (etc) from PL/PGSQL (You'll get a cannot copy > node) Actually, this is fixed in 7.1. Ian -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.1 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE5my88fn9ub9ZE1xoRAhBlAKDZiHmPn4pA/HO+YkwSi2ZgmmwN5wCeLQGy yuDczpWIW8MIgWhR6i4lQS8= =W35n -----END PGP SIGNATURE-----