pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jan Wieck <janwieck@yahoo.com>
To: Bruce Momjian <pgman@candle.pha.pa.us>
Cc: jack <datactrl@tpg.com.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: call the same pl/pgsql procedure twice in the same connection
Date: Mon, 25 Mar 2002 16:36:14 -0500 (EST)
Message-ID: <200203252136.g2PLaEg25650@saturn.janwieck.net> (raw)
In-Reply-To: <200203252106.g2PL6dv16423@candle.pha.pa.us>

Bruce Momjian wrote:
> 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.

    This  is  somehow connected to the temporary view discussion,
    as it  needs  the  same  detection  if  a  query  depends  on
    temporary  objects.  As soon as we have a detection mechanism
    for it, I can modify PL/pgSQL not to save prepared plans  for
    these statements.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com




view thread (12+ messages)  latest in thread

Message-ID: <200203252136.g2PLaEg25650@saturn.janwieck.net>
Permalink:  ../200203252136.g2PLaEg25650@saturn.janwieck.net/
Also on:    postgresql.org/message-id/200203252136.g2PLaEg25650@saturn.janwieck.net

 · 

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: janwieck@yahoo.com, pgman@candle.pha.pa.us, datactrl@tpg.com.au
  Subject: Re: call the same pl/pgsql procedure twice in the same connection
  In-Reply-To: <200203252136.g2PLaEg25650@saturn.janwieck.net>

* 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