FW: Re: create temp in function

--NextPart_Webmail_9m3u9jl4l_8588_1208819052_0
Content-Type: text/plain
Content-Transfer-Encoding: 8bit

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:    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
Content-Type: message/rfc822

From:    "Kerri Reno" <kreno@[EMAIL PROTECTED]
>
To:    pgsql-general@[EMAIL PROTECTED]
    [GENERAL] create temp in function
Date:    Mon, 21 Apr 2008 22:10:04 +0000
Content-Type: text/html; charset=ISO-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Hi All!&nbsp; I&#39;m new to this list, but I&#39;ve been using PG for a
co=
uple 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
pgadm=
in:<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><br>-- <br>Yuma Educational Computer Consortium<br>Compass Development
T=
eam<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;&n=
bsp; .=B7:*=A8=A8*:=B7.&nbsp;&nbsp; .=B7:*=A8=A8*:=B7.

--NextPart_Webmail_9m3u9jl4l_8588_1208819052_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_8588_1208819052_0--