Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 6E8A89FB27A for ; Sun, 11 Feb 2007 13:27:39 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 32819-01-7 for ; Sun, 11 Feb 2007 13:27:33 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.186]) by postgresql.org (Postfix) with ESMTP id B41529FB4D3 for ; Sun, 11 Feb 2007 13:27:32 -0400 (AST) Received: by nf-out-0910.google.com with SMTP id x4so1873526nfb for ; Sun, 11 Feb 2007 09:27:31 -0800 (PST) DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=Yaci9U6yLGEqNGH7NrGqLfG0jv0CWFWJWC19R+6p1i1P5hbJKou849qcCU0uLt+zKUI2TdtaIvt3xVr+KQZLqLerQYRVE6tnnkAxd+w4i8cgL1yHxhIEaqh4gizxhPyaj5spQ/KNa7eFFCXve7kkUutPCWIpKmFBPFPLp0+NXLA= Received: by 10.82.153.5 with SMTP id a5mr9106707bue.1171214851692; Sun, 11 Feb 2007 09:27:31 -0800 (PST) Received: by 10.82.183.4 with HTTP; Sun, 11 Feb 2007 09:27:31 -0800 (PST) Message-ID: <1f30b80c0702110927g74385f97sc8a1e4d26a29076e@mail.gmail.com> Date: Sun, 11 Feb 2007 22:27:31 +0500 From: imad To: "Robert Wimmer" Subject: Re: temporary table / recursion Cc: pgsql-interfaces@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <1f30b80c0702110639v3ff4b9b1pcb7a4d849b4f2928@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.119 tagged_above=0 required=5 tests=AWL, SPF_PASS X-Spam-Level: X-Archive-Number: 200702/10 X-Sequence-Number: 6430 On 2/11/07, Robert Wimmer wrote: > > > > >From: imad > >To: "Robert Wimmer" > >Subject: Re: [INTERFACES] temporary table / recursion > >Date: Sun, 11 Feb 2007 19:39:25 +0500 > > > >The problem is the plan which is stored against the INSERT statement > >in PLpgSQL function. The next time it is called, it uses the same plan > >and fails to locate the table based on the OID because CREATE temp > >table has been called again and a new table exists now with a > >different OID. > > i changed the code as you suggested > > *** snippet *** > > CREATE FUNCTION recurs.scan(p_start INTEGER) RETURNS SETOF recurs.tree AS $$ > DECLARE tmp RECORD; > BEGIN > > CREATE TEMP TABLE recurs_temp (id INT, parent_id INT, label TEXT); > > INSERT INTO recurs_temp SELECT * FROM recurs.tree WHERE id = p_start; -- > first node > PERFORM recurs.walk(p_start); > FOR tmp IN SELECT * FROM recurs_temp LOOP RETURN NEXT tmp; END LOOP; > > DROP TABLE recurs_temp; > > RETURN; > > END; $$ > LANGUAGE plpgsql; > > > > >Another way might be to now attach your temp table with the > >transaction i.e. omit the clause ON COMMIT DROP. And drop the table at > >the end of transaction manually. > > > >If this doesn't help too, see the execute command in PLpgSQL. This > >will not save the plan against any command and this is what you need. > > > > then i used the function with a prepared statement and EXECUTE and i had the > same problems as before ... > > *** output *** > > recurs=# PREPARE recurs_func(INT) AS SELECT * FROM recurs.scan($1); > PREPARE > recurs=# EXECUTE recurs_func(1); Not like that, use the execute command inside your function. Here is the description and example. http://www.postgresql.org/docs/8.0/static/plpgsql-statements.html Recursive queries are also planned for 8.3. --Imad www.EnterpriseDB.com