Thread: Need help with CASE statement in Function

Need help with CASE statement in Function

From
"Hengky Lie"
Date:
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Dear friends,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I am a new user to postgreSQL and really need help to solve my “stupid ?” problem.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I have created  function with 4 arguments like this :</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Courier New" size="2"><span style="font-size:10.0pt;
font-family:"Courier New"">CREATE OR REPLACE FUNCTION "public"."fHistoryCard" (begdate date, enddate date, ProductID
varchar,storeID varchar) RETURNS SETOF "public"."tbltrans" AS</span></font><p class="MsoNormal"><font face="Courier
New"size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">$body$</span></font><p class="MsoNormal"><font face="Courier New" size="2"><span
style="font-size:10.0pt;
font-family:"Courier New"">/* New function body */</span></font><p class="MsoNormal"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New""> </span></font><p class="MsoNormal"><font face="Courier New" size="2"><span
style="font-size:10.0pt;
font-family:"Courier New"">select * from tbltrans</span></font><p class="MsoNormal"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New"">where tbltrans."Date” between $1 and $2</span></font><p class="MsoNormal"><font
face="CourierNew" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">and upper(tbltrans."ProductID")=upper($3)</span></font><p class="MsoNormal"><b><font
face="CourierNew" size="2"><span style="font-size: 
10.0pt;font-family:"Courier New";font-weight:bold">and tbltrans."StoreID"=$4</span></font></b><p
class="MsoNormal"><fontface="Courier New" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">order by tbltrans."Tanggal";</span></font><p class="MsoNormal"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New"">$body$</span></font><p class="MsoNormal"><font face="Courier New" size="2"><span
style="font-size:10.0pt;
font-family:"Courier New"">LANGUAGE 'sql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">---------------------</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">My Question is : How to make argument 4 optional ? When IS NULL the function will show all
transactionbetween date $1 and $2 and product ID=$3 </span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">But when $4 is not null then the fucntion will show all transaction between date $1 and $2 and
productID=$3 AND STOREID=$4.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I really appreciate any suggestions.</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Thanks a lot.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Hengky</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font></div>

Re: Need help with CASE statement in Function

From
"Dawid Kuroczko"
Date:
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


Re: Need help with CASE statement in Function

From
Richard Broersma Jr
Date:
--- Hengky Lie <hengkyliwandouw@gmail.com> 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.