Letting a function work on NULL

Поиск
Список
Период
Сортировка
От Anton de Wet
Тема Letting a function work on NULL
Дата
Msg-id Pine.LNX.4.10.9906202049050.21065-100000@ra.obsidian.co.za
обсуждение исходный текст
Ответы Re: [SQL] Letting a function work on NULL  (Herouth Maoz <herouth@oumail.openu.ac.il>)
Список pgsql-sql
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.




В списке pgsql-sql по дате отправления:

Предыдущее
От: Thomas Good
Дата:
Сообщение: Re: [SQL] DISTINCT count(*), possible?
Следующее
От: Herouth Maoz
Дата:
Сообщение: Re: [SQL] Letting a function work on NULL