Re: creating a function returning FALSE on NULL input ?

[Yvon Thoraval <yvon.thoraval@xxxxxxxxx>]

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@xxxxxxxxx>

On Nov 7, 2012, at 0:33, Yvon Thoraval <yvon.thoraval@xxxxxxxxx> 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