Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 3C7AC9F9E33
	for <pgsql-sql-postgresql.org@postgresql.org>;
	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 <pgsql-sql-postgresql.org@postgresql.org>;
	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 <pgsql-sql@postgresql.org>; Wed,  3 Oct 2007 14:04:24 -0300 (ADT)
Received: by mu-out-0910.google.com with SMTP id i10so7485464mue
	for <pgsql-sql@postgresql.org>; 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" <qnex42@gmail.com>
To: "Hengky Lie" <hengkyliwandouw@gmail.com>
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 <hengkyliwandouw@gmail.com> 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