Need help with CASE statement in Function

Поиск
Список
Период
Сортировка
От Hengky Lie
Тема Need help with CASE statement in Function
Дата
Msg-id 000d01c805dc$9e8db450$1d00a8c0@hengky
обсуждение исходный текст
Ответы Re: Need help with CASE statement in Function
Re: Need help with CASE statement in Function
Список 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

 

 

В списке pgsql-sql по дате отправления: