Thread: CAST(null as date)...
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 anyway to make this work, or should I do something else? plantest=# select cast(null as date); ?column? ---------- (1 row) plantest=# select nullifzls(''); nullifzls --------------- null (1 row) plantest=# select cast(nullifzls('') as date); ERROR: Bad date external representation 'null' plantest=# select cast(cast('null' as text) as date); ERROR: Bad date external representation 'null' Thanks... Ian
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)
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 wasenough. 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@megazone23.bigpanda.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)
"Ian Harding" <ianh@tpchd.org> writes: > creat function nullifzls(text) returns text as ' > if {[string length $1] == 0} { > return NULL > } else { > return $1 > } > ' language 'pltcl'; Try return_null regards, tom lane
Argh! I have a dog-eared copy of that page of the docs that I printed on 5/11/01. Time to print a new one!! Thanks! Ian A. Harding Programmer/Analyst II Tacoma-Pierce County Health Department (253) 798-3549 mailto: iharding@tpchd.org We have only two things to worry about: That things will never get back to normal, and that they already have. >>> Tom Lane <tgl@sss.pgh.pa.us> 06/19/02 05:57AM >>> "Ian Harding" <ianh@tpchd.org> writes: > creat function nullifzls(text) returns text as ' > if {[string length $1] == 0} { > return NULL > } else { > return $1 > } > ' language 'pltcl'; Try return_null regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly