Sean Chittenden <sean(at)chittenden(dot)org> writes:
> CREATE FUNCTION s.f()
> RETURNS BIGINT
> EXTERNAL SECURITY DEFINER
> AS '
> BEGIN
> EXECUTE ''CREATE LOCAL TEMP TABLE t (
> a TEXT NOT NULL,
> b TEXT
> ) WITHOUT OIDS ON COMMIT DROP;'';
> EXECUTE ''CREATE UNIQUE INDEX t_key_udx ON t(a);'';
> INSERT INTO t (a, b) VALUES (''foo''::TEXT, ''bar''::TEXT);
This is not going to work more than once, because the INSERT caches
a plan that refers to the first-time-through temp table.
You could put the INSERT into an EXECUTE as well. Or use a different
PL language that doesn't cache plans.
regards, tom lane