Re: Using null or not null in function arguments - Mailing list pgsql-general

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@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------