new system views

[Pavel Stehule]

Hi

I am working on Orafce 3.6. It based on my experience with my last project, when we migrate bigger 15 years old project to Postgres.

Changes are mainly in generating script:

https://github.com/orafce/orafce/blob/master/orafce--3.5--3.6.sql

Please, check it. Enhance it if it is possible.

Any ideas for new release?

Regards

Pavel

[U ikki]

Hi,

Thank you for your proposal.

I think that it isn't necessary to add these LPAD to orafce.

You can't used LPAD(int,int,int) in PostgreSQL because implicit casting rules are different from oracle.

In other words, it isn't LPAD's incompatible.

For example,

If you add CAST rules, LPAD(int,int,int) become available in PostgreSQL.

ex) CREATE CAST (int4 AS text) WITH INOUT AS IMPLICIT;

How about you?

# If we add LPAD to orafce, should think about other function(rpad,substring, etc.).

# But it is very difficult.

----------------

oracle

----------------

SQL> select LPAD(123, 8,0) from hoge;

LPAD(123

--------

00000123

----------------

PostgreSQL

----------------

postgres=# select lpad(123,8,0);

ERROR:  function lpad(integer, integer, integer) does not exist

LINE 1: select lpad(123,8,0);

               ^

HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

postgres=# CREATE CAST (int4 AS text) WITH INOUT AS IMPLICIT;

CREATE CAST

postgres=# select lpad(123,8,0);

   lpad   

----------

 00000123

(1 row)

regards,

[Pavel Stehule]

2017-06-28 8:19 GMT+02:00 U ikki <ueh...@gmail.com>:

Hi,

Thank you for your proposal.

I think that it isn't necessary to add these LPAD to orafce.

You can't used LPAD(int,int,int) in PostgreSQL because implicit casting rules are different from oracle.

In other words, it isn't LPAD's incompatible.

For example,

If you add CAST rules, LPAD(int,int,int) become available in PostgreSQL.

ex) CREATE CAST (int4 AS text) WITH INOUT AS IMPLICIT;

How about you?

It is choose between two devils :) - and numeric lpad is less devil than automatic int to text cast.

implicit int to text conversion can do unclean compare between int and text - should be used text eq,lt,gt .. function or int eq,... function? 

  

# If we add LPAD to orafce, should think about other function(rpad,substring, etc.).

# But it is very difficult.

lpad is often used for formatting that it has sense. 

lpad(4, 4, 0) ... 0004

using rpad, substring, trim for numbers is usually something wrong, when developer lost a information about used data type and it should be fixed on application side. Any useless cast means lower performance - more CPU operations .. and SQL expression executor is interpret .. so it can be significant overhead, or using wrong estimator function -- text instead numeric

 

--
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-general@googlegroups.com.
Visit this group at https://groups.google.com/group/orafce-general.

[U ikki]

Hi,

Thank you for reply.

On Wednesday, June 28, 2017 at 3:42:02 PM UTC+9, Pavel Stehule wrote:

2017-06-28 8:19 GMT+02:00 U ikki <ueh...@gmail.com>:

Hi,

Thank you for your proposal.

I think that it isn't necessary to add these LPAD to orafce.

You can't used LPAD(int,int,int) in PostgreSQL because implicit casting rules are different from oracle.

In other words, it isn't LPAD's incompatible.

For example,

If you add CAST rules, LPAD(int,int,int) become available in PostgreSQL.

ex) CREATE CAST (int4 AS text) WITH INOUT AS IMPLICIT;

How about you?

It is choose between two devils :) - and numeric lpad is less devil than automatic int to text cast.

implicit int to text conversion can do unclean compare between int and text - should be used text eq,lt,gt .. function or int eq,... function? 

  

# If we add LPAD to orafce, should think about other function(rpad,substring, etc.).

# But it is very difficult.

lpad is often used for formatting that it has sense. 

lpad(4, 4, 0) ... 0004

using rpad, substring, trim for numbers is usually something wrong, when developer lost a information about used data type and it should be fixed on application side. Any useless cast means lower performance - more CPU operations .. and SQL expression executor is interpret .. so it can be significant overhead, or using wrong estimator function -- text instead numeric

 

I agree with you.

- Other functions(RPAD,SUBSTRIN, ...) is unneccesary.

- We should avoid use implicit cast.

regards,

To post to this group, send email to orafce-...@googlegroups.com.