From: ow <oneway_111@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Temp tables, reports in Postgresql (and other RDBMS)
Date: Sun, 15 Oct 2006 09:08:47 -0700 (PDT)
Message-ID: <20061015160847.83246.qmail@web53909.mail.yahoo.com> (raw)
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
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: oneway_111@yahoo.com
Subject: Re: Temp tables, reports in Postgresql (and other RDBMS)
In-Reply-To: <20061015160847.83246.qmail@web53909.mail.yahoo.com>
* 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