Re: Need help with CASE statement in Function

On 10/3/07, Hengky Lie <hengkyliwandouw(at)gmail(dot)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