
As per my previous post on this matter:
http://www.postgresql.org/mhonarc/pgsql-general/2000-08/msg00326.html

... somebody responded to me in private (IIRC - don't remember the name,
and can't find it in the archives), saying that this had been fixed in 7.1
- I assumed this meant it already was in CVS, and as it turns out, that's
not the case. Having gotten latest (CVS as of a few hours ago) working and
loaded the database(s), I still get the exact same errors as before... Any
clues as to if/when this will be fixed? (If it won't, or is of so little
priority that it's far in the future - a simple "Won't happen" will work.)


Thanks in advance,
-- 
Dominic J. Eidson
                                        "Baruk Khazad! Khazad ai-menu!" - Gimli
-------------------------------------------------------------------------------
http://www.the-infinite.org/              http://www.the-infinite.org/~dominic/


Wed, 30 Aug 2000 23:18:20 -0500 (CDT)
"Dominic J. Eidson" <sauron@the-infinite.org>
More about "CREATE TABLE" from inside a function/trigger...
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.)

			regards, tom lane

Thu, 31 Aug 2000 01:23:55 -0400
Tom Lane <tgl@sss.pgh.pa.us>
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 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@Yahoo.com #



Thu, 31 Aug 2000 10:07:57 -0500 (EST)
Jan Wieck <janwieck@Yahoo.com>
Re: More about "CREATE TABLE" from inside a function/trigger...