Received: from mail1.tpgi.com.au (mail.tpgi.com.au [203.12.160.57]) by postgresql.org (Postfix) with ESMTP id 39E30475943 for ; Mon, 25 Mar 2002 05:33:30 -0500 (EST) Received: from jac (bri5-56k-062.tpgi.com.au [203.213.6.62]) by mail1.tpgi.com.au (8.11.6/8.11.6) with SMTP id g2PAXVj11197 for ; Mon, 25 Mar 2002 21:33:31 +1100 Message-ID: <00ef01c1d3e8$81eb27f0$1400a8c0@jac> From: "jack" To: Subject: call the same pl/pgsql procedure twice in the same connection session Date: Mon, 25 Mar 2002 20:33:29 +1000 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Archive-Number: 200203/311 X-Sequence-Number: 7189 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? JACK JACK