Re: Asking GO on SQL SERVER - Mailing list pgsql-sql

Hem... I think postgresql do not like that.
For this example in function spEAR470.
When "open Phasil for" not yet finishing, postgresql do "Delete from ear470_02 where sessid = Psess;". And this function becoming ERROR.
But this case happen when this function call from 3 users in same time.
Any solution please?
Thanks before.

Create or Replace Function spEAR470 (refcursor,varchar,char,char,char)
returns refcursor
as '
declare
    Phasil    alias for $1;    Psess    alias for $2;
    PTglFrom    alias for $3; PTglTo alias for $4;
    Psbmscd    alias for $5;

    jumlah integer;

    waktu text;

begin

Select ''  Begin - spEAR470 = '' || timeofday() into waktu;        
Raise Notice ''%'',waktu;

    Delete from ear470_01 where sessid = Psess;
    Delete from ear470_02 where sessid = Psess;

    /* Rekap data transaksi proses produksi untuk bulan dan mesin yang bersangkutan */
    insert into ear470_01(sessid,sbmscd,sbmsnm,wipcod,bjcod,
        wasgrpnm,wastcd,wastds,
        qtywaste,unit,qtywstbesar,qtywstkecil,qtykonv,
        running,runbesar,runkecil,runkonv)
    select PSess,B.sbmscd,D.SBMSNM,B.wipcod,'''',
        coalesce(C.wasgrpnm,''''),coalesce(A.wastcd,''''),coalesce(C.wastds,''''),
        sum(coalesce(A.qtywst,0)),E.brunin,0,0,sum(A.qtywst),
        0,0,0,0
    from B
    left outer join A on B.jentrn=A.jentrn and B.thbltr=A.thbltr and B.nortrn=A.nortrn
        and A.brgcod in (''WST')
    left outer join C on C.wastcd=A.wastcd
    inner Join D On D.SBMSCD = B.SBMSCD
    inner join E On B.wipcod = E.brgcod
    where B.TGLTRN between PTglFrom and PTglTo and Trim(B.sbmscd) like (PSbmscd)
        and B.gdskcd = ''S'' and B.sbskcd <> ''P''
    group by B.sbmscd,B.wipcod,A.wastcd,C.wastds,E.brunin,D.SBMSNM,C.wasgrpnm;


Select ''  spEAR470 - 9 = '' || timeofday() into waktu;        
Raise Notice ''%'',waktu;

    insert into ear470_02(sessid,sbmscd,sbmsnm,wasgrpnm,wastcd,wastds,qtywaste,unit,qtykonv,running,runkonv,activetime)
    select sessid,sbmscd,sbmsnm,wasgrpnm,wastcd,wastds,sum(qtywaste),unit,sum(qtykonv),0,0,0
    from ear470_01
    where sessid = Psess
    group by sessid,sbmscd,sbmsnm,wasgrpnm,wastcd,wastds,unit;

Select ''  spEAR470 - 10 = '' || timeofday() into waktu;        
Raise Notice ''%'',waktu;

   /* Put the result into hasil variable  */
   open Phasil for
   select SBMSCD,SBMSNM,WASGRPNM,WASTCD,WASTDS,Qtywaste,Unit,QtyKonv,Running,ActiveTime,RunKonv
   from ear470_02
   Where SESSID = PSess
   Order By SBMSNM,WASGRPNM,WASTDS;

Select ''  spEAR470 - 15 = '' || timeofday() into waktu;        
Raise Notice ''%'',waktu;

   /* Delete unused data */
   Delete from ear470_01 where sessid = Psess;
   Delete from ear470_02 where sessid = Psess;

Select ''  End - spEAR470 = '' || timeofday() into waktu;        
Raise Notice ''%'',waktu;

   return Phasil;
end;
' language 'plpgsql';




--
-------------------------------------------------------------------
"He who is quick to become angry will commit folly, and a crafty man is hated"