Re: More about "CREATE TABLE" from inside a function/trigger...

Tom Lane wrote:
> I believe you could do CREATE TABLE from inside a pltcl or plperl
> function today.  plpgsql won't work because it tries to cache query
> plans for repeated execution --- which essentially means that you
> can only substitute parameters for data values, not for table names
> or field names or other structural aspects of a query.  But the other
> two just treat queries as dynamically-generated strings, so you can
> do anything you want in those languages.  (At a performance price,
> of course: no caching.  There ain't no such thing as a free lunch.)
   You're right - any longer not :-)
   I  just  committed a little patch adding an EXECUTE statement   to PL/pgSQL.  It takes an expression  (preferrably
resulting  in a string which is a valid SQL command) and executes it via   SPI_exec() (no prepare/cache).
 
   It can occur as is,  where  the  querystrings  execution  via   SPI_exec()  must  NOT  return  SPI_OK_SELECT. Or it
canoccur   instead of  the  SELECT  part  of  a  FOR  loop,  where  it's   execution via SPI_exec() MUST return
SPI_OK_SELECT.
   Here's the output from a little test:
     CREATE TABLE t1 (a integer, b integer, c integer);     CREATE     INSERT INTO t1 VALUES (1, 11, 111);     INSERT
192761     INSERT INTO t1 VALUES (2, 22, 222);     INSERT 19277 1     INSERT INTO t1 VALUES (3, 33, 333);     INSERT
192781     CREATE FUNCTION f1 (name, name) RETURNS integer AS '       DECLARE         sumrec  record;         result
integer;      BEGIN         EXECUTE ''CREATE TEMP TABLE f1_temp (val integer)'';         EXECUTE ''INSERT INTO f1_temp
SELECT'' || $2 ||                 '' FROM '' || $1;         FOR sumrec IN EXECUTE ''SELECT sum(val) AS sum FROM
f1_temp''        LOOP           result = sumrec.sum;         END LOOP;         EXECUTE ''DROP TABLE f1_temp'';
RETURNresult;       END;     ' LANGUAGE 'plpgsql';     CREATE     SELECT f1('t1', 'a') AS "sum t1.a";      sum t1.a
----------            6     (1 row)
 
     SELECT f1('t1', 'b') AS "sum t1.b";      sum t1.b     ----------            66     (1 row)
     SELECT f1('t1', 'c') AS "sum t1.c";      sum t1.c     ----------           666     (1 row)
   So  PL/pgSQL  can  now  execute dynamic SQL including utility   statements.
   Who adds this new feature to the docs? I don't have the  jade   tools  installed  and  don't  like to fiddle around
insource   files where I cannot check the results.
 
   I think two little functions  for  quoting  of  literals  and   identifiers might be handy. Like
           quote_ident('silly "TEST" table')
           returns '"silly ""TEST"" table"'
   so that the querystring build in the above sample can be done   in a bullet proof way.
   Comments?


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #