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
>
>
