Thread: Temp tables, reports in Postgresql (and other RDBMS)
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
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
--- 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