Thread: COALESCE not filtering well.
G'Day!
I have issues with filtering the data based on the criteria. Please take a look at the way I use COALESCE especially the WHERE part of my function.
The function is not returning me a filtered result.
for example, if I try to execute the function as follows:
SELECT * FROM sp_item(10,NULL); [It returns all the rows.... which is not what I am expecting... I'm expecting only the row with itemid=10 ]
Please advise.
Thanks & Regards,
Mohan
Mohan
--------------------------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION sp_item(itemid integer, itemname character varying)
RETURNS SETOF item AS
$BODY$
CREATE OR REPLACE FUNCTION sp_item(itemid integer, itemname character varying)
RETURNS SETOF item AS
$BODY$
declare
ret_row record;
BEGIN
FOR ret_row in
--SELECT itemid,itemcode,itemname,itemdescription,archived from item
SELECT * FROM item
WHERE ( ( COALESCE($1,0)=0 OR itemid=$1) AND (COALESCE($2, '')='' OR itemname LIKE '%'||$2||'%') ) LOOP
return next ret_row;
END LOOP;
return;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100
ROWS 1000;
On Mon, Jul 6, 2009 at 6:37 AM, Mohan Raj B<brightmohan@gmail.com> wrote: > CREATE OR REPLACE FUNCTION sp_item(itemid integer, itemname character > varying) > WHERE ( ( COALESCE($1,0)=0 OR itemid=$1) AND (COALESCE($2, '')='' OR > itemname LIKE '%'||$2||'%') ) LOOP itemid and itemname are your parameters, they're being substituted in the query so you're getting 10=10 and NULL LIKE '%'||NULL||'%' -- greg http://mit.edu/~gsstark/resume.pdf