Need help with CASE statement in Function

From: "Hengky Lie" <hengkyliwandouw(at)gmail(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Need help with CASE statement in Function
Date: 2007-10-03 16:44:04
Message-ID: 000d01c805dc$9e8db450$1d00a8c0@hengky
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Dawid Kuroczko 2007-10-03 17:04:22 Re: Need help with CASE statement in Function
Previous Message Dawid Kuroczko 2007-10-03 15:41:04 Re: Finding broken regex'es