Thread: create temp in function

create temp in function

From

"Kerri Reno"

Date:

21 April 2008, 22:08:48

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?
TIA,
Kerri

--
Yuma Educational Computer Consortium
Compass Development Team
Kerri Reno
kreno@yumaed.org      (928) 502-4240
.·:*¨¨*:·.   .·:*¨¨*:·.   .·:*¨¨*:·.

Re: create temp in function

From

"A. Kretschmer"

Date:

22 April 2008, 05:22:00

am  Mon, dem 21.04.2008, um 15:22:52 -0600 mailte Kerri Reno folgendes:
> 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);


Use EXECUTE for DDL-Statements within function, because the planner
cached the OID's.

Other solution: use 8.3.

More details about that:
http://merlinmoncure.blogspot.com/2007/09/as-previously-stated-postgresql-8.html

Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net