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

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 #