Обсуждение: PGplSql: Relation 44451 does not exist

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