"CREATE TEMPORARY TABLE" does not work in SQL language function?

From: "David Johnston" <polobo(at)yahoo(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: "CREATE TEMPORARY TABLE" does not work in SQL language function?
Date: 2013-01-31 19:17:20
Message-ID: 012a01cdffe7$97807980$c6816c80$@yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

The only difference between the following two "CREATE FUNCTION" command is
the language in which they are written.

I do not recall and cannot seem to find after a quick search any limitation
regarding the use of "CREATE TEMPORARY TABLE" in an SQL language function.
It is not one of "BEGIN, COMMIT, ROLLBACK, SAVEPOINT" which are the only
explicit ones listed. DDL commands are neither explicitly prohibited nor
allowed but the phase "as well as other SQL commands" is too vague to be
authoritative.

version

PostgreSQL 9.0.4, compiled by Visual C++ build 1500, 64-bit

--SQL Language Function Fails

--SQL Error: ERROR: relation "temptbl" does not exist

--LINE 10: SELECT (one || two)::varchar AS result FROM temptbl;

SET LOCAL search_path = public;

CREATE FUNCTION temptabletest()

RETURNS varchar

AS $$

CREATE TEMPORARY TABLE temptbl

ON COMMIT DROP

AS SELECT '1'::varchar AS one, 'A'::varchar AS two;

SELECT (one || two)::varchar AS result FROM temptbl;

$$

LANGUAGE sql

STRICT

VOLATILE

;

While the following pl/pgsql function works:

SET LOCAL search_path = public;

CREATE FUNCTION temptabletest()

RETURNS varchar

AS $$

BEGIN

CREATE TEMPORARY TABLE temptbl

ON COMMIT DROP

AS SELECT '1'::varchar AS one, 'A'::varchar AS two;

RETURN (SELECT (one || two)::varchar AS result FROM temptbl);

END;

$$

LANGUAGE plpgsql

STRICT

VOLATILE

;

Thanks!

David J.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Vincent Veyron 2013-01-31 19:30:04 Re: SQL sentence to insert where updated rows is zero...
Previous Message Carlo Stonebanks 2013-01-31 18:59:20 Re: Pg & Tcl - is it dying out?