X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 785B5D1B1EC for ; Mon, 5 Jul 2004 09:25:45 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 72738-05 for ; Mon, 5 Jul 2004 12:25:44 +0000 (GMT) Received: from srv2.redbackinternet.com (ns1.redbackinternet.com [64.5.60.205]) by svr1.postgresql.org (Postfix) with ESMTP id 589C3D1B18F for ; Mon, 5 Jul 2004 09:25:36 -0300 (ADT) X-ClientAddr: 213.86.202.2 Received: from asus (host213-86-202-2.uk.regusnet.com [213.86.202.2]) (authenticated (0 bits)) by srv2.redbackinternet.com (8.11.6/8.11.6) with ESMTP id i65C2DB32744 for ; Mon, 5 Jul 2004 12:02:13 GMT From: "Ben" To: Subject: What is PG best practice for storing temporary data in functions? Date: Mon, 5 Jul 2004 13:29:52 +0100 Message-ID: <014601c4628b$c651c3c0$0500a8c0@asus> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0147_01C46294.28162BC0" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2616 Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-MailScanner-Information: Please contact the ISP for more information X-MailScanner: Found to be clean X-MailScanner-SpamCheck: not spam, SpamAssassin (score=-4.7, required 5, BAYES_00 -4.90, HTML_70_80 0.10, HTML_MESSAGE 0.10) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=HTML_70_80, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200407/45 X-Sequence-Number: 18297 This is a multi-part message in MIME format. ------=_NextPart_000_0147_01C46294.28162BC0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit There is a very common technique used in other RDBMS (e.g. Sybase) stored procedure programming which I am not sure how best to replicate in Postgres functions. A Sybase stored procedure can do select into #temp1 create table #temp2 (.) call some proc which also uses #temp1 and #temp2 etc where #temp1 and #temp2 are temporary tables magically created by the server for the duration of the procedure call only (the procedure can be safely executed in parallel since each execution sees only its own data). Under the hood, it does this by mangling the names of the temp tables with a unique identifier for the procedure context. What are the cleanest and most performant ways to do this in Postgres (7.4.2)? I am aware of temporary tables but they are globally visible to other invocations in the same session. We need the equivalent of local method variables I guess. ------=_NextPart_000_0147_01C46294.28162BC0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Message

There is = a very=20 common technique used in other RDBMS = (e.g.=20 Sybase) stored procedure=20 programming which I am not sure how best to replicate in Postgres=20 functions.

 

A Sybase = stored=20 procedure can do

  select= <some=20 complex query> into #temp1

  create= table=20 #temp2 (…)

  call s= ome proc=20 which also uses #temp1 and #temp2

etc

 

where #temp1 and= #temp2=20 are temporary tables magically created by the server for the duration of th= e=20 procedure call only (the procedure can be safely executed in parallel since= each=20 execution sees only its own data). Under the hood, it does this by mangling= the=20 names of the temp tables with a unique identifier for the procedure=20 context.

 

What are = the=20 cleanest and most performant ways to do this in Postgres (7.4.2)? I am aware of temporary tables but they are glob= ally=20 visible to other invocations in the same session. We need the equivalent of= =20 local method variables I guess.

 

 

------=_NextPart_000_0147_01C46294.28162BC0--