Hi,
I have a pgplsql function. It is quite short and simple.
The problem:
The following error message comes up for the SECOND time I call the it
“Relation 44451 does not exist”
In the function I create a temporary table, and at the end I drop it… There may be problem with this ? I don’t know…
So the error message does not come up for the first call.
The body of function:
declare
in_type alias for $1;
in_MemberID alias for $2;
in_SponsorID alias for $3;
vSponsorID integer;
vtSponsorID integer;
vi integer;
vMaxi integer;
vDirect integer;
vRec record;
begin
vDirect := (select count(*) as ps from comMatrixMembers cmm where cmm.state ='A' and cmm.sponsorID = in_SponsorID);
create temp sequence Tmembers_id0_id_seq;
create temp table Tmembers (
id0_id integer default nextval('Tmembers_id0_id_seq') ,
memberID integer NOT NULL
);
vi := 0;
vSponsorID := in_SponsorID;
vMaxi := 2;
while vi <= vMaxi loop
vtSponsorID := vSponsorID;
vSponsorID := (select memberID from Tmembers where id0_id = vi);
if vSponsorID is null then
vSponsorID := vtSponsorID;
end if;
insert into Tmembers (memberID)
select cmm.memberID from comMatrixMembers cmm
where cmm.sponsorID = vSponsorID
and cmm.state = 'A';
vMaxi := (select coalesce(max(id0_id),0) as vm from Tmembers);
vi := vi + 1;
end loop;
vi := (select (count(*) - vDirect) as ps from Tmembers);
-- check whether in_SponsorID is a sponsor of in_MemberID
vMaxi := (select count(*) as ps from Tmembers where memberID = in_MemberID);
if in_MemberID = in_SponsorID then
vMaxi := 1;
end if;
drop sequence Tmembers_id0_id_seq;
drop table Tmembers;
if in_type = 'D' then
return vDirect;
elsif in_type ='I' then
return vi;
elsif in_type = 'A' then
return vi + vDirect;
else
return vMaxi;
end if;
end
