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

From Michael Glaesemann
Subject Re: Using null or not null in function arguments
Date
Msg-id 4F569930-F198-4EF3-AC22-337EC64B7FDA@seespotcode.net
Whole thread Raw
In response to Re: Using null or not null in function arguments  (Igor Katson <descentspb@gmail.com>)
Responses Re: Using null or not null in function arguments  (Igor Katson <descentspb@gmail.com>)
List pgsql-general
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




pgsql-general by date:

Previous
From: Igor Katson
Date:
Subject: Re: Using null or not null in function arguments
Next
From: Igor Katson
Date:
Subject: Re: Using null or not null in function arguments