Re: Function Temp Table Woes - Mailing list 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