From: Tom Lane <tgl@sss.pgh.pa.us>
To: ow <oneway_111@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Temp tables, reports in Postgresql (and other RDBMS)
Date: Sun, 15 Oct 2006 12:54:08 -0400
Message-ID: <23115.1160931248@sss.pgh.pa.us> (raw)
In-Reply-To: <20061015160847.83246.qmail@web53909.mail.yahoo.com>
References: <20061015160847.83246.qmail@web53909.mail.yahoo.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, oneway_111@yahoo.com
Subject: Re: Temp tables, reports in Postgresql (and other RDBMS)
In-Reply-To: <23115.1160931248@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox