Re: Using null or not null in function arguments - Mailing list pgsql-general

Michael Glaesemann wrote:
>
> On Jan 23, 2009, at 10:11 , Igor Katson wrote:
>
>> 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;
>
> Here's an alternate formulation that eliminates the CASE statements
> which I find hard to read:
>
> 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 (i_city_id IS NULL OR city_id = i_city_id)
>                AND (i_edu_id IS NULL OR edu_id = i_edu_id)
>                AND (i_firstname IS NULL OR upper(firstname) ~
> upper(i_firstname))
>                AND (i_lastname IS NULL OR upper(lastname) ~
> upper(i_lastname))
>          LIMIT limit_
>          OFFSET offset_
>      LOOP
>          RETURN NEXT rec;
>      END LOOP;
>      RETURN;
>   END;
> $$ language plpgsql;
>
> And you really don't even need to use PL/pgSQL: an SQL function would
> work just as well.
>
> 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,
>                                 <OUT columns>)
> RETURNS SETOF RECORD
> LANGUAGE SQL AS $$
>   SELECT *
>     FROM isocial_user.user
>     WHERE ($1 IS NULL OR city_id = i_city_id)
>           AND ($2 IS NULL OR edu_id = i_edu_id)
>           AND ($3 IS NULL OR upper(firstname) ~ upper(i_firstname))
>           AND ($4 IS NULL OR upper(lastname) ~ upper(i_lastname))
>     LIMIT $5
>     OFFSET $6
> $$;
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>
Thank you, Michael, that one looks prettier.
Sam, I'm not sure if this is correct to do that, as you I don't want to
remember what will happen, if you use NULL = NULL or upper(NULL) etc.:

  WHERE
    COALESCE(city_id = i_city_id, TRUE) AND
    COALESCE(edu_id  = i_edu_id,  TRUE) AND
    COALESCE(upper(firstname) ~ upper(i_firstname), TRUE) AND
    COALESCE(upper(lastname)  ~ upper(i_lastname),  TRUE)