pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUsing Temporary Tables in postgres functions
8+ messages / 4 participants
[nested] [flat]
* Using Temporary Tables in postgres functions
@ 2007-01-25 14:39 Mario Splivalo <mario.splivalo@mobart.hr>
2007-01-25 16:00 ` Re: Using Temporary Tables in postgres functions Andrew Sullivan <ajs@crankycanuck.ca>
2007-01-25 16:03 ` Re: Using Temporary Tables in postgres functions Stephan Szabo <sszabo@megazone.bigpanda.com>
2007-01-25 16:09 ` Re: Using Temporary Tables in postgres functions Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 8+ messages in thread
From: Mario Splivalo @ 2007-01-25 14:39 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Using Temporary Tables in postgres functions
2007-01-25 14:39 Using Temporary Tables in postgres functions Mario Splivalo <mario.splivalo@mobart.hr>
@ 2007-01-25 16:00 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-01-26 10:47 ` Re: Using Temporary Tables in postgres functions Mario Splivalo <mario.splivalo@mobart.hr>
2 siblings, 1 reply; 8+ messages in thread
From: Andrew Sullivan @ 2007-01-25 16:00 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Using Temporary Tables in postgres functions
2007-01-25 14:39 Using Temporary Tables in postgres functions Mario Splivalo <mario.splivalo@mobart.hr>
2007-01-25 16:00 ` Re: Using Temporary Tables in postgres functions Andrew Sullivan <ajs@crankycanuck.ca>
@ 2007-01-26 10:47 ` Mario Splivalo <mario.splivalo@mobart.hr>
0 siblings, 0 replies; 8+ messages in thread
From: Mario Splivalo @ 2007-01-26 10:47 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Using Temporary Tables in postgres functions
2007-01-25 14:39 Using Temporary Tables in postgres functions Mario Splivalo <mario.splivalo@mobart.hr>
@ 2007-01-25 16:03 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2007-01-26 10:46 ` Re: Using Temporary Tables in postgres functions Mario Splivalo <mario.splivalo@mobart.hr>
2 siblings, 1 reply; 8+ messages in thread
From: Stephan Szabo @ 2007-01-25 16:03 UTC (permalink / raw)
To: Mario Splivalo <mario.splivalo@mobart.hr>; +Cc: pgsql-sql
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?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Using Temporary Tables in postgres functions
2007-01-25 14:39 Using Temporary Tables in postgres functions Mario Splivalo <mario.splivalo@mobart.hr>
2007-01-25 16:03 ` Re: Using Temporary Tables in postgres functions Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2007-01-26 10:46 ` Mario Splivalo <mario.splivalo@mobart.hr>
0 siblings, 0 replies; 8+ messages in thread
From: Mario Splivalo @ 2007-01-26 10:46 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Using Temporary Tables in postgres functions
2007-01-25 14:39 Using Temporary Tables in postgres functions Mario Splivalo <mario.splivalo@mobart.hr>
@ 2007-01-25 16:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-01-26 10:51 ` Re: Using Temporary Tables in postgres functions Mario Splivalo <mario.splivalo@mobart.hr>
2 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2007-01-25 16:09 UTC (permalink / raw)
To: mario.splivalo@mobart.hr; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Using Temporary Tables in postgres functions
2007-01-25 14:39 Using Temporary Tables in postgres functions Mario Splivalo <mario.splivalo@mobart.hr>
2007-01-25 16:09 ` Re: Using Temporary Tables in postgres functions Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-01-26 10:51 ` Mario Splivalo <mario.splivalo@mobart.hr>
2007-01-26 14:57 ` Re: Using Temporary Tables in postgres functions Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Mario Splivalo @ 2007-01-26 10:51 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Using Temporary Tables in postgres functions
2007-01-25 14:39 Using Temporary Tables in postgres functions Mario Splivalo <mario.splivalo@mobart.hr>
2007-01-25 16:09 ` Re: Using Temporary Tables in postgres functions Tom Lane <tgl@sss.pgh.pa.us>
2007-01-26 10:51 ` Re: Using Temporary Tables in postgres functions Mario Splivalo <mario.splivalo@mobart.hr>
@ 2007-01-26 14:57 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2007-01-26 14:57 UTC (permalink / raw)
To: mario.splivalo@mobart.hr; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2007-01-26 14:57 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-01-25 14:39 Using Temporary Tables in postgres functions Mario Splivalo <mario.splivalo@mobart.hr>
2007-01-25 16:00 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-01-26 10:47 ` Mario Splivalo <mario.splivalo@mobart.hr>
2007-01-25 16:03 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2007-01-26 10:46 ` Mario Splivalo <mario.splivalo@mobart.hr>
2007-01-25 16:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-01-26 10:51 ` Mario Splivalo <mario.splivalo@mobart.hr>
2007-01-26 14:57 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox