oracle.nvl

[r.taka...@jp.fujitsu.com]

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]

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

https://github.com/orafce/orafce/commit/cf58d28bbad08674f47625d636018bf7b3907404

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]

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.

https://github.com/orafce/orafce/blob/master/doc/orafce_documentation/Orafce_Documentation_05.md

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]

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.

https://github.com/orafce/orafce/blob/master/doc/orafce_documentation/Orafce_Documentation_05.md

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]

Thank you for fixing.

I'll use them.

Regards,

Ryohei Takahashi

[Pavel Stehule]

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