From: | Jan Wieck <janwieck(at)yahoo(dot)com> |
---|---|
To: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
Cc: | jack <datactrl(at)tpg(dot)com(dot)au>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: call the same pl/pgsql procedure twice in the same connection |
Date: | 2002-03-25 21:36:14 |
Message-ID: | 200203252136.g2PLaEg25650@saturn.janwieck.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(at)Yahoo(dot)com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
From | Date | Subject | |
---|---|---|---|
Next Message | chester c young | 2002-03-25 21:41:12 | Re: quickest query to check a table for existance of a value in a field |
Previous Message | Bruce Momjian | 2002-03-25 21:23:57 | Re: About EXECUTE in pl/pgsql |