Обсуждение: 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-----