[GENERAL] COALESCE not filtering well.

[Mohan Raj B]

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

--------------------------------------------------------------------------------------------------------------------------------------------
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;