Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id 21D8C2E003E for ; Wed, 2 Apr 2008 23:12:47 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 75600-09 for ; Wed, 2 Apr 2008 23:12:34 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from yw-out-2324.google.com (yw-out-2324.google.com [74.125.46.29]) by developer.postgresql.org (Postfix) with ESMTP id BC08E2E0033 for ; Wed, 2 Apr 2008 23:12:42 -0300 (ADT) Received: by yw-out-2324.google.com with SMTP id 2so354299ywt.73 for ; Wed, 02 Apr 2008 19:12:41 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; bh=ttbGB7Fsi7mXqzxoEDTTeGPXHEWN/ldwS1Uflni/Coc=; b=S6g9LQZOeZhrhH7pUwjMZg9pzjOzCuMKEEpvix4gU47OsUdqCyYN78Sa3ZgdxlhoUwzOAvryIk6vKYJbxRyp3Thcu49u/LohOQhi/R50YOC7rBRNTOxUMtuu6HIYcHcp5yIjmaH7J24qIqrB5jE8UUIfLlSggfJQLbn+o2p5/Sc= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=tqKB76WSQ5u1RMs8OlPlB0G5ZIVQ+TEdftBTS52T3GycQGM8kP65jPW16w0Vax5sXRpmC/I7E1pj0jHuRzdfoo5RFF24X3M5FqiiTSQwe+Ib/CAGOR2a/6dmlcTSiTzii8tsQb4SJ6Ue+YBYkZIaqcrv2oCr+Ltj5bV3dIwXrZY= Received: by 10.150.225.4 with SMTP id x4mr110497ybg.220.1207188761512; Wed, 02 Apr 2008 19:12:41 -0700 (PDT) Received: by 10.150.218.5 with HTTP; Wed, 2 Apr 2008 19:12:41 -0700 (PDT) Message-ID: <1703cd180804021912p690c3ff5lf2b712e0a56ad607@mail.gmail.com> Date: Thu, 3 Apr 2008 09:12:41 +0700 From: "Otniel Michael" To: pgsql-sql@postgresql.org Subject: Re: Asking GO on SQL SERVER In-Reply-To: <47F438FD.4050507@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_4143_21146760.1207188761494" References: <1703cd180804021844r5b21506em3a6c90f2b5abe0ca@mail.gmail.com> <47F438FD.4050507@gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200804/14 X-Sequence-Number: 30553 ------=_Part_4143_21146760.1207188761494 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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'; On Thu, Apr 3, 2008 at 8:55 AM, paul rivers wrote: > Otniel Michael wrote: > > > Hi All.. > > > > Anybody knows what is "GO" (SQL Server) on Postgres? > > > > Thanks. > > > > -- > > ------------------------------------------------------------------- > > "He who is quick to become angry will commit folly, and a crafty man is > > hated" > > > > semi-colon. > > > -- ------------------------------------------------------------------- "He who is quick to become angry will commit folly, and a crafty man is hated" ------=_Part_4143_21146760.1207188761494 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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';

On Thu, Apr 3, 2008 at 8:55 AM, paul rivers <rivers.paul@gmail.com> wrote:
Otniel Michael wrote:
Hi All..

Anybody knows what is "GO" (SQL Server) on Postgres?

Thanks.

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

semi-colon.





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