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);
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 '
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''
result = sumrec.sum;
EXECUTE ''DROP TABLE f1_temp'';
' LANGUAGE 'plpgsql';
SELECT f1('t1', 'a') AS "sum t1.a";
SELECT f1('t1', 'b') AS "sum t1.b";
SELECT f1('t1', 'c') AS "sum t1.c";
So PL/pgSQL can now execute dynamic SQL including utility
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.
# 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, Vadim||Date: 2000-08-31 16:07:02|
|Subject: RE: WAL|
|Previous:||From: Zeugswetter Andreas SB||Date: 2000-08-31 15:00:59|
|Subject: AW: AW: Backend-internal SPI operations |