Subject: Using Temporary Tables in postgres functions
From: Mario Splivalo <mario.splivalo@mobart.hr>
Reply-To: mario.splivalo@mobart.hr
To: pgsql-sql@postgresql.org
Content-Type: text/plain
Organization: Mob-Art d.o.o.
Date: Thu, 25 Jan 2007 15:39:14 +0100
Message-Id: <1169735954.31872.38.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

When I try to use TEMPORARY TABLE within postgres functions (using 'sql'
as a function language), I can't because postgres can't find that
temporary table. Consider this example:

CREATE FUNCTION func1() RETURNS SETOF v_messages_full AS $BODY$
CREATE TEMPORARY TABLE tmpTbl
AS
SELECT 
	message_id
FROM 
	cached_messages
WHERE 
	billing_status = 2;


UPDATE cached_messages SET billing_status = 1 WHERE message_id IN
(SELECT message_id FROM tmpTbl);

SELECT
	*
FROM
	v_messages_full
WHERE
	message_id IN (SELECT message_id FROM tmpTbl);
$BODY$ LANGUAGE 'sql' VOLATILE SECURITY DEFINER;

When I try to execute above CREATE FUNCTION statement postgres gives me
this:
ERROR:  relation "tmptbl" does not exist
CONTEXT:  SQL function "func1"

If I rewrite func1() using 'plpgsq' I have no troubles creating
temporary tables, I just need to use EXEC when referencing to those
temporary tables (which is cumbersome, but there it is).

Am I doing something wrong here, or there is no way of using temporary
tables within 'sql' written functions?

	Mike