Thread: Using Temporary Tables in postgres functions
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* FROMv_messages_full WHEREmessage_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
On Thu, Jan 25, 2007 at 03:39:14PM +0100, Mario Splivalo wrote: > 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: You need to build the temp table and EXECUTE the statement. The problem is that the plan is cached for later re-use. Since the cached plan has the id of a table that no longer exists, you get this error. See the PL/pgSQL part of the manual for more on this. A -- Andrew Sullivan | ajs@crankycanuck.ca Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris
On Thu, 25 Jan 2007, Mario Splivalo wrote: > 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; It seems like the sql function checker is unhappy with the above. Does it actually work if you turn off the check_function_bodies configuration variable, create the function and then call it?
Mario Splivalo <mario.splivalo@mobart.hr> writes:
> Am I doing something wrong here, or there is no way of using temporary
> tables within 'sql' written functions?
I believe the problem is that for a SQL function we parse the whole
function body before executing any of it. So you'd need to split this
into two separate functions.
regards, tom lane
On Thu, 2007-01-25 at 08:03 -0800, Stephan Szabo wrote: > On Thu, 25 Jan 2007, Mario Splivalo wrote: > > > 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; > > It seems like the sql function checker is unhappy with the above. Does it > actually work if you turn off the check_function_bodies configuration > variable, create the function and then call it? Yes, when I do 'set check_function_bodies to false;' then I can create the function but I'm unable to execute it: testdb1=# select * from func1(); ERROR: relation "tmptbl" does not exist CONTEXT: SQL function "func1" during startup Bummer. In the end I wrote the function using plpgsql. Now, is there any performance impact on using plpgsql instead of sql in simple scenarios as in func1() example? I guess there should be some, as minimas as it can be, but have no way of actualy knowing that. Mike
On Thu, 2007-01-25 at 11:00 -0500, Andrew Sullivan wrote: > On Thu, Jan 25, 2007 at 03:39:14PM +0100, Mario Splivalo wrote: > > 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: > > You need to build the temp table and EXECUTE the statement. The > problem is that the plan is cached for later re-use. Since the > cached plan has the id of a table that no longer exists, you get this > error. See the PL/pgSQL part of the manual for more on this. Yes, I'm well aware of that, and learned to live with it :) The only drawback is that my plpgsql code looks realy uqly, because of the creation of the string variables containing the actuall SQL code that deals with the temporary tables used. Therefore I'm trying not to use temp tables as much as I can. A table-type variables would be realy nice thing to have, I guess they would exist only in memory, and for some complicated OLTP stuff those could be realy realy hand. Just a wish, in a way :) Mike
On Thu, 2007-01-25 at 11:09 -0500, Tom Lane wrote: > Mario Splivalo <mario.splivalo@mobart.hr> writes: > > Am I doing something wrong here, or there is no way of using temporary > > tables within 'sql' written functions? > > I believe the problem is that for a SQL function we parse the whole > function body before executing any of it. So you'd need to split this > into two separate functions. Having two function complicates, a bit, interface between applicaation and the database. But, then again, If I split it in two functions, then one with the UPDATE still can't find temp table referenced in other function, right? I have no problem writing func1() example in plpgsql, it just seemed to me that using sql instead of plpgsql (when I actually can use sql) gives me a little performance improvement. Mike
Mario Splivalo <mario.splivalo@mobart.hr> writes:
> On Thu, 2007-01-25 at 11:09 -0500, Tom Lane wrote:
>> I believe the problem is that for a SQL function we parse the whole
>> function body before executing any of it. So you'd need to split this
>> into two separate functions.
> Having two function complicates, a bit, interface between applicaation
> and the database. But, then again, If I split it in two functions, then
> one with the UPDATE still can't find temp table referenced in other
> function, right?
It can as long as the table already exists when the function is entered.
regards, tom lane