Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 2D0249FB201 for ; Sun, 11 Feb 2007 17:06:07 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 26611-02 for ; Sun, 11 Feb 2007 17:05:59 -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.188]) by postgresql.org (Postfix) with ESMTP id 91DA79FB1E7 for ; Sun, 11 Feb 2007 17:05:59 -0400 (AST) Received: by nf-out-0910.google.com with SMTP id x4so1919405nfb for ; Sun, 11 Feb 2007 13:05:58 -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=mbyXY1/clGeifSyxx6A2E1N1oGNm9hATIuSp6HFzU5tNUZH+BoyrzWiXDlvnxIFex7/tostItbz6DEzHthgGC+1HhdpJnjD11KhqsL4abz3qAkQ1F5tae0kagw6kIQrssmYYBmeXu+/vchl38u8PT+nPTIPt6Ad0QwhFS9hEhxA= Received: by 10.82.114.3 with SMTP id m3mr6821785buc.1171227957752; Sun, 11 Feb 2007 13:05:57 -0800 (PST) Received: by 10.82.183.4 with HTTP; Sun, 11 Feb 2007 13:05:57 -0800 (PST) Message-ID: <1f30b80c0702111305m7c04ffe4hbb58d780561eb606@mail.gmail.com> Date: Mon, 12 Feb 2007 02:05:57 +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: <1f30b80c0702110927g74385f97sc8a1e4d26a29076e@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, UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200702/13 X-Sequence-Number: 6433 Your INSERT statement is still missing EXECUTE command :-) --Imad www.EnterpriseDB.com On 2/12/07, Robert Wimmer wrote: > > >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 > > > > that was what I tried before the PREPARE EXECUTE example and it did not > work. > so i will try it again > > *** snippet *** > > CREATE FUNCTION recurs.scan(p_start INTEGER) RETURNS SETOF recurs.tree AS $$ > DECLARE tmp RECORD; > BEGIN > > EXECUTE '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 > EXECUTE recurs.walk(p_start); -- create tree > FOR tmp IN SELECT * FROM recurs_temp LOOP RETURN NEXT tmp; END LOOP; > > EXECUTE 'DROP TABLE recurs_temp'; > > RETURN; > > END; $$ > LANGUAGE plpgsql; > > **** > > and the output ... > > **** > > recurs=# SELECT * FROM recurs.scan(1); > id | parent_id | label > ----+-----------+----------- > 1 | | 1 > 4 | 2 | 1.1.1 > 5 | 2 | 1.1.2 > 6 | 2 | 1.1.3 > 7 | 2 | 1.1.4 > 8 | 2 | 1.1.5 > 11 | 10 | 1.1.6.2 > 13 | 12 | 1.1.6.3.1 > 12 | 10 | 1.1.6.3 > 10 | 9 | 1.1.6.1 > 9 | 2 | 1.1.6 > 2 | 1 | 1.1 > 14 | 3 | 1.2.1 > 15 | 3 | 1.2.2 > 16 | 3 | 1.2.3 > 3 | 1 | 1.2 > (16 rows) > > recurs=# \dt > No relations found. > recurs=# SELECT * FROM recurs.scan(1); > ERROR: relation with OID 2084590 does not exist > KONTEXT: SQL statement "INSERT INTO recurs_temp SELECT * FROM recurs.tree > WHERE id = $1 " > PL/pgSQL function "scan" line 6 at SQL statement > recurs=# \dt > No relations found. > > **** > > > so i dont know what went wrong now ... > > i am using Postgres 8.0.3 on Windows XP > > nevertheless thanx for your help > > > > >--Imad > >www.EnterpriseDB.com > > > >---------------------------(end of broadcast)--------------------------- > >TIP 6: explain analyze is your friend > > _________________________________________________________________ > Die MSN Homepage liefert Ihnen alle Infos zu Ihren Lieblingsthemen. > http://at.msn.com/ > >