From: | Michael Glaesemann <grzm(at)seespotcode(dot)net> |
---|---|
To: | Igor Katson <descentspb(at)gmail(dot)com> |
Cc: | Sam Mason <sam(at)samason(dot)me(dot)uk>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Using null or not null in function arguments |
Date: | 2009-01-23 15:22:39 |
Message-ID: | 4F569930-F198-4EF3-AC22-337EC64B7FDA@seespotcode.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Igor Katson | 2009-01-23 15:32:17 | Re: Using null or not null in function arguments |
Previous Message | Igor Katson | 2009-01-23 15:11:25 | Re: Using null or not null in function arguments |