Sam Mason wrote:
On Fri, Jan 23, 2009 at 02:16:34PM +0300, Igor Katson wrote:
a) If the input argument is NULL, then the corresponding select
statement will change from
column = arg
to
column IS NULL
I think you want to use the IS [NOT] DISTINCT FROM operator. It
works like the = and <> operators. i.e. the following expressions are
equivalent:
x IS NOT DISTINCT FROM y
and
CASE WHEN x IS NULL THEN y IS NULL
ELSE COALESCE(x = y, FALSE) END
b) If the input argument is NULL, then the corresponding select
statement will be removed, so if it was not written.
not sure what you mean here, but maybe one of the existing suggestions
may help or the "RETURNS NULL ON NULL INPUT" flag when you're creating
the function may be what you're looking for.
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;
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
