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?
Thanks!
Kerri
On 4/21/08, Adrian Klaver <aklaver@xxxxxxxxxxx> wrote:
-- Forgot to hit reply all.
------------- Forwarded Message: --------------
From: aklaver@xxxxxxxxxxx (Adrian Klaver)
To: "Kerri Reno" <kreno@xxxxxxxxxx>
Subject: Re: [GENERAL] create temp in function
Date: Mon, 21 Apr 2008 23:01:53 +0000
> -------------- Original message ----------------------
> From: "Kerri Reno" <kreno@xxxxxxxxxx>
> > 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
> >
> >
>
Yuma Educational Computer Consortium
Compass Development Team
Kerri Reno
kreno@xxxxxxxxxx (928) 502-4240
.·:*¨¨*:·. .·:*¨¨*:·. .·:*¨¨*:·.
