| From: | Anton de Wet <adw(at)obsidian(dot)co(dot)za> |
|---|---|
| To: | pgsql-sql(at)postgreSQL(dot)org |
| Subject: | Letting a function work on NULL |
| Date: | 1999-06-20 18:53:53 |
| Message-ID: | Pine.LNX.4.10.9906202049050.21065-100000@ra.obsidian.co.za |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | 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.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Herouth Maoz | 1999-06-21 10:57:31 | Re: [SQL] Letting a function work on NULL |
| Previous Message | Thomas Good | 1999-06-20 12:47:00 | Re: [SQL] DISTINCT count(*), possible? |