Thread: Letting a function work on NULL

I have a function that returns an integer for 100*year+month for a given
date. Currently it croaks if the date is NULL and I would like it to
return 0 (or even NULL).

Function:

create function ym (date) returns int4 as 'select 
date_part(\'year\',$1)::int4*100+date_part(\'month\',$1)::int4 ' language
'sql';

I've tried:
create function ym (date) returns int4 as 'select
date_part(\'year\',$1)::int4*100+date_part(\'month\',$1)::int4 where $1 is
not null except select 0 where $1 is null' language 'sql';

(and variations with 0::int4, union instead of except, and a few desparate
others)

But all gives:

select ym(NULL);
ERROR:  typeidTypeRelid: Invalid type - oid = 0

Is there any way to specify an exception on the input type?

Anton


--------------------------------------------------------------------
Smith's Law:
Any sufficiently optimistic statement is indistinguishable from sarcasm.

At 21:53 +0300 on 20/06/1999, Anton de Wet wrote:


> But all gives:
>
> select ym(NULL);
> ERROR:  typeidTypeRelid: Invalid type - oid = 0
>
> Is there any way to specify an exception on the input type?

There are two different issues here. First, passing the "literal" NULL to
functions always results in this. In SQL92, NULL is not even considered a
literal. Try this, for example:

testing=> select abstime( NULL ) from test2;
ERROR:  typeidTypeRelid: Invalid type - oid = 0

(abstime() is a builting function converting to abstime);

In order to test the functionality of a function in nulls, you should
create a table, some of whose values are null:

testing=> select f, abstime( f ) from test2;          f|abstime
------------+----------------------------
3.1415926535|Sat Jan 01 02:00:03 2000 IST           |    1.11111|Sat Jan 01 02:00:01 2000 IST
(3 rows)

That's about your testing procedure. About your problem in itself: you
should either use a pl/pgsql procedure or make use of COALESCE.

One last note: EXCEPT is a query operator that excludes certain tuples from
a query. It's not an "else". When you write "SELECT something1 EXCEPT
SELECT something2", it means "select all the 'something1' objects which are
not 'something2'

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

<tt>If you are using v6.5 you can use the coalesce() function.</tt><br /><tt>COALESCE returns null if and ond only its
operandsall evaluate to null; otherwise it returns the value of its first nonnull
operand.</tt><tt></tt><p><tt>hygea=>select mantello, coalesce(mantello,'null') from cani;</tt><br
/><tt>mantello|case</tt><br/><tt>--------+----</tt><br /><tt>        |null</tt><br /><tt>AL      |AL</tt><br
/><tt>       |null</tt><br /><tt>(3 rows)</tt><br /><tt></tt>  <br />  <p>Anton de Wet ha scritto: <blockquote
type="CITE">Ihave a function that returns an integer for 100*year+month for a given <br />date. Currently it croaks if
thedate is NULL and I would like it to <br />return 0 (or even NULL). <p>Function: <p>create function ym (date) returns
int4as 'select <br />date_part(\'year\',$1)::int4*100+date_part(\'month\',$1)::int4 ' language <br />'sql'; <p>I've
tried:<br />create function ym (date) returns int4 as 'select <br
/>date_part(\'year\',$1)::int4*100+date_part(\'month\',$1)::int4where $1 is <br />not null except select 0 where $1 is
null'language 'sql'; <p>(and variations with 0::int4, union instead of except, and a few desparate <br />others) <p>But
allgives: <p>select ym(NULL); <br />ERROR:  typeidTypeRelid: Invalid type - oid = 0 <p>Is there any way to specify an
exceptionon the input type? <p>Anton <p>-------------------------------------------------------------------- <br
/>Smith'sLaw: <br />Any sufficiently optimistic statement is indistinguishable from sarcasm.</blockquote><p><br
/>______________________________________________________________<br />PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled
bygcc 2.7.2.3 <br />^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ <br />Jose' <br />