Re: CAST(null as date)...

That does, indeed work! However, mine looked more like this....

creat function nullifzls(text) returns text as '
if {[string length $1] == 0} {
return NULL
} else {
return $1
}
' language 'pltcl';

It doesn't work. I don't do the explicit cast before returning the value, but I thought defining the return datatype was enough. It seems to show up as text...

BTW, it's no big deal, I just replaced it with:

case when length($foo) = 0 then NULL else ''$foo'' end

>>> Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> 06/18/02 04:13PM >>>

On Tue, 18 Jun 2002, Ian Harding wrote:

> I tried to create a function to return the string 'null' (without
> quotes, of course...) if the input was a zero length string, so I
> could use it in casting arguments to another function such as:
>
> select myfunction(cast(nullifzls($maybeemptyvar) as date),
> cast(....));
>
> However I have this dilemma. The return type from the nullifzls
> function is text. Text blows up the cast. Is there any way to make
> this work, or should I do something else?

Wouldn't you want the function to return NULL, not 'null' since
the latter is a perfectly happily defined string containing the
word null? ;)

create function ff(text) returns text as 'select case when $1 = '''' then
cast(NULL as text) else $1 end;' language 'sql';

sszabo=# select ff('');
ff
----

(1 row)

sszabo=# select cast (ff('') as date);
ff
----

(1 row)

sszabo=# select cast (ff('') as date) is NULL;
?column?
----------
t
(1 row)