Fwd: create temp in function

------=_Part_13672_25819194.1208813054530
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

BTW, this happens the same in PostgreSQL 8.0 and 8.2.

Thanks!
Kerri

---------- Forwarded message ----------
From: Kerri Reno <kreno@[EMAIL PROTECTED]
>
Date: Apr 21, 2008 3:22 PM
Subject: create temp in function
To: pgsql-general@[EMAIL PROTECTED]
 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 =3D
'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=3D# select testtemp();
NOTICE:  relid: 186270497
NOTICE:  count: 0
 testtemp
----------
 t
(1 row)

compassdevel_lb=3D# 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
=
=3D
'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?
TIA,
Kerri

--=20
Yuma Educational Computer Consortium
Compass Development Team
Kerri Reno
kreno@[EMAIL PROTECTED]
      (928) 502-4240
..=B7:*=A8=A8*:=B7.   .=B7:*=A8=A8*:=B7.   .=B7:*=A8=A8*:=B7.

--=20
Yuma Educational Computer Consortium
Compass Development Team
Kerri Reno
kreno@[EMAIL PROTECTED]
      (928) 502-4240
..=B7:*=A8=A8*:=B7.   .=B7:*=A8=A8*:=B7.   .=B7:*=A8=A8*:=B7.

------=_Part_13672_25819194.1208813054530
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

BTW, this happens the same in PostgreSQL 8.0 and
8.2.<br><br>Thanks!<br>Ker=
ri<br><br>---------- Forwarded message ----------<br><span
class=3D"gmail_q=
uote">From: <b class=3D"gmail_sendername">Kerri Reno</b> &lt;<a
href=3D"mai=
lto:kreno@[EMAIL PROTECTED]
">kreno@[EMAIL PROTECTED]
>&gt;<br>
Date: Apr 21, 2008 3:22 PM<br>Subject: create temp in function<br>To: <a
hr=
ef=3D"mailto:pgsql-general@[EMAIL PROTECTED]
">pgsql-general@[EMAIL PROTECTED]
>=
<br><br></span>Hi All!&nbsp; I&#39;m new to this list, but I&#39;ve been
us=
ing PG for a couple of years now.&nbsp; I&#39;m trying to do something in
a=
 function that I just can&#39;t seem to do. <br>
<br>If I do the following in psql or pgadmin:<br clear=3D"all">
<div style=3D"margin-left: 40px;">create temp table schedrec (sch text, cl
=
text, st text);<br>select distinct(attrelid) from pg_attribute where
attrel=
id =3D &#39;schedrec&#39;::regclass;<br>select * from schedrec;<br>drop
tab=
le schedrec;<br>

</div><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 second
run.=
&nbsp; It gives me:<br><div style=3D"margin-left:
40px;">compassdevel_lb=3D=
# select testtemp();<br>

NOTICE:&nbsp; relid: 186270497<br>NOTICE:&nbsp; count:
0<br>&nbsp;testtemp<=
br>----------<br>&nbsp;t<br>(1 row)<br><br>compassdevel_lb=3D# select
testt=
emp();<br>NOTICE:&nbsp; relid: &lt;NULL&gt;<br>ERROR:&nbsp; relation with
O=
ID 186270497 does not exist<br>

CONTEXT:&nbsp; SQL statement &quot;SELECT&nbsp; count(*) from
schedrec&quot=
;<br>PL/pgSQL function &quot;testtemp&quot; line 9 at select into
variables=
<br></div><br>Here is my function:<br>create or replace function
testtemp()=
<br>&nbsp;&nbsp;&nbsp; returns boolean as<br>

$body$<br>declare<br>&nbsp;&nbsp;&nbsp; query text;<br>&nbsp;&nbsp;&nbsp;
r=
elid integer;<br>&nbsp;&nbsp;&nbsp; cnt
integer;<br>begin<br>&nbsp;&nbsp;&n=
bsp; create temp table schedrec (sch text, cl text, st
text);<br>&nbsp;&nbs=
p;&nbsp; select into relid distinct(attrelid) from pg_attribute where
attre=
lid =3D &#39;schedrec&#39;::regclass;<br>

&nbsp;&nbsp;&nbsp; raise notice &#39;relid: %&#39;,
relid;<br>&nbsp;&nbsp;&=
nbsp; select into cnt count(*) from schedrec;<br>&nbsp;&nbsp;&nbsp; raise
n=
otice &#39;count: %&#39;, cnt;<br>&nbsp;&nbsp;&nbsp; drop table
schedrec;<b=
r>&nbsp;&nbsp;&nbsp; if relid is null then<br>&nbsp;&nbsp;&nbsp;
&nbsp;&nbs=
p;&nbsp; return false;<br>

&nbsp;&nbsp;&nbsp; else<br>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; return
tru=
e;<br>&nbsp;&nbsp;&nbsp; end if;<br>end;<br>$body$<br>language plpgsql
secu=
rity definer;<br><br>Can anyone please help me with
this?<br>TIA,<br>Kerri<=
br><span class=3D"sg"><br>-- <br>Yuma Educational Computer Consortium<br>
Compass Development Team<br>
Kerri Reno<br><a href=3D"mailto:kreno@[EMAIL PROTECTED]
" target=3D"_blank"
onclick=
=3D"return
top.js.OpenExtLink(window,event,this)">kreno@[EMAIL PROTECTED]
>&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(928)
502-4240<br>.=B7:*=A8=A8*:=B7.&nbsp;&n=
bsp; .=B7:*=A8=A8*:=B7.&nbsp;&nbsp; .=B7:*=A8=A8*:=B7.
</span><br clear=3D"all"><br>-- <br>Yuma Educational Computer
Consortium<br=
>Compass Development Team<br>Kerri Reno<br><a
href=3D"mailto:kreno@[EMAIL PROTECTED]
">kreno@[EMAIL PROTECTED]
>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(928)
502-4240<=
br>.=B7:*=A8=A8*:=B7.&nbsp;&nbsp; .=B7:*=A8=A8*:=B7.&nbsp;&nbsp;
.=B7:*=A8=
=A8*:=B7.

------=_Part_13672_25819194.1208813054530--