Date: Tue, 8 Aug 2006 20:12:47 -0600
From: Michael Fuhr <mike@fuhr.org>
To: Chad Voelker <ch_rob@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Function Temp Table Woes
Message-ID: <20060809021247.GA14345@winnie.fuhr.org>
References: <20060808172402.25966.qmail@web31208.mail.mud.yahoo.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20060808172402.25966.qmail@web31208.mail.mud.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 | 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