DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=J4fU5KO4CY1Ho0bKp/SVNJfoOmbzimyw+BGuGaEoXELmT/Cj0gzo4RQ4/oo7fp1S3QIDwK4gzYMt1/UABaUXKNLEAiiIdMrEnArU8Kr3U+NZIBclgmbXBx431Ix7yLO8xOGNqJh6afvpgn8yCcgrdnMCgUijs5nxakTgDb0yzXI=
	;
Message-ID: <20061015181058.22966.qmail@web53909.mail.yahoo.com>
Date: Sun, 15 Oct 2006 11:10:58 -0700 (PDT)
From: ow <oneway_111@yahoo.com>
Subject: Re: Temp tables, reports in Postgresql (and other RDBMS) 
To: pgsql-sql@postgresql.org
In-Reply-To: <23115.1160931248@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit


--- Tom Lane <tgl@sss.pgh.pa.us> wrote:

> If you need some procedural logic (if-then-else stuff)

Yes

> then you'd need
> to go over to plpgsql, which would be a bit of a pain because its habit
> of caching query plans doesn't play nice with temp tables.

Going to plpgsql is fine. What I'm trying to understand is how one goes about
writing reports in Postgres as per our scenario, be that with temp tables,
cursors, RECORDs, ROWTYPEs, etc.

In our RDBMS we use temp tables because it's the easiest way to take some table
as a base for your report and then extend it, as needed, with additional
columns, as I showed in step (1). Also, once the stored proc has run, the temp
table is automatically dropped, no need to worry about it. How does one go
about in Postgres? Do people use cursors or whatever instead of temp tables?

> could work some trick with ON COMMIT DELETE ROWS temp tables that are
> created once at the start of a session and are auto-emptied after each
> function by the ON COMMIT rule.  Since the tables themselves don't
> get dropped, there's no problem with plan invalidation.

Not sure I understand. Our plan was actually to drop the temp table ON COMMIT,
because it's stor proc that creates the temp table. If the session already has
the temp table then creating it in stored proc again will fail, no?

Thanks


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com