Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1]) by hub.org (8.9.3/8.9.3) with ESMTP id IAA26173 for ; Mon, 21 Jun 1999 08:53:24 -0400 (EDT) (envelope-from jose@sferacarta.com) Received: from proxy.sferacarta.com (mail@sfcabop1.nettuno.it [193.207.10.213]) by bologna.nettuno.it (8.8.6/8.8.8/NETTuno 3.2) with ESMTP id OAA22462; Mon, 21 Jun 1999 14:52:20 +0200 (MDT) Received: from rosso.sferacarta.com (sferacarta.com) [10.20.30.5] by proxy.sferacarta.com with esmtp (Exim 2.05 #1 (Debian)) id 10w4sG-0005sf-00; Mon, 21 Jun 1999 14:16:36 +0000 Message-ID: <376E2C54.9EB1CE1F@sferacarta.com> Date: Mon, 21 Jun 1999 14:13:08 +0200 From: =?iso-8859-1?Q?Jos=E9?= Soares X-Mailer: Mozilla 4.5 [it] (Win95; I) X-Accept-Language: it MIME-Version: 1.0 To: Anton de Wet CC: pgsql-sql@postgresql.org Subject: Re: [SQL] Letting a function work on NULL References: Content-Type: multipart/alternative; boundary="------------D0BE82C792B297780892704D" --------------D0BE82C792B297780892704D Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit If you are using v6.5 you can use the coalesce() function. COALESCE returns null if and ond only its operands all evaluate to null; otherwise it returns the value of its first nonnull operand. hygea=> select mantello, coalesce(mantello,'null') from cani; mantello|case --------+---- |null AL |AL |null (3 rows) Anton de Wet ha scritto: > 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. ______________________________________________________________ PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Jose' --------------D0BE82C792B297780892704D Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit If you are using v6.5 you can use the coalesce() function.
COALESCE returns null if and ond only its operands all evaluate to null; otherwise it returns the value of its first nonnull operand.

hygea=> select mantello, coalesce(mantello,'null') from cani;
mantello|case
--------+----
        |null
AL      |AL
        |null
(3 rows)
 
 

Anton de Wet ha scritto:

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.


______________________________________________________________
PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Jose'
  --------------D0BE82C792B297780892704D--