--NextPart_Webmail_9m3u9jl4l_18347_1208820022_0
Content-Type: text/plain
Content-Transfer-Encoding: 8bit
-------------- Original message ----------------------
From: "Kerri Reno" <kreno@[EMAIL PROTECTED]
>
> Adrian,
>
> I don't understand. Why do I need to use execute? It runs fine the
first
> time. The second time it bombs, because it's not seeing schedrec
> correctly. Which part should be in an execute query statement?
plpgsql caches query plans. In versions prior to 8.3 this meant that the
first time you ran a function the plans for the statements where cached
for use by later runs of the function in the same session. The error you
are getting about OID missing means the function is looking for the OID of
the temp table as it was cached in the first run and not finding it. To get
around this you need to EXECUTE the create temp table statement. This
causes the plan not be cached but run anew for each call of the function.
If you follow the link I included in the previous email you will see some
examples.
>
> Thanks!
> Kerri
>
> On 4/21/08, Adrian Klaver <aklaver@[EMAIL PROTECTED]
> wrote:
> >
> > Forgot to hit reply all.
> >
> > ------------- Forwarded Message: --------------
> > From: aklaver@[EMAIL PROTECTED]
(Adrian Klaver)
> > To: "Kerri Reno" <kreno@[EMAIL PROTECTED]
>
> > Subject: Re: [GENERAL] create temp in function
> > Date: Mon, 21 Apr 2008 23:01:53 +0000
> > > -------------- Original message ----------------------
> > > From: "Kerri Reno" <kreno@[EMAIL PROTECTED]
>
> > > > Hi All! I'm new to this list, but I've been using PG for a couple
of
> > years
> > > > now. I'm trying to do something in a function that I just can't
seem
> > to do.
> > > >
> > > >
> > > > If I do the following in psql or pgadmin:
> > > > create temp table schedrec (sch text, cl text, st text);
> > > > select distinct(attrelid) from pg_attribute where attrelid =
> > > > 'schedrec'::regclass;
> > > > select * from schedrec;
> > > > drop table schedrec;
> > > >
> > > > I can do it over and over and over again without problem;
> > > >
> > > > But if I create and run the following function, it bombs on the
second
> > run.
> > > > It gives me:
> > > > compassdevel_lb=# select testtemp();
> > > > NOTICE: relid: 186270497
> > > > NOTICE: count: 0
> > > > testtemp
> > > > ----------
> > > > t
> > > > (1 row)
> > > >
> > > > compassdevel_lb=# select testtemp();
> > > > NOTICE: relid: <NULL>
> > > > ERROR: relation with OID 186270497 does not exist
> > > > CONTEXT: SQL statement "SELECT count(*) from schedrec"
> > > > PL/pgSQL function "testtemp" line 9 at select into variables
> > > >
> > > > Here is my function:
> > > > create or replace function testtemp()
> > > > returns boolean as
> > > > $body$
> > > > declare
> > > > query text;
> > > > relid integer;
> > > > cnt integer;
> > > > begin
> > > > create temp table schedrec (sch text, cl text, st text);
> > > > select into relid distinct(attrelid) from pg_attribute where
> > attrelid =
> > > > 'schedrec'::regclass;
> > > > raise notice 'relid: %', relid;
> > > > select into cnt count(*) from schedrec;
> > > > raise notice 'count: %', cnt;
> > > > drop table schedrec;
> > > > if relid is null then
> > > > return false;
> > > > else
> > > > return true;
> > > > end if;
> > > > end;
> > > > $body$
> > > > language plpgsql security definer;
> > > >
> > > > Can anyone please help me with this?
> > >
> >
> > If you are running a version <8.3 you will need to use EXECUTE. See:
> >
> >
>
http://www.postgresql.org/docs/8.2/interactive/plpgsql-statements.html#PLPGSQL-S
> TATEMENTS-EXECUTING-DYN
> >
> > >
> > > > TIA,
> > > > Kerri
--
Adrian Klaver
aklaver@[EMAIL PROTECTED]
message/rfc822
From: "Kerri Reno" <kreno@[EMAIL PROTECTED]
>
To: "Adrian Klaver" <aklaver@[EMAIL PROTECTED]
>
Cc: pgsql-general@[EMAIL PROTECTED]
Re: FW: Re: [GENERAL] create temp in function
Date: Mon, 21 Apr 2008 23:12:27 +0000
Content-Type: text/html; charset=ISO-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
Adrian,<br><br>I don't understand. Why do I need to use
execute?&=
nbsp; It runs fine the first time. The second time it bombs, because
=
it's not seeing schedrec correctly. Which part should be in an
ex=
ecute query statement?<br>
<br>Thanks!<br>Kerri<br><br><div><span class=3D"gmail_quote">On 4/21/08,
<b=
class=3D"gmail_sendername">Adrian Klaver</b> <<a
href=3D"mailto:aklaver=
@[EMAIL PROTECTED]
">aklaver@[EMAIL PROTECTED]
>> wrote:</span><blockquote
class=3D=
"gmail_quote" style=3D"border-left: 1px solid rgb(204, 204, 204); margin:
0=
pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div style=3D"direction: ltr;">Forgot to hit reply all.<br>
<br>
------------- Forwarded Message: --------------<br>
From: <a onclick=3D"return top.js.OpenExtLink(window,event,this)"
href=3D"m=
ailto:aklaver@[EMAIL PROTECTED]
">aklaver@[EMAIL PROTECTED]
> (Adrian Klaver)<br>
To: "Kerri Reno" <<a onclick=3D"return
top.js.OpenExtLink(wind=
ow,event,this)"
href=3D"mailto:kreno@[EMAIL PROTECTED]
">kreno@[EMAIL PROTECTED]
>><b=
r>
Subject: Re: [GENERAL] create temp in function<br>
Date: Mon, 21 Apr 2008 23:01:53 +0000<br>
</div><div style=3D"direction: ltr;"><span class=3D"e"
id=3D"q_119733bddd92=
893e_1">> -------------- Original message ----------------------<br>
> From: "Kerri Reno" <<a onclick=3D"return
top.js.OpenExtLi=
nk(window,event,this)"
href=3D"mailto:kreno@[EMAIL PROTECTED]
">kreno@[EMAIL PROTECTED]
>><br>
> > Hi All! I'm new to this list, but I've been using
P=
G for a couple of years<br>
> > now. I'm trying to do something in a function that I
ju=
st can't seem to do.<br>
> ><br>
> ><br>
> > If I do the following in psql or pgadmin:<br>
> > create temp table schedrec (sch text, cl text, st text);<br>
> > select distinct(attrelid) from pg_attribute where attrelid
=3D<br>
> > 'schedrec'::regclass;<br>
> > select * from schedrec;<br>
> > drop table schedrec;<br>
> ><br>
> > I can do it over and over and over again without problem;<br>
> ><br>
> > But if I create and run the following function, it bombs on the
s=
econd run.<br>
> > It gives me:<br>
> > compassdevel_lb=3D# select testtemp();<br>
> > NOTICE: relid: 186270497<br>
> > NOTICE: count: 0<br>
> > testtemp<br>
> > ----------<br>
> > t<br>
> > (1 row)<br>
> ><br>
> > compassdevel_lb=3D# select testtemp();<br>
> > NOTICE: relid: <NULL><br>
> > ERROR: relation with OID 186270497 does not exist<br>
> > CONTEXT: SQL statement "SELECT count(*) from
sch=
edrec"<br>
> > PL/pgSQL function "testtemp" line 9 at select into
vari=
ables<br>
> ><br>
> > Here is my function:<br>
> > create or replace function testtemp()<br>
> > returns boolean as<br>
> > $body$<br>
> > declare<br>
> > query text;<br>
> > relid integer;<br>
> > cnt integer;<br>
> > begin<br>
> > create temp table schedrec (sch text, cl text, st
t=
ext);<br>
> > select into relid distinct(attrelid) from
pg_attrib=
ute where attrelid =3D<br>
> > 'schedrec'::regclass;<br>
> > raise notice 'relid: %', relid;<br>
> > select into cnt count(*) from schedrec;<br>
> > raise notice 'count: %', cnt;<br>
> > drop table schedrec;<br>
> > if relid is null then<br>
> > return false;<br>
> > else<br>
> > return true;<br>
> > end if;<br>
> > end;<br>
> > $body$<br>
> > language plpgsql security definer;<br>
> ><br>
> > Can anyone please help me with this?<br>
><br>
<br>
If you are running a version <8.3 you will need to use EXECUTE.
See:<br>
</span></div><div style=3D"direction: ltr;"> <a onclick=3D"return
top.=
js.OpenExtLink(window,event,this)"
href=3D"http://www.postgresql.org/docs/8=
..2/interactive/plpgsql-statements.html#PLPGSQL-S"
target=3D"_blank">http://=
www.postgresql.org/docs/8.2/interactive/plpgsql-statements.html#PLPGSQL-S</=
a> TATEMENTS-EXECUTING-DYN<br>
</div><div style=3D"direction: ltr;"><span class=3D"q"><br>
><br>
> > TIA,<br>
> > Kerri<br>
> ><br>
> ><br>
><br>
<br></span></div></blockquote></div>-- <br>Yuma Educational Computer
Consor=
tium<br>Compass Development Team<br>Kerri Reno<br><a
href=3D"mailto:kreno@[EMAIL PROTECTED]
">kreno@[EMAIL PROTECTED]
> (928)
50=
2-4240<br>.=B7:*=A8=A8*:=B7. .=B7:*=A8=A8*:=B7. .=
=B7:*=A8=A8*:=B7.
--NextPart_Webmail_9m3u9jl4l_18347_1208820022_0
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0
--
Sent via pgsql-general mailing list (pgsql-general@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--NextPart_Webmail_9m3u9jl4l_18347_1208820022_0--
|
