From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Ian Harding <ianh(at)tpchd(dot)org> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: CAST(null as date)... |
Date: | 2002-06-18 23:13:01 |
Message-ID: | 20020618160900.G66586-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2002-06-18 23:17:32 | Re: Highly obscure and erratic |
Previous Message | Varun Kacholia | 2002-06-18 23:00:30 | Re: Highly obscure and erratic |