Re: Using null or not null in function arguments
От | Michael Glaesemann |
---|---|
Тема | Re: Using null or not null in function arguments |
Дата | |
Msg-id | 4F569930-F198-4EF3-AC22-337EC64B7FDA@seespotcode.net обсуждение исходный текст |
Ответ на | Re: Using null or not null in function arguments (Igor Katson <descentspb@gmail.com>) |
Ответы |
Re: Using null or not null in function arguments
(Igor Katson <descentspb@gmail.com>)
|
Список | 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 по дате отправления: