Received: from candle.pha.pa.us (216-55-132-35.dsl.san-diego.abac.net [216.55.132.35]) by postgresql.org (Postfix) with ESMTP id 4D837475A41 for ; Mon, 25 Mar 2002 16:06:44 -0500 (EST) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.10.1) id g2PL6dv16423; Mon, 25 Mar 2002 16:06:39 -0500 (EST) From: Bruce Momjian Message-Id: <200203252106.g2PL6dv16423@candle.pha.pa.us> Subject: Re: call the same pl/pgsql procedure twice in the same connection In-Reply-To: <00ef01c1d3e8$81eb27f0$1400a8c0@jac> To: jack Date: Mon, 25 Mar 2002 16:06:39 -0500 (EST) Cc: pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL97 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Archive-Number: 200203/331 X-Sequence-Number: 7206 jack wrote: > I have a function with pl/pgSQL such as... > CREATE FUNCTION comp_userExp (TEXT) RETURNS INTEGER AS ' > DECLARE > stUserName TEXT; > BEGIN > stUserName := upper($1); > > CREATE TEMP TABLE comuser AS > SELECT * FROM comt_user1 > WHERE userName=stUserName; > > CREATE TEMP TABLE comUser1 AS > SELECT a.userName FROM comt_user2 a, comuser b > WHERE a.userName = b.userName > ORDER BY b.userName; > > CREATE TEMP TABLE comUser2 AS > SELECT a.userName FROM comt_user3 a, comuser b > WHERE a.userName = b.userName > ORDER BY b.userName; > > DROP Table comuser,comuser1,comuser2; > > RETURN 0; > > END;' > LANGUAGE 'PLPGSQL'; > > This function can't run twice in the same connection session. After tracing > error, the reason is because 2nd and 3rd SQL refer to table comuser. It will > cause "can't find relation number xxxxx". I think ,after first run, the > procedure just use relation number to access table, while actually table was > dropped and create again with a different relation number on the 2nd time. > If I disconnect database, and re-connect again, it won't cuase any problem. > If don't want to disconnect and connect, is there any way to fix the > problem? Yes, this is coming up a lot recently, maybe an FAQ. You need to use EXECUTE in plpgsql so the string is reparsed every time and the proper oid assigned. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026