pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Letting a function work on NULL
3+ messages / 3 participants
[nested] [flat]

* Letting a function work on NULL
@ 1999-06-20 18:53  Anton de Wet <adw@obsidian.co.za>
  0 siblings, 2 replies; 3+ messages in thread

From: Anton de Wet @ 1999-06-20 18:53 UTC (permalink / raw)
  To: 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.





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] Letting a function work on NULL
@ 1999-06-21 10:57  Herouth Maoz <herouth@oumail.openu.ac.il>
  parent: Anton de Wet <adw@obsidian.co.za>
  1 sibling, 0 replies; 3+ messages in thread

From: Herouth Maoz @ 1999-06-21 10:57 UTC (permalink / raw)
  To: Anton de Wet <adw@obsidian.co.za>; pgsql-sql

At 21:53 +0300 on 20/06/1999, Anton de Wet wrote:


> But all gives:
>
> select ym(NULL);
> ERROR:  typeidTypeRelid: Invalid type - oid = 0
>
> Is there any way to specify an exception on the input type?

There are two different issues here. First, passing the "literal" NULL to
functions always results in this. In SQL92, NULL is not even considered a
literal. Try this, for example:

testing=> select abstime( NULL ) from test2;
ERROR:  typeidTypeRelid: Invalid type - oid = 0

(abstime() is a builting function converting to abstime);

In order to test the functionality of a function in nulls, you should
create a table, some of whose values are null:

testing=> select f, abstime( f ) from test2;
           f|abstime
------------+----------------------------
3.1415926535|Sat Jan 01 02:00:03 2000 IST
            |
     1.11111|Sat Jan 01 02:00:01 2000 IST
(3 rows)

That's about your testing procedure. About your problem in itself: you
should either use a pl/pgsql procedure or make use of COALESCE.

One last note: EXCEPT is a query operator that excludes certain tuples from
a query. It's not an "else". When you write "SELECT something1 EXCEPT
SELECT something2", it means "select all the 'something1' objects which are
not 'something2'

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] Letting a function work on NULL
@ 1999-06-21 12:13  José Soares <jose@sferacarta.com>
  parent: Anton de Wet <adw@obsidian.co.za>
  1 sibling, 0 replies; 3+ messages in thread

From: José Soares @ 1999-06-21 12:13 UTC (permalink / raw)
  To: Anton de Wet <adw@obsidian.co.za>; +Cc: 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'

^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~1999-06-21 12:13 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-06-20 18:53 Letting a function work on NULL Anton de Wet <adw@obsidian.co.za>
1999-06-21 10:57 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-06-21 12:13 ` José Soares <jose@sferacarta.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox