creating a function returning FALSE on NULL input ?

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.

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

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

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.

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