From: | José Soares <jose(at)sferacarta(dot)com> |
---|---|
To: | Anton de Wet <adw(at)obsidian(dot)co(dot)za> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: [SQL] Letting a function work on NULL |
Date: | 1999-06-21 12:13:08 |
Message-ID: | 376E2C54.9EB1CE1F@sferacarta.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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'
From | Date | Subject | |
---|---|---|---|
Next Message | Unprivileged user | 1999-06-21 14:32:31 | General Bug Report: CREATE TABLE AS doesn't work |
Previous Message | Herouth Maoz | 1999-06-21 10:57:31 | Re: [SQL] Letting a function work on NULL |