Search Postgresql Archives

Fwd: create temp in function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

Thanks!
Kerri

---------- Forwarded message ----------
From: Kerri Reno <kreno@xxxxxxxxxx>
Date: Apr 21, 2008 3:22 PM
Subject: create temp in function
To: pgsql-general@xxxxxxxxxxxxxx

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@xxxxxxxxxx      (928) 502-4240
.·:*¨¨*:·.   .·:*¨¨*:·.   .·:*¨¨*:·.


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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux