Re: [SQL] Letting a function work on NULL

<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 />