From hengkyliwandouw@gmail.com Wed Oct 3 16:44:49 2007 Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id AF1049F98DC for ; Wed, 3 Oct 2007 13:44:49 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 14592-08 for ; Wed, 3 Oct 2007 13:44:40 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.183]) by postgresql.org (Postfix) with ESMTP id 74D5F9F98AA for ; Wed, 3 Oct 2007 13:44:40 -0300 (ADT) Received: by wa-out-1112.google.com with SMTP id m38so6219549waf for ; Wed, 03 Oct 2007 09:44:38 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:from:to:subject:date:message-id:mime-version:content-type:x-mailer:thread-index:x-mimeole; bh=/6AFhgxTSv5l6cFQv+0i8J8OybXyTuLutnilaiPKY8Q=; b=ZHh/MjnqtpIZtVCUabvQ0LhqERhAul8sVlrfMC5b1zmOCeGgmfpMkCBT+3o1IjWNgYA6iS3gd4Q2ijJLU8CGoEYAfETcvtP1ED5qYI57E67GNOaCnn/A/smVbUv+iJQ0q6XvYt0sx9G8xWWIkDRTIvLS3IcMX/574XViigcSSnM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:from:to:subject:date:message-id:mime-version:content-type:x-mailer:thread-index:x-mimeole; b=JXi3ho09MBXFzHf6olSVIs0nPO1BaekDokVq2dTYjRvceH19hCxh7/LW14Q0zAUU9d5CqV40GXBh1w6FRLlX+GzGvkNMybFeQnysFXTVIu9L/QB8WsbIBRcRYyOnvM2ZYt2INQOWI8Y1ASkORVtb8gQH4+5pNuhf8IXea4erqd8= Received: by 10.114.201.1 with SMTP id y1mr7037318waf.1191429878548; Wed, 03 Oct 2007 09:44:38 -0700 (PDT) Received: from hengky ( [125.162.255.170]) by mx.google.com with ESMTPS id j39sm952332waf.2007.10.03.09.44.34 (version=SSLv3 cipher=RC4-MD5); Wed, 03 Oct 2007 09:44:38 -0700 (PDT) From: "Hengky Lie" To: Subject: Need help with CASE statement in Function Date: Thu, 4 Oct 2007 00:44:04 +0800 Message-ID: <000d01c805dc$9e8db450$1d00a8c0@hengky> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_000E_01C8061F.ACB0F450" X-Mailer: Microsoft Office Outlook 11 Thread-Index: AcgF3Jv0vODXiyFfQqeilj6rETB/iQ== X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/13 X-Sequence-Number: 29385 This is a multi-part message in MIME format. ------=_NextPart_000_000E_01C8061F.ACB0F450 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Dear friends, I am a new user to postgreSQL and really need help to solve my "stupid ?" problem. I have created function with 4 arguments like this : CREATE OR REPLACE FUNCTION "public"."fHistoryCard" (begdate date, enddate date, ProductID varchar, storeID varchar) RETURNS SETOF "public"."tbltrans" AS $body$ /* New function body */ select * from tbltrans where tbltrans."Date" between $1 and $2 and upper(tbltrans."ProductID")=upper($3) and tbltrans."StoreID"=$4 order by tbltrans."Tanggal"; $body$ LANGUAGE 'sql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; --------------------- My Question is : How to make argument 4 optional ? When IS NULL the function will show all transaction between date $1 and $2 and product ID=$3 But when $4 is not null then the fucntion will show all transaction between date $1 and $2 and product ID=$3 AND STOREID=$4. I really appreciate any suggestions. Thanks a lot. Hengky ------=_NextPart_000_000E_01C8061F.ACB0F450 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Dear friends,

I am a new user to postgreSQL and really need help to = solve my “stupid ?” problem.

I have created function with 4 arguments like = this :

CREATE OR REPLACE FUNCTION "public"."fHistoryCard" (begdate date, enddate date, = ProductID varchar, storeID varchar) RETURNS SETOF = "public"."tbltrans" AS

$body$

/* New function body = */

select * from = tbltrans

where tbltrans."Date” between $1 = and $2

and = upper(tbltrans."ProductID")=3Dupper($3)

and = tbltrans."StoreID"=3D$4

order by = tbltrans."Tanggal";

$body$

LANGUAGE 'sql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

---------------------

My Question is : How to make argument 4 optional ? = When IS NULL the function will show all transaction between date $1 and $2 and = product ID=3D$3

But when $4 is not null then the fucntion will show = all transaction between date $1 and $2 and product ID=3D$3 AND = STOREID=3D$4.

I really appreciate any = suggestions.

Thanks a lot.

Hengky

