Thread: PGplSql: Relation 44451 does not exist
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
Kuti Attila wrote: > 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. Plpgsql is compiled, so it refers to tables via their OID. The second time around Tmembers has a different OID and so you get your error message. The solution is to use the EXECUTE construct when accessing the table, or to use an interpreted language (e.g. pltcl). HTH -- Richard Huxton Archonet Ltd
Hello, read please http://www.postgresql.org/docs/faqs/FAQ.html#4.26 regards Pavel Stehule On Tue, 22 Jun 2004, Kuti Attila wrote: > 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 > >