Skip site navigation (1) Skip section navigation (2)

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

From: Jan Wieck <janwieck(at)Yahoo(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Dominic J(dot) Eidson" <sauron(at)the-infinite(dot)org>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: More about "CREATE TABLE" from inside a function/trigger...
Date: 2000-08-31 15:07:57
Message-ID: 200008311507.KAA17350@jupiter.jw.home (view raw or flat)
Thread:
Lists: pgsql-hackers
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 can occur
    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 19276 1
      INSERT INTO t1 VALUES (2, 22, 222);
      INSERT 19277 1
      INSERT INTO t1 VALUES (3, 33, 333);
      INSERT 19278 1
      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'';
          RETURN result;
        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 in source
    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(at)Yahoo(dot)com #



In response to

pgsql-hackers by date

Next:From: Mikheev, VadimDate: 2000-08-31 16:07:02
Subject: RE: WAL
Previous:From: Zeugswetter Andreas SBDate: 2000-08-31 15:00:59
Subject: AW: AW: Backend-internal SPI operations

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group