X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 06C0C475EC9 for ; Tue, 8 Apr 2003 06:14:33 -0400 (EDT) Received: from snickers.hotpop.com (snickers.hotpop.com [204.57.55.49]) by postgresql.org (Postfix) with ESMTP id AD509475E9D for ; Tue, 8 Apr 2003 06:14:31 -0400 (EDT) Received: from bonbon.net (kubrick.hotpop.com [204.57.55.16]) by snickers.hotpop.com (Postfix) with SMTP id 4C81C70A31 for ; Tue, 8 Apr 2003 10:14:26 +0000 (UTC) Received: from yudha (unknown [61.94.212.169]) by smtp-1.hotpop.com (Postfix) with SMTP id 6F8201A0150 for ; Tue, 8 Apr 2003 10:14:14 +0000 (UTC) Message-ID: <018101c2fdb8$6e46d1e0$ea00a8c0@yudha> From: "Yudha Setiawan" To: Subject: Language SQL, But Need Return PlPgSql Date: Tue, 8 Apr 2003 16:52:49 +0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0149_01C2FDEF.498B4430" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2919.6700 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700 X-HotPOP: ----------------------------------------------- Sent By HotPOP.com FREE Email Get your FREE POP email at www.HotPOP.com ----------------------------------------------- X-Spam-Status: No, hits=-1.5 required=5.0 tests=BAYES_30,HTML_50_60 version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/106 X-Sequence-Number: 12807 This is a multi-part message in MIME format. ------=_NextPart_000_0149_01C2FDEF.498B4430 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Dear Expert; Gimme your hand please,=20 I've been using function with "Language SQL" and combined with "type" for the simply-one. Like this; ------------------------------------- create type simply_1 as( fc_name char(20), fn_codedept integer, fc_descdept char(30) ); Create function F_Simple(integer) returns setof d_4developer.ty_rep_coba as= =20 ' select a.fc_name, a.fn_codedept, b.fc_descdept=20 from Emplo_Corp a, Depart b=20 where a.fn_codedept =3D b.fn_codedept and=20 a.fn_codedept =3D $1;=20 ' language SQL; -------------------------------------- That's running pretty good. But now i have more complicated condition.=20 There are hundreds combination of reports must be handled by me and most of all have the same result. If i still using style of "Language=20 SQL", that's means I will have to make function as much as my report.=20 I Tried Using style "Language PlPgSql" but I got a Fully Confused=20 about what a kind of RETURN i will use ?? ------------------------------------------------- Create or replace function MultiRpt(integer,char(1))=20 setof d_4developer.ty_rep_coba as=20 'begin -- View Corporate's Employee If $2 =3D ''C'' Then select a.fc_name, a.fn_codedept, b.fc_descdept=20 from Emplo_Corp a, depart b=20 where a.fn_codedept =3D b.fn_codedept and=20 a.fn_codedept =3D $1;=20 End If;=20=20 -- View Branch'A' Employee If $2 =3D ''A'' Then select a.fc_name, a.fn_codedept, b.fc_descdept=20 from Emplo_BRANCH_A a, depart b=20 where a.fn_codedept =3D b.fn_codedept and=20 a.fn_codedept =3D $1;=20 End If;=20=20 -- View Branch'B' Employee If $2 =3D ''B'' Then select a.fc_name, a.fn_codedept, b.fc_descdept=20 from Emplo_BRANCH_B a, depart b=20 where a.fn_codedept =3D b.fn_codedept and=20 a.fn_codedept =3D $1; End If;' Return (-- ?? --) end;' language PlPgSql; ------------------------------------------------- Some advice will be helpfull. Thank's=20 GOD Bless You All and Bravo Postgres !! Yudha Setiawan. VoIP=20 No. : 628899911223344 ------=_NextPart_000_0149_01C2FDEF.498B4430 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Dear Expert;
Gimme your hand please,
I've been using function with "Language SQL" and combined with
"type" for the simply-= one. Like=20 this;
-------------------------------------
create type simply_1 as(
     fc_n= ame =20      char(20),
    =20 fn_codedept   integer,
    =20 fc_descdept   char(30)  );
 
Create function F_Simple(integer) = returns=20 setof d_4developer.ty_rep_coba as
' b.fc_descdept
         from Emplo_Corp a= ,=20 Depart b
         where a.fn_c= odedept=20 =3D b.fn_codedept and
           =    =20 a.fn_codedept =3D $1;
' language SQL;
--------------------------------------
 
That's running pretty good. But no= w i have=20 more complicated condition.
There are hundreds combination of = reports=20 must be handled by me and
most of all have the same result. = If i=20 still using style of "Language
SQL", that's means I will have to = make=20 function as much as my report.
I Tried Using style "Language PlPg= Sql" but=20 I got a Fully Confused
about what a kind of RETURN i= will use=20 ??
-------------------------------------------------
Create or replace function=20 MultiRpt(integer,char(1))
           =       =20 setof d_4developer.ty_rep_coba as
'begin
 -- View Corporate's=20 Employee
 If $2 =3D ''C'' Then<= /DIV>
    select a.fc_name, a.fn_codedept, = b.fc_descdept
           from= =20 Emplo_Corp a, depart b
           = where=20 a.fn_codedept =3D b.fn_codedept and
           =      =20 a.fn_codedept =3D $1;
 End If;  
 
 -- View Branch'A'=20 Employee
 If $2 =3D ''A'' Then
    select a.fc_name, a.fn_codedept, = b.fc_descdept
           from= =20 Emplo_BRANCH_A a, depart b
           = where=20 a.fn_codedept =3D b.fn_codedept and
           =      =20 a.fn_codedept =3D $1;
 End If;=20  
 
 -- View Branch'B'=20 Employee
 If $2 =3D ''B'' Then
    select a.fc_name, a.fn_codedept, = b.fc_descdept
           from= =20 Emplo_BRANCH_B a, depart b
           = where=20 a.fn_codedept =3D b.fn_codedept and
           =      =20 a.fn_codedept =3D $1;
 End If;'
 Return (-- ?? --)
end;' language PlPgSql;
-------------------------------------------------
 
Some advice will be helpfull. Than= k's=20
GOD Bless You All and Bravo Postgr= es=20 !!
 
 
 
Yudha Setiawan.
VoIP=20
    No.        :=20 628899911223344
------=_NextPart_000_0149_01C2FDEF.498B4430--