Обсуждение: 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