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
|
Список | 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 по дате отправления: