pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNeed help with CASE statement in Function
3+ messages / 3 participants
[nested] [flat]
* Need help with CASE statement in Function
@ 2007-10-03 16:44 Hengky Lie <hengkyliwandouw@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Hengky Lie @ 2007-10-03 16:44 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Need help with CASE statement in Function
@ 2007-10-03 17:04 Dawid Kuroczko <qnex42@gmail.com>
parent: Hengky Lie <hengkyliwandouw@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Dawid Kuroczko @ 2007-10-03 17:04 UTC (permalink / raw)
To: Hengky Lie <hengkyliwandouw@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Need help with CASE statement in Function
@ 2007-10-03 17:07 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Hengky Lie <hengkyliwandouw@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Richard Broersma Jr @ 2007-10-03 17:07 UTC (permalink / raw)
To: Hengky Lie <hengkyliwandouw@gmail.com>; pgsql-sql
--- 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-10-03 17:07 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-03 16:44 Need help with CASE statement in Function Hengky Lie <hengkyliwandouw@gmail.com>
2007-10-03 17:04 ` Dawid Kuroczko <qnex42@gmail.com>
2007-10-03 17:07 ` Richard Broersma Jr <rabroersma@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox