DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=wbuQTG3RArGBgTWm0FJSIPdD36swwBfV2nIlZCvBS1GeHEKLaDn8djWKBHZcrZu06nyr7LiW+ZtY21vbKKkH1Lawc0+VGUOcjkcDBMvHPnS9GIdN1FIeFGnKZmuq+FQhg/b5NCfri0DlqIKfNYAHE/09W4opakXgJYzIyDYB/CQ=
	;
Message-ID: <20060808172402.25966.qmail@web31208.mail.mud.yahoo.com>
Date: Tue, 8 Aug 2006 10:24:02 -0700 (PDT)
From: Chad Voelker <ch_rob@yahoo.com>
Subject: Function Temp Table Woes
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

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