PGplSql: Relation 44451 does not exist

[Kuti Attila]

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 comMatrixMember s 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('Tmembe rs_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
comMatrixMember s cmm

where cmm.sponsorID = vSponsorID

and cmm.state = 'A';

vMaxi := (select coalesce(max(id 0_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

[Richard Huxton]

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Pavel Stehule]

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 comMatrixMember s 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('Tmembe rs_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
comMatrixMember s cmm

where cmm.sponsorID = vSponsorID

and cmm.state = 'A';

vMaxi := (select coalesce(max(id 0_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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match