Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 8AF611337B47 for ; Mon, 31 Jan 2011 00:23:29 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 04300-08 for ; Mon, 31 Jan 2011 04:23:22 +0000 (UTC) X-Greylist: delayed 00:06:40.433278 by SQLgrey-1.7.6 Received: from mail19k.g19.rapidsite.net (mail19k.g19.rapidsite.net [204.202.242.122]) by mail.postgresql.org (Postfix) with SMTP id 260781337999 for ; Mon, 31 Jan 2011 00:23:21 -0400 (AST) Received: from mx78.stngva01.us.mxservers.net (204.202.242.149) by mail19k.g19.rapidsite.net (RS ver 1.0.95vs) with SMTP id 0-0789407400 for ; Sun, 30 Jan 2011 23:16:40 -0500 (EST) Received: from unknown [161.58.75.15] (EHLO mmm1913.dulles19-verio.com) by va1-mx78.stngva01.us.mxservers.net (mxl_mta-3.1.0-05) with ESMTP id 8a7364d4.2494561184.133062.00-002.va1-mx78.stngva01.us.mxservers.net (envelope-from ); Sun, 30 Jan 2011 23:16:40 -0500 (EST) Received: (qmail 41117 invoked from network); 31 Jan 2011 04:16:40 -0000 Received: from unknown (HELO ?192.168.1.184?) (24.5.41.9) by with SMTP; 31 Jan 2011 04:16:40 -0000 From: Leon Starr Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable Subject: creating and accessing temp table data inside a non-committed transaction Date: Sun, 30 Jan 2011 20:16:39 -0800 Message-Id: To: pgsql-admin@postgresql.org Mime-Version: 1.0 (Apple Message framework v1082) X-Mailer: Apple Mail (2.1082) X-Spam: [F=0.2000000000; S=0.200(2010122901); MH=0.500(2011013014)] X-MAIL-FROM: X-SOURCE-IP: [161.58.75.15] X-SF-Loop: 1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201101/191 X-Sequence-Number: 36631 Plpgsql experts: I've got a situation and am wondering if temp tables are the accepted = solution. (setting the temp parameter on create table) I've never worked with them and a colleague = just brought them to my attention as a way to solve a problem I've encountered. He's not sure = about this, so I am hoping you guys can point me in the right direction. The problem is that I've got function that takes some parameters, for = example: set constraints all deferred; select my_func( x, y, z ); commit; The problem is that the values x, y and z are inserted into a permanent = table Q early in the processing of my_func. Then, several calls down in deep_func(), still inside my_func, = I need to access the value of x. I tried to do a select on Q to get the value, but I came up NULL! = Guessing that is because the transaction hasn't committed yet since I am still inside my_func. Right? So how do I get the value of parameter x? Possible solutions: 1) just pass x all the way down the calling path so that = deep_func( x ) can just access it as a parameter value. Pain in the butt because none of the = intermediate calls need x. 2) insert the value of a into a temp table and the do a select = on that within deep_func(). But will I stull come up NULL, or do temp tables work = differently? 3) some other technique if I am thinking about this totally the = wrong way! As always, help clearing my confusion muchly appreciated! (obviously I = can just do a bunch of experiments on my own, but expert insight is always nice to have) - Leon