Re: Using null or not null in function arguments

Sam Mason wrote:
> On Fri, Jan 23, 2009 at 02:16:34PM +0300, Igor Katson wrote:
>
>> a) If the input argument is NULL, then the corresponding select
>> statement will change from
>>
>> column = arg
>> to
>> column IS NULL
>>
>
> I think you want to use the IS [NOT] DISTINCT FROM operator. It
> works like the = and <> operators. i.e. the following expressions are
> equivalent:
>
> x IS NOT DISTINCT FROM y
>
> and
>
> CASE WHEN x IS NULL THEN y IS NULL
> ELSE COALESCE(x = y, FALSE) END
>
>
>
>> b) If the input argument is NULL, then the corresponding select
>> statement will be removed, so if it was not written.
>>
>
> not sure what you mean here, but maybe one of the existing suggestions
> may help or the "RETURNS NULL ON NULL INPUT" flag when you're creating
> the function may be what you're looking for.
>
>
>
That one is awesome, thanks, I completely forgot about CASE statement.
The search func now looks as follows, and works perfectly:

CREATE OR REPLACE FUNCTION isocial_user_func.search_users
(i_city_id int, i_edu_id int, i_firstname text, i_lastname text,
limit_ int, offset_ int) RETURNS SETOF isocial_user.user AS $$
DECLARE
rec isocial_user.user;
BEGIN
FOR rec IN SELECT * FROM isocial_user.user
WHERE
CASE
WHEN i_city_id IS NULL THEN TRUE
ELSE city_id = i_city_id
END AND
CASE
WHEN i_edu_id IS NULL THEN TRUE
ELSE edu_id = i_edu_id
END AND
CASE
WHEN i_firstname IS NULL THEN TRUE
ELSE upper(firstname) ~ upper(i_firstname)
END AND
CASE
WHEN i_lastname IS NULL THEN TRUE
ELSE upper(lastname) ~ upper(i_lastname)
END
LIMIT limit_
OFFSET offset_
LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
$$ language plpgsql;