oracle.nvl

43 views
Skip to first unread message

r.taka...@jp.fujitsu.com

unread,
Oct 25, 2018, 11:17:51 PM10/25/18
to Better Oracle functions support
Hi,


In relase 3.6.0, these two oracle.nvl functions are released.


CREATE OR REPLACE FUNCTION oracle.nvl(bigint, int)
RETURNS bigint AS $$
SELECT coalesce($1, $2)
$$ LANGUAGE sql IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION oracle.nvl(numeric, int)
RETURNS numeric AS $$
SELECT coalesce($1, $2)
$$ LANGUAGE sql IMMUTABLE STRICT;


I expect these functions to work like Oracle's nvl.
However, these functions works as follows.


postgres=# select oracle.nvl(null::bigint, 0);
 nvl
-----

(1 row)

postgres=# select oracle.nvl(null::numeric, 0);
 nvl
-----

(1 row)


I think the problem is that these functions are defined as STRICT functions.
According to the PostgreSQL Documentation, STRICT indicates that the function always returns null whenever any of its arguments are null.
I think the solution is to remove STRICT.



Regards,
Ryohei Takahashi

Pavel Stehule

unread,
Oct 26, 2018, 1:12:20 AM10/26/18
to orafce-...@googlegroups.com
Hi

pá 26. 10. 2018 v 5:17 odesílatel <r.taka...@jp.fujitsu.com> napsal:
sure. Should be fixed now.

Thank you for report

Regards

Pavel




Regards,
Ryohei Takahashi

--
You received this message because you are subscribed to the Google Groups "Better Oracle functions support" group.
To post to this group, send email to orafce-...@googlegroups.com.
Visit this group at https://groups.google.com/group/orafce-general.

r.taka...@jp.fujitsu.com

unread,
Oct 26, 2018, 2:16:17 AM10/26/18
to Better Oracle functions support
Thank you for fixing immediately.

To let more users know these convenient functions, I think these functions should be written in the following document as with public.nvl.

The document of NVL says 'Specify the same data types for expr1 and expr2.',  but oracle.nvl does not need to.


Regards,
Ryohei Takahashi



2018年10月26日金曜日 14時12分20秒 UTC+9 Pavel Stehule:

Pavel Stehule

unread,
Oct 29, 2018, 3:40:31 AM10/29/18
to orafce-...@googlegroups.com
Hi

pá 26. 10. 2018 v 8:16 odesílatel <r.taka...@jp.fujitsu.com> napsal:
Thank you for fixing immediately.

To let more users know these convenient functions, I think these functions should be written in the following document as with public.nvl.

The document of NVL says 'Specify the same data types for expr1 and expr2.',  but oracle.nvl does not need to.

It is still valid for almost all types.
 
postgres=# \df *.nvl
                         List of functions
┌────────┬──────┬──────────────────┬────────────────────────┬──────┐
│ Schema │ Name │ Result data type │  Argument data types   │ Type │
╞════════╪══════╪══════════════════╪════════════════════════╪══════╡
│ oracle │ nvl  │ bigint           │ bigint, integer        │ func │
│ oracle │ nvl  │ numeric          │ numeric, integer       │ func │
│ public │ nvl  │ anyelement       │ anyelement, anyelement │ func │
└────────┴──────┴──────────────────┴────────────────────────┴──────┘
(3 rows)

Only combination bigint | numeric, int is different. But it is often pattern.

select nvl(bigint, 0), select nvl(numeric, 0). I wrote there sentence: This is not necessary for types (numeric, int) and (bigint, int).

Regards

Pavel

r.taka...@jp.fujitsu.com

unread,
Oct 29, 2018, 5:02:53 AM10/29/18
to Better Oracle functions support
Thank you for fixing.
I'll use them.


Regards,
Ryohei Takahashi

Pavel Stehule

unread,
Oct 29, 2018, 5:07:06 AM10/29/18
to orafce-...@googlegroups.com


po 29. 10. 2018 v 10:02 odesílatel <r.taka...@jp.fujitsu.com> napsal:
Thank you for fixing.
I'll use them.


I thank you for report :)

Pavel
Reply all
Reply to author
Forward
0 new messages