pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: jack <datactrl@tpg.com.au>
To: pgsql-sql@postgresql.org
Subject: call the same pl/pgsql procedure twice in the same connection session
Date: Mon, 25 Mar 2002 20:33:29 +1000
Message-ID: <00ef01c1d3e8$81eb27f0$1400a8c0@jac> (raw)

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




view thread (12+ messages)  latest in thread

Message-ID: <00ef01c1d3e8$81eb27f0$1400a8c0@jac>
Permalink:  ../00ef01c1d3e8$81eb27f0$1400a8c0@jac/
Also on:    postgresql.org/message-id/00ef01c1d3e8$81eb27f0$1400a8c0@jac

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: datactrl@tpg.com.au
  Subject: Re: call the same pl/pgsql procedure twice in the same connection session
  In-Reply-To: <00ef01c1d3e8$81eb27f0$1400a8c0@jac>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox