急问一个问题:已建好的函数不能重复执行的问题

leeshasr

我要写一个函数 对 某一个表进行分析,其中在函数中 要创建临时性的辅助表,但是函数在PSQL中只能执行一次,第二次执行就会出错!一直找不到问题所在,请帮忙分析一下!

代码:

CREATE OR REPLACE FUNCTION ps_temp_table("varchar")
RETURNS bool AS
$BODY$
DECLARE

BEGIN

perform n.nspname ,c.relname
FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname like 'pg_temp_%'
AND pg_catalog.pg_table_is_visible(c.oid)
AND Upper(relname) = Upper($1);

IF FOUND THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;

END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

create or replace function proc_rkmx_temp() returns bool as $$

declare myrow record;

begin

if ps_temp_table('temp1')=TRUE then
drop table temp1;
end if;

if ps_temp_table('temp1')=FALSE then
create temporary table temp1 as
select distinct nsrsbh,nsrmc from nsrdt;
end if;

if ps_temp_table('temp2')=TRUE then
drop table temp2;
end if;

if ps_temp_table('temp2')=FALSE then
create temporary table temp2 as
select distinct date_part('year',rkrq) as years, date_part('month',rkrq) as months from rkinf;
end if;

select * into myrow from temp1 cross join temp2 limit 1;

if found then
return TRUE;
else
return FALSE;
end if;

end;

$$ language plpgsql

执行情况:

workdb=> \i temp.sql
CREATE FUNCTION
workdb=> select proc_rkmx_temp();
proc_rkmx_temp
----------------
t
(1 row)

workdb=> select proc_rkmx_temp();
错误: OID 为 17535 的关系不存在
CONTEXT: SQL 语句 "SELECT * from temp1 cross join temp2 limit 1"
PL/pgSQL function "proc_rkmx_temp" line 24 at select into variables
workdb=>

如果退出 PSQL 又可以执行一次 select proc_rkmx_temp();
问题何在????????