Re: [SQL] Using Temporary Tables in postgres functions
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Using Temporary Tables in postgres functions
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Using Temporary Tables in postgres functions
On Thu, 2007-01-25 at 11:09 -0500, Tom Lane wrote: Mario Splivalo [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Using Temporary Tables in postgres functions
Mario Splivalo [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] 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 * 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Using Temporary Tables in postgres functions
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 | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Using Temporary Tables in postgres functions
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? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Using Temporary Tables in postgres functions
Mario Splivalo [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
