From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | "Dominic J(dot) Eidson" <sauron(at)the-infinite(dot)org> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: CREATE TABLE from inside a function... |
Date: | 2000-08-16 16:11:03 |
Message-ID: | Pine.BSF.4.10.10008160906050.92055-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)bigpanda(dot)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)...
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2000-08-16 16:16:47 | Re: Unimpletmented features |
Previous Message | Ross J. Reedstrom | 2000-08-16 15:51:25 | Re: rebuilding a table from a datafile |