Re: Using null or not null in function arguments

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.

Thanks in advance and regards,
Igor Katson.

On 23/01/2009 11:16, Igor Katson wrote:

> 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

You could build your statement dynamically as a string, then execute it
using EXECUTE:

http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

> b) If the input argument is NULL, then the corresponding select
> statement will be removed, so if it was not written.

Likewise - something like

if i_city_id is null then
... build statement...
... execute statement ...
end if;

HTH,

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod(at)iol(dot)ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

Raymond O'Donnell wrote:
> On 23/01/2009 11:16, Igor Katson wrote:
>
>
>> 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
>>
>
> You could build your statement dynamically as a string, then execute it
> using EXECUTE:
>
> http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
>
>
>
>> b) If the input argument is NULL, then the corresponding select
>> statement will be removed, so if it was not written.
>>
>
> Likewise - something like
>
> if i_city_id is null then
> ... build statement...
> ... execute statement ...
> end if;
>
> HTH,
>
> Ray.
>
Thanks, Ray, but I see now, that I didn't explain the exact problem
correctly. The one is, that this kind of functions (search ones) can
have tens of arguments, and the more the amount of arguments is, the
more combinations of IF ... THEN conditionals will be present to build
the logic. If I have a couple of them, this can be easily handled
through IF THEN, or dynamic statements, but what if I have tens of 'em?

2009/1/23, Igor Katson <descentspb(at)gmail(dot)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

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.

--
Sam http://samason.me.uk/

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;

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

Michael Glaesemann wrote:
>
> 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
>
>
>
Thank you, Michael, that one looks prettier.
Sam, I'm not sure if this is correct to do that, as you I don't want to
remember what will happen, if you use NULL = NULL or upper(NULL) etc.:

WHERE
COALESCE(city_id = i_city_id, TRUE) AND
COALESCE(edu_id = i_edu_id, TRUE) AND
COALESCE(upper(firstname) ~ upper(i_firstname), TRUE) AND
COALESCE(upper(lastname) ~ upper(i_lastname), TRUE)

On Fri, Jan 23, 2009 at 06:32:17PM +0300, Igor Katson wrote:
> Sam, I'm not sure if this is correct to do that, as you I don't want to
> remember what will happen, if you use NULL = NULL or upper(NULL) etc.:
>
> WHERE
> COALESCE(city_id = i_city_id, TRUE) AND
> COALESCE(edu_id = i_edu_id, TRUE) AND
> COALESCE(upper(firstname) ~ upper(i_firstname), TRUE) AND
> COALESCE(upper(lastname) ~ upper(i_lastname), TRUE)

I'm not quite sure what you mean when you say "I don't want to remember
what will happen". Here is a state table of the various options you've
presented:

param tbl Sam's Your's/Michael's
NULL NULL TRUE NULL
NULL 0 TRUE TRUE
NULL 1 TRUE TRUE
0 NULL TRUE NULL
0 0 TRUE TRUE
0 1 FALSE FALSE
1 NULL TRUE NULL
1 0 FALSE FALSE
1 1 TRUE TRUE

The "tbl" column is the value for, say, edu_id; the "param" is the
matching parameter value, say i_edu_id. "Sam's" is the output of doing a
COALESCE and the "Your's/Michael's" column is the output of doing your
original CASE statement or Michael's OR variant. The thing to note are
the extra NULLs in your variant as these will cause any row with a NULL
value in the table to never get returned. This may, or may not, be what
you want!

--
Sam http://samason.me.uk/