Proposal for adding Oracle compatible LPAD and RPAD

[Amit Langote]

Hi,

I propose to add Oracle compatible lpad and rpad functions with the following syntax;

LPAD(string, length[, fill])

RPAD(string, length[, fill])

When working with wide characters (for example, Japanese characters), there are some differences between the behavior of padding functions on Oracle and PostgreSQL. For example, Oracle counts in terms of display length/width on the terminal screen unlike PostgreSQL which counts in terms of characters. An example is shown below: 

postgres=# SELECT '|' || oracle.lpad('あbcd'::varchar2(5), 10, 'xい'::varchar2(5)) || '|';

   ?column?   

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

 |xいxいあbc|

(1 row)

postgres=# SELECT '|' || lpad('あbcd'::varchar2(5), 10, 'xい'::varchar2(5)) || '|';

     ?column?     

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

 |xいxいxいxあbc|

(1 row)

Please note that each Japanese character shown above has display length of 2 and others have 1.

One other difference in behavior is that padding spaces of CHAR strings are removed in case of PostgreSQL but not in case of Oracle. An example is shown below:

-- note that the fill 'xい' is 5 characters with total display length of 1+2+1+1+1 = 6 and the string 'あbcd' has display length of 2+1+1+1 = 5. So, to fit the output display length of 10, one padding space of fill (which is CHAR) has to be removed.

postgres=# SELECT '|' || oracle.lpad('あbcd'::text, 10, 'xい'::char(5)) || '|';

   ?column?   

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

 |xい  あbcd|

(1 row)

-- note that the padding spaces of fill here are insignificant

postgres=# SELECT '|' || lpad('あbcd'::text, 10, 'xい'::char(5)) || '|';

     ?column?     

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

 |xいxいxいあbcd|

(1 row)

Moreover, the proposed supports 'string' and 'fill' to be of any of the types CHAR, VARCHAR, TEXT, VARCHAR2 or NVARCHAR2.

Hope this is a worthwhile enhancement.

Please take a look here:

https://github.com/amitlan/orafce/commit/70791b98942141933e87bf3f1c7d86741da7d21b

If alright, I will send a pull request.

Thanks,

Amit

[Pavel Stehule]

Hi

this patch looks well

please, rebase it against last changes in source tree (I removed support for pg 9.2)

* please, check your code for PostgreSQL 8.3

* please, actualize (move func def) to builtins.h

Regards

Pavel

--
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 http://groups.google.com/group/orafce-general.

[Amit Langote]

On Wed, Jan 7, 2015 at 3:28 AM, Pavel Stehule <pavel....@gmail.com> wrote:
> Hi
>
> this patch looks well
>

Thank you!

> please, rebase it against last changes in source tree (I removed support for
> pg 9.2)
>

OK, done!

> * please, check your code for PostgreSQL 8.3
>

Done! Works for all PG versions >= 8.3 (compiles well. regression
tests run fine)

> * please, actualize (move func def) to builtins.h
>

Done!

I sent a pull request.

Thanks,
Amit