------=_NextPart_000_000E_01C8061F.ACB0F450-- From qnex42@gmail.com Wed Oct 3 17:04:33 2007 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 3C7AC9F9E33 for ; Wed, 3 Oct 2007 14:04:33 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 92690-08 for ; Wed, 3 Oct 2007 14:04:23 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from mu-out-0910.google.com (mu-out-0910.google.com [209.85.134.190]) by postgresql.org (Postfix) with ESMTP id 0C35C9F9D78 for ; Wed, 3 Oct 2007 14:04:24 -0300 (ADT) Received: by mu-out-0910.google.com with SMTP id i10so7485464mue for ; Wed, 03 Oct 2007 10:04:23 -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:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=V+y+LF/cD5hUNbly9Y1rg2FGwBNvXq9g8SWdkttjRXk=; b=IZhPoT/KhleukIEEHEFGam2X3MacoBr/2+yS5D+oFqCTNlx5/vCkWPxseUG06/PWRoaXwaSLW+GuArtAMM8whIxzfSndJ8+sVmFiB6P89/+Q7U5yTUMZ+Tm/QGaBOAnI+PjfPmNVUMF+izt1iw7pXM42cmIxx7Gx/uReF7fxmF4= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=AbNpVakiIuLI/QXHVAVEuQmWS/uJMK9XNBHqCMV+YIq5UJDEdpLtIDqCPCmREgkgVFZMZXwRX6Kz/Sw0A109aFln5acZqVUZwCMKpl/EQjzfYQaYO8i+1IVTmWui2nN1BWv/4zcTlZdtSB3Twik6VgmtzAWH32Szvl+2MniskPc= Received: by 10.82.178.11 with SMTP id a11mr12417101buf.1191431062996; Wed, 03 Oct 2007 10:04:22 -0700 (PDT) Received: by 10.82.170.9 with HTTP; Wed, 3 Oct 2007 10:04:22 -0700 (PDT) Message-ID: <758d5e7f0710031004q3c71b07bqc04158750527621e@mail.gmail.com> Date: Wed, 3 Oct 2007 19:04:22 +0200 From: "Dawid Kuroczko" To: "Hengky Lie" Subject: Re: Need help with CASE statement in Function Cc: pgsql-sql@postgresql.org In-Reply-To: <000d01c805dc$9e8db450$1d00a8c0@hengky> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <000d01c805dc$9e8db450$1d00a8c0@hengky> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/14 X-Sequence-Number: 29386 On 10/3/07, Hengky Lie wrote: > Dear friends, > I am a new user to postgreSQL and really need help to solve my "stupid ?" > problem. > > I have created function with 4 arguments like this : > > CREATE OR REPLACE FUNCTION "public"."fHistoryCard" (begdate date, enddate > date, ProductID varchar, storeID varchar) RETURNS SETOF "public"."tbltrans" > AS > > $body$ > /* New function body */ > select * from tbltrans > where tbltrans."Date" between $1 and $2 > and upper(tbltrans."ProductID")=upper($3) > and tbltrans."StoreID"=$4 > order by tbltrans."Tanggal"; > $body$ > > LANGUAGE 'sql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; > --------------------- > My Question is : How to make argument 4 optional ? When IS NULL the function > will show all transaction between date $1 and $2 and product ID=$3 > But when $4 is not null then the fucntion will show all transaction between > date $1 and $2 and product ID=$3 AND STOREID=$4. Try something like: CREATE OR REPLACE FUNCTION "public"."fHistoryCard" (begdate date, enddate date, ProductID varchar, storeID varchar) RETURNS SETOF "public"."tbltrans" AS $body$ /* New function body */ SELECT * FROM tbltrans WHERE tbltrans."Date" between $1 and $2 AND upper(tbltrans."ProductID")=upper($3) AND ($4 IS NULL OR tbltrans."StoreID"=$4) ORDER BY tbltrans."Tanggal"; $body$ LANGUAGE 'sql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; In other words, change: tbltrans."StoreID"=$4 into ($4 IS NULL OR tbltrans."StoreID"=$4) Haven't tested it, but should work fine. Regards, Dawid From rabroersma@yahoo.com Wed Oct 3 17:07:39 2007 Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 92F1A9FA0A3 for ; Wed, 3 Oct 2007 14:07:39 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 93493-05 for ; Wed, 3 Oct 2007 14:07:23 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from web31808.mail.mud.yahoo.com (web31808.mail.mud.yahoo.com [68.142.207.71]) by postgresql.org (Postfix) with SMTP id F1E599F95CB for ; Wed, 3 Oct 2007 14:07:28 -0300 (ADT) Received: (qmail 3162 invoked by uid 60001); 3 Oct 2007 17:07:26 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=1sU9zEQdihDxHsgrgDb77XppQtMzDe4hstZj7urltlAS0/lZ7Q77WLCYI0jhRTVX2qDpizwmKn3E4PUDmK+SmY+va9emG9dSToycYL3E+qS2cboW0XCtzHscErP0UrhC9gw4UflmIkTx37NJqmXuMIxtopWj5B1jmu2sdUDJeY4=; X-YMail-OSG: 1VjXSuMVM1lv2yXAmuWiuN5Wai_tTLUU52vb3bNmDPj9s1eOINtk6U4dEIeeJ1hQXlaq5UJN.w-- Received: from [12.180.237.138] by web31808.mail.mud.yahoo.com via HTTP; Wed, 03 Oct 2007 10:07:25 PDT Date: Wed, 3 Oct 2007 10:07:25 -0700 (PDT) From: Richard Broersma Jr Subject: Re: Need help with CASE statement in Function To: Hengky Lie , pgsql-sql@postgresql.org In-Reply-To: <000d01c805dc$9e8db450$1d00a8c0@hengky> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Message-ID: <282657.3138.qm@web31808.mail.mud.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/15 X-Sequence-Number: 29387 --- Hengky Lie wrote: > My Question is : How to make argument 4 optional ? When IS NULL the function > will show all transaction between date $1 and $2 and product ID=$3 Could you simply overload your function by having two functions? One with arguement 4 and one without? Regards, Richard Broersma Jr.