From: | Mario Splivalo <mario(dot)splivalo(at)mobart(dot)hr> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Using Temporary Tables in postgres functions |
Date: | 2007-01-25 14:39:14 |
Message-ID: | 1169735954.31872.38.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Sullivan | 2007-01-25 16:00:38 | Re: Using Temporary Tables in postgres functions |
Previous Message | Achilleas Mantzios | 2007-01-24 08:47:44 | Re: server process (PID xxx) was terminated by signal 7 |