Postgres Pro
Downloads
Home   >   mailing lists

Thread: creating a function returning FALSE on NULL input ?

creating a function returning FALSE on NULL input ?

From
Yvon Thoraval
Date:
I'd like to create a function :
returning true if the length of the text arg is greater than 0 ;
false otherwise ;

and also returning false when arg is NULL, then i wrote :

CREATE FUNCTION has_infos(text) RETURNS boolean AS 'select character_length($1) > 0;' LANGUAGE SQL IMMUTABLE RETURNS FALSE ON NULL INPUT;

where i get the error :

ERROR:  syntax error at or near "FALSE"
LINE 1: ...r_length($1) > 0;' LANGUAGE SQL IMMUTABLE RETURNS FALSE ON N...

with "RETURNS NULL on NULL INPUT", that's OK.

--
Yvon


Re: creating a function returning FALSE on NULL input ?

From
David Johnston
Date:
On Nov 7, 2012, at 0:33, Yvon Thoraval <yvon.thoraval@gmail.com> wrote:

I'd like to create a function :
returning true if the length of the text arg is greater than 0 ;
false otherwise ;

and also returning false when arg is NULL, then i wrote :

CREATE FUNCTION has_infos(text) RETURNS boolean AS 'select character_length($1) > 0;' LANGUAGE SQL IMMUTABLE RETURNS FALSE ON NULL INPUT;

where i get the error :

ERROR:  syntax error at or near "FALSE"
LINE 1: ...r_length($1) > 0;' LANGUAGE SQL IMMUTABLE RETURNS FALSE ON N...

with "RETURNS NULL on NULL INPUT", that's OK.

--
Yvon


You have to allow for null input and deal with it in the function body.  Usually via a CASE structure.

David J.

Re: creating a function returning FALSE on NULL input ?

From
Yvon Thoraval
Date:
Fine thanks,I've found that way :
recettes=> DROP FUNCTION has_infos(text);
DROP FUNCTION


recettes=> CREATE FUNCTION has_infos(text DEFAULT '') RETURNS boolean AS $BODY$
recettes$> BEGIN
recettes$> IF character_length($1) > 0 THEN
recettes$>   RETURN TRUE;
recettes$> ELSE
recettes$>   RETURN FALSE;
recettes$> END IF ;
recettes$> END
recettes$> $BODY$
recettes-> LANGUAGE 'plpgsql' ;
CREATE FUNCTION

I've verified, it works on NULL input...



2012/11/7 David Johnston <polobo@yahoo.com>
On Nov 7, 2012, at 0:33, Yvon Thoraval <yvon.thoraval@gmail.com> wrote:

I'd like to create a function :
returning true if the length of the text arg is greater than 0 ;
false otherwise ;

and also returning false when arg is NULL, then i wrote :

CREATE FUNCTION has_infos(text) RETURNS boolean AS 'select character_length($1) > 0;' LANGUAGE SQL IMMUTABLE RETURNS FALSE ON NULL INPUT;

where i get the error :

ERROR:  syntax error at or near "FALSE"
LINE 1: ...r_length($1) > 0;' LANGUAGE SQL IMMUTABLE RETURNS FALSE ON N...

with "RETURNS NULL on NULL INPUT", that's OK.

--
Yvon


You have to allow for null input and deal with it in the function body.  Usually via a CASE structure.

David J.




--
Yvon


Re: creating a function returning FALSE on NULL input ?

From
Tom Lane
Date:
Yvon Thoraval <yvon.thoraval@gmail.com> writes:
> CREATE FUNCTION has_infos(text) RETURNS boolean AS 'select
> character_length($1) > 0;' LANGUAGE SQL IMMUTABLE RETURNS FALSE ON NULL
> INPUT;

FWIW, "RETURNS NULL ON NULL INPUT" is a formulaic phrase specified
in the SQL standard.  It's not meant to be something you can plug an
arbitrary value into --- it has to be written exactly that way.
The traditional Postgres term is just "STRICT", which means precisely
the same thing.

            regards, tom lane

Re: creating a function returning FALSE on NULL input ?

From
Alban Hertroys
Date:
On 7 Nov 2012, at 6:33, Yvon Thoraval wrote:

> CREATE FUNCTION has_infos(text) RETURNS boolean AS 'select character_length($1) > 0;' LANGUAGE SQL IMMUTABLE RETURNS
FALSEON NULL INPUT; 

Try:
CREATE FUNCTION has_infos(text) RETURNS boolean AS 'select coalesce(character_length($1) > 0, false);' LANGUAGE SQL
IMMUTABLE;

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

Re: creating a function returning FALSE on NULL input ?

From
Yvon Thoraval
Date:
Fine, thanks it's shorter than mine and works too.
I'll adopt it ))



2012/11/7 Alban Hertroys <haramrae@gmail.com>
On 7 Nov 2012, at 6:33, Yvon Thoraval wrote:

> CREATE FUNCTION has_infos(text) RETURNS boolean AS 'select character_length($1) > 0;' LANGUAGE SQL IMMUTABLE RETURNS FALSE ON NULL INPUT;

Try:
CREATE FUNCTION has_infos(text) RETURNS boolean AS 'select coalesce(character_length($1) > 0, false);' LANGUAGE SQL IMMUTABLE;

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.




--
Yvon