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: