Обсуждение: 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