DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=2ErlavAw8sG7AiHWfYLns2/MzkF6/jVaY61FoOOCpxHjylOm2FSfbC5UANDAOh5bWOk/FZeLYwxhjE2ray8lBgS0k3nP+FFt4qxvkHiG0Er7anVwQFYeD5D185sAd3AV+kx9E08Lm0qwJEZI8rb+xDE8X0fbq2QDNkMgNmtcuuk=
	;
Message-ID: <20061015160847.83246.qmail@web53909.mail.yahoo.com>
Date: Sun, 15 Oct 2006 09:08:47 -0700 (PDT)
From: ow <oneway_111@yahoo.com>
Subject: Temp tables, reports in Postgresql (and other RDBMS)
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit


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