From: | "Dominic J(dot) Eidson" <sauron(at)the-infinite(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | CREATE TABLE from inside a function... |
Date: | 2000-08-16 05:05:31 |
Message-ID: | Pine.LNX.4.10.10008151036360.28080-100000@morannon.the-infinite.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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/
From | Date | Subject | |
---|---|---|---|
Next Message | sridhvenk | 2000-08-16 05:36:44 | Re: Tables Question |
Previous Message | Chris Bitmead | 2000-08-16 05:04:28 | Re: Search (select) options |