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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
