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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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 #
From | Date | Subject | |
---|---|---|---|
Next Message | Mikheev, Vadim | 2000-08-31 16:07:02 | RE: WAL |
Previous Message | Zeugswetter Andreas SB | 2000-08-31 15:00:59 | AW: AW: Backend-internal SPI operations |