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