pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTemp tables, reports in Postgresql (and other RDBMS)
3+ messages / 2 participants
[nested] [flat]
* Temp tables, reports in Postgresql (and other RDBMS)
@ 2006-10-15 16:08 ow <oneway_111@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: ow @ 2006-10-15 16:08 UTC (permalink / raw)
To: pgsql-sql
Hi,
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:
CREATE PROCEDURE someReportProc AS
/* Purpose: Creates a report based on Table1.
*
* Overview of what will be done:
* 1) create a temp table based on Table1 (that has 3 columns) + 2 extra
columns
* (col4 and col5) and populate the temp table with data from Table1
* 2) run some logic to populate 1st extra column (col4)
* 3) run some logic to populate 2nd extra column (col5)
* 4) run select to return results to the client
*/
BEGIN
-- step 1) create temp table #tempReportData
SELECT Table1.*, space(1) as col4, 0 as col5
INTO #tempReportData
FROM Table1
WHERE ....
-- step 2)
UPDATE #tempReportData
SET col4 = Table4.someColumn
FROM Table4
WHERE #tempReportData.id = Table4.id AND ...
-- step 3)
UPDATE #tempReportData
SET col5 = Table5.someColumn + 123
FROM Table5
WHERE #tempReportData.id = Table5.id AND ...
-- step 4)
-- return data to the client, #tempReportData will be automatically dropped
-- once this stored proc execution is completed
SELECT * from #tempReportData
END
How would one rewrite the above logic in Postgresql? It should be noted that:
1) the real report logic may be much more complex. In other words, writing the
report's logic with one SQL statement should be assumed impossible.
2) The reports are usually written to work on result sets, as in the example
above. It's possible to rewrite the above logic with cursors, etc, though
keeping the result set approach would be more preferable.
Thanks in advance
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Temp tables, reports in Postgresql (and other RDBMS)
@ 2006-10-15 16:54 Tom Lane <tgl@sss.pgh.pa.us>
parent: ow <oneway_111@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2006-10-15 16:54 UTC (permalink / raw)
To: ow <oneway_111@yahoo.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Temp tables, reports in Postgresql (and other RDBMS)
@ 2006-10-15 18:10 ow <oneway_111@yahoo.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: ow @ 2006-10-15 18:10 UTC (permalink / raw)
To: pgsql-sql
--- 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-10-15 18:10 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-15 16:08 Temp tables, reports in Postgresql (and other RDBMS) ow <oneway_111@yahoo.com>
2006-10-15 16:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-10-15 18:10 ` ow <oneway_111@yahoo.com>
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