From: | ow <oneway_111(at)yahoo(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Temp tables, reports in Postgresql (and other RDBMS) |
Date: | 2006-10-15 16:08:47 |
Message-ID: | 20061015160847.83246.qmail@web53909.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-10-15 16:54:08 | Re: Temp tables, reports in Postgresql (and other RDBMS) |
Previous Message | Daniel CAUNE | 2006-10-15 15:23:01 | Re: migrating numeric to serial from MSSQL to postgres |