Thread: "CREATE TEMPORARY TABLE" does not work in SQL language function?
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.
"David Johnston" <polobo@yahoo.com> writes: > The only difference between the following two "CREATE FUNCTION" command is > the language in which they are written. The reason the SQL function doesn't work is that SQL functions parse and analyze their whole bodies before beginning execution --- so "temptbl" doesn't exist yet when the SELECT is analyzed. It's not about temp tables as such, but rather that any DDL that affects the meaning of later statements is problematic. IIRC, there have been a few discussions about changing this behavior, but nothing's been done, partly out of fear of breaking existing applications. regards, tom lane