Re: More about "CREATE TABLE" from inside a function/trigger... - Mailing list pgsql-hackers
From | Jan Wieck |
---|---|
Subject | Re: More about "CREATE TABLE" from inside a function/trigger... |
Date | |
Msg-id | 200008311507.KAA17350@jupiter.jw.home Whole thread Raw |
In response to | Re: More about "CREATE TABLE" from inside a function/trigger... (Tom Lane <tgl@sss.pgh.pa.us>) |
List | 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 canoccur 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 192761 INSERT INTO t1 VALUES (2, 22, 222); INSERT 19277 1 INSERT INTO t1 VALUES (3, 33, 333); INSERT 192781 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''; RETURNresult; 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 insource 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 #
pgsql-hackers by date: