To: ow <oneway_111@yahoo.com>
cc: pgsql-sql@postgresql.org
Subject: Re: Temp tables, reports in Postgresql (and other RDBMS) 
In-reply-to: <20061015160847.83246.qmail@web53909.mail.yahoo.com> 
References: <20061015160847.83246.qmail@web53909.mail.yahoo.com>
Comments: In-reply-to ow <oneway_111@yahoo.com>
	message dated "Sun, 15 Oct 2006 09:08:47 -0700"
Date: Sun, 15 Oct 2006 12:54:08 -0400
Message-ID: <23115.1160931248@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

ow <oneway_111@yahoo.com> writes:
> We are considering moving some reports from *** to Postgres. Our reports are
> written as stored procs in Transact-SQL and usually have the following
> structure:
> ...
> How would one rewrite the above logic in Postgresql?

If that's all you have to do, then SQL-language functions ought to be
enough.

If you need some procedural logic (if-then-else stuff) 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.  Possibly you
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.

There's also the possibility of using one of the other PLs such as
plperl, if you're familiar with any of the common scripting languages
they're based on.  The other PLs don't do implicit plan caching so
they won't have problems with temp tables; but it does mean knowing
still another language and putting up with some notational inconvenience.

			regards, tom lane