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
(thathas 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
Table1WHERE ....
-- 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.idAND ...
-- step 4) -- return data to the client, #tempReportData will be automatically dropped -- once this stored proc
executionis 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