pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFunction Temp Table Woes
3+ messages / 2 participants
[nested] [flat]
* Function Temp Table Woes
@ 2006-08-08 17:24 Chad Voelker <ch_rob@yahoo.com>
2006-08-09 02:12 ` Re: Function Temp Table Woes Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 3+ messages in thread
From: Chad Voelker @ 2006-08-08 17:24 UTC (permalink / raw)
To: pgsql-sql
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
INNER JOIN 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
WHERE u.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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Function Temp Table Woes
2006-08-08 17:24 Function Temp Table Woes Chad Voelker <ch_rob@yahoo.com>
@ 2006-08-09 02:12 ` Michael Fuhr <mike@fuhr.org>
2006-08-10 11:52 ` Re: Function Temp Table Woes Chad Voelker <ch_rob@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Michael Fuhr @ 2006-08-09 02:12 UTC (permalink / raw)
To: Chad Voelker <ch_rob@yahoo.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Function Temp Table Woes
2006-08-08 17:24 Function Temp Table Woes Chad Voelker <ch_rob@yahoo.com>
2006-08-09 02:12 ` Re: Function Temp Table Woes Michael Fuhr <mike@fuhr.org>
@ 2006-08-10 11:52 ` Chad Voelker <ch_rob@yahoo.com>
0 siblings, 0 replies; 3+ messages in thread
From: Chad Voelker @ 2006-08-10 11:52 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-08-10 11:52 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-08 17:24 Function Temp Table Woes Chad Voelker <ch_rob@yahoo.com>
2006-08-09 02:12 ` Michael Fuhr <mike@fuhr.org>
2006-08-10 11:52 ` Chad Voelker <ch_rob@yahoo.com>
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