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;