Resp.: Using null or not null in function arguments

2009/1/23, Igor Katson <descentspb@gmail.com>:
> I have a row search function, smth like
>
> CREATE OR REPLACE FUNCTION user_func.search_users
> (i_city_id int, i_edu_id int, i_first_name text, i_last_name text,
> limit_ int, offset_ int) RETURNS SETOF user.user AS $$
> ..... SELECT * FROM user WHERE
>       city_id = i_city_id
> ...
> $$ language plpgsql;
>
> How do I write a function without complex logic, which will do:
> a) If the input argument is NULL, then the corresponding select
> statement will change from
>
> column = arg
> to
> column IS NULL
>
> maybe there is some built-in function for that?
>
> b) If the input argument is NULL, then the corresponding select
> statement will be removed, so if it was not written.
>
> I think, this is a common problem.
>


Try:

SET transform_null_equals ON;
at function's begining.
http://www.postgresql.org/docs/current/interactive/runtime-config-compatible.html#RUNTIME-CONFIG-COMPATIBLE-VERSION
18.12.2. Platform and Client Compatibility

Osvaldo