Thread: Function Temp Table Woes
Hello,
I'm a newbie to plpgsql functions so any help I can
get would be appreciated. I have a little bit of a
history with MS SQL Server 2000, so this may be where
I'm messed up ;)
The Goal: Return a set of records from one table based
on entries in a temporary table. I'd expect this to be
a commonly requested functionality. After reading the
docs, support lists and googling, I haven't come up
with a clean solution. My query is at the end of this
post.
I've come to the conclusion that returning a REFCURSOR
is the best approach, but I will take other
suggestions. My current issue is that the temp table
(tt_occ_units) is not being dropped after commit. The
second calling of this function produces the 'relation
already exists' error.
I've learned that there is an implicit transaction
around every function and I was hoping that
transaction would force the commit and drop the table.
That tells me that the table shouldn't be there if I
immediately call it again. Otherwise, I am calling
this function using SQL similar to: BEGIN; SELECT
sUnitsByOccStatus('c', FALSE, '08/07/2006'); FETCH ALL
IN c; COMMIT;
Note that I am actually making this call from PHP.
But, after I get over this hurdle, I'm sure I can make
it work from there.
Any thoughts?
Thanks in advance.
-Chad
--Current function text
CREATE OR REPLACE FUNCTION sUnitsByOccStatus (cur
REFCURSOR, occupied BOOLEAN, refDate DATE) RETURNS
REFCURSOR AS $$ BEGIN CREATE TEMP TABLE tt_occ_units (unit_id INTEGER)
ON COMMIT DROP; -- Get ids for all available units SELECT u.id INTO tt_occ_units FROM tbl_unit u
INNERJOIN tbl_tenant_unit tu ON u.id =
tu.unit_id INNER JOIN tbl_rent r ON tu.rent_id = r.id WHERE r.date_start < refDate AND r.date_end
> refDate; -- Return tbl_unit records for avail or not
avail depending on what the caller wants. IF occupied THEN OPEN cur FOR SELECT u.* FROM tbl_unit u
WHEREu.id IN (select unit_id from
tt_occ_units); ELSE OPEN cur FOR SELECT u.* FROM tbl_unit u WHERE u.id NOT IN (select unit_id from
tt_occ_units); END IF; -- IF RETURN cur; END; $$ LANGUAGE plpgsql STRICT VOLATILE;
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
On Tue, Aug 08, 2006 at 10:24:02AM -0700, Chad Voelker wrote: > The Goal: Return a set of records from one table based > on entries in a temporary table. I'd expect this to be > a commonly requested functionality. After reading the > docs, support lists and googling, I haven't come up > with a clean solution. My query is at the end of this > post. > > I've come to the conclusion that returning a REFCURSOR > is the best approach, but I will take other suggestions. A set-returning function would probably be easier to use; search for examples of functions declared with "RETURNS SETOF <tablename>". Using views instead of a function might also be possible. > My current issue is that the temp table (tt_occ_units) is > not being dropped after commit. The second calling of this > function produces the 'relation already exists' error. The temporary table is indeed being dropped; the problem is that the function creates another non-temporary table with the same name and that table isn't being dropped: > CREATE TEMP TABLE tt_occ_units (unit_id INTEGER) > ON COMMIT DROP; > -- Get ids for all available units > SELECT u.id INTO tt_occ_units The first command above creates the temporary table but then SELECT INTO creates the non-temporary table with the same name but in a different schema. Here's a simple example that shows what's happening: CREATE FUNCTION test() RETURNS void AS $$ BEGIN CREATE TEMP TABLE foo (x integer) ON COMMIT DROP; SELECT x INTO foo FROM (SELECT 1::integer AS x) AS s; END; $$ LANGUAGE plpgsql; test=> \dt *.foo No matching relations found. test=> BEGIN; BEGIN test=> SELECT test();test ------ (1 row) test=> \dt *.foo List of relations Schema | Name | Type | Owner -----------+------+-------+-------pg_temp_1 | foo | table | mfuhrpublic | foo | table | mfuhr (2 rows) test=> COMMIT; COMMIT test=> \dt *.foo List of relationsSchema | Name | Type | Owner --------+------+-------+-------public | foo | table | mfuhr (1 row) Notice that the temporary table went away after the commit but that the non-temporary table remained -- that's the table that's giving you trouble. Instead of using SELECT INTO you could use INSERT with a query, like this: CREATE TEMP TABLE <tablename> ... INSERT INTO <tablename> SELECT ... However, this still has a problem: after the first time you call the function subsequent calls will fail with "relation with OID XXXXX does not exist." See the FAQ for the reason and how to avoid it: http://www.postgresql.org/docs/faqs.FAQ.html#item4.19 Instead of using a temporary table, consider incorporating that query directly into the main query/queries. -- Michael Fuhr
Michael- Nice explaination of what was happening to the temp table. Thank you. -Chad --- Michael Fuhr <mike@fuhr.org> wrote: > On Tue, Aug 08, 2006 at 10:24:02AM -0700, Chad > Voelker wrote: > > The Goal: Return a set of records from one table > based > > on entries in a temporary table. I'd expect this > to be > > a commonly requested functionality. After reading > the > > docs, support lists and googling, I haven't come > up > > with a clean solution. My query is at the end of > this > > post. > > > > I've come to the conclusion that returning a > REFCURSOR > > is the best approach, but I will take other > suggestions. > > A set-returning function would probably be easier to > use; search > for examples of functions declared with "RETURNS > SETOF <tablename>". > Using views instead of a function might also be > possible. > > > My current issue is that the temp table > (tt_occ_units) is > > not being dropped after commit. The second calling > of this > > function produces the 'relation already exists' > error. > > The temporary table is indeed being dropped; the > problem is that > the function creates another non-temporary table > with the same name > and that table isn't being dropped: > > > CREATE TEMP TABLE tt_occ_units (unit_id > INTEGER) > > ON COMMIT DROP; > > -- Get ids for all available units > > SELECT u.id INTO tt_occ_units > > The first command above creates the temporary table > but then SELECT > INTO creates the non-temporary table with the same > name but in a > different schema. Here's a simple example that > shows what's happening: > > CREATE FUNCTION test() RETURNS void AS $$ > BEGIN > CREATE TEMP TABLE foo (x integer) ON COMMIT > DROP; > SELECT x INTO foo FROM (SELECT 1::integer AS x) > AS s; > END; > $$ LANGUAGE plpgsql; > > test=> \dt *.foo > No matching relations found. > test=> BEGIN; > BEGIN > test=> SELECT test(); > test > ------ > > (1 row) > > test=> \dt *.foo > List of relations > Schema | Name | Type | Owner > -----------+------+-------+------- > pg_temp_1 | foo | table | mfuhr > public | foo | table | mfuhr > (2 rows) > > test=> COMMIT; > COMMIT > test=> \dt *.foo > List of relations > Schema | Name | Type | Owner > --------+------+-------+------- > public | foo | table | mfuhr > (1 row) > > Notice that the temporary table went away after the > commit but that > the non-temporary table remained -- that's the table > that's giving > you trouble. Instead of using SELECT INTO you could > use INSERT > with a query, like this: > > CREATE TEMP TABLE <tablename> ... > INSERT INTO <tablename> SELECT ... > > However, this still has a problem: after the first > time you call > the function subsequent calls will fail with > "relation with OID > XXXXX does not exist." See the FAQ for the reason > and how to avoid > it: > > http://www.postgresql.org/docs/faqs.FAQ.html#item4.19 > > Instead of using a temporary table, consider > incorporating that > query directly into the main query/queries. > > -- > Michael Fuhr > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com