Thread: proposal: unescape_text function

> On 23 Jun 2020, at 11:51, Pavel Stehule <pavel.stehule@gmail.com> wrote:

> I changed the name to more accurately "unicode_unescape". Patch is assigned

You've made this function return Oid, where it used to be void.  Was that a
copy-paste mistake? Else the code needs fixing as it doesn't return an Oid.

+Oid
+check_unicode_value(pg_wchar c)
+{
+   if (!is_valid_unicode_codepoint(c))
+       ereport(ERROR,
+               (errcode(ERRCODE_SYNTAX_ERROR),
+                errmsg("invalid Unicode escape value")));
+}

cheers ./daniel

The following review has been posted through the commitfest application:
make installcheck-world:  tested, passed
Implements feature:       tested, passed
Spec compliant:           tested, passed
Documentation:            tested, passed

Hi,

The patch looks good to me.

The new status of this patch is: Ready for Committer

On 2020-10-07 11:00, Pavel Stehule wrote:
>         Since the idea originated from unescaping unicode string
>         literals i.e.
>                 select unescape('Odpov\u011Bdn\u00E1 osoba');
> 
>         Shouldn't the built-in function support the above syntax as well?
> 
> 
>     good idea. The prefixes u (4 digits) and U (8 digits) are supported

I don't really get the point of this function.  There is AFAICT no 
function to produce this escaped format, and it's not a recognized 
interchange format.  So under what circumstances would one need to use this?

On 2020-11-29 18:36, Pavel Stehule wrote:
> 
>     I don't really get the point of this function.  There is AFAICT no
>     function to produce this escaped format, and it's not a recognized
>     interchange format.  So under what circumstances would one need to
>     use this?
> 
> 
> Some corporate data can be in CSV format with escaped unicode 
> characters. Without this function it is not possible to decode these 
> files without external application.

I would like some supporting documentation on this.  So far we only have 
one stackoverflow question, and then this implementation, and they are 
not even the same format.  My worry is that if there is not precise 
specification, then people are going to want to add things in the 
future, and there will be no way to analyze such requests in a 
principled way.

>> po 30. 11. 2020 v 22:15 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
>> napsal:
>>> I checked this and it is "prefix backslash-u hex" used by Java,
>>> JavaScript  or RTF -
>>> https://billposer.org/Software/ListOfRepresentations.html

If I look on that page, it appears that RTF is using a similar-looking
escape but in decimal rather than hex.

It would be important to define what is done with non-BMP characters?
Will there be another escape for a six- or eight-hexdigit format for
the codepoint, or will it be represented by two four-hexdigit escapes
for consecutive UTF-16 surrogates?

Regards,
-Chap

On 11/30/20 8:14 AM, Peter Eisentraut wrote:
> On 2020-11-29 18:36, Pavel Stehule wrote:
>>
>>     I don't really get the point of this function.  There is AFAICT no
>>     function to produce this escaped format, and it's not a recognized
>>     interchange format.  So under what circumstances would one need to
>>     use this?
>>
>>
>> Some corporate data can be in CSV format with escaped unicode
>> characters. Without this function it is not possible to decode these
>> files without external application.
>
> I would like some supporting documentation on this.  So far we only
> have one stackoverflow question, and then this implementation, and
> they are not even the same format.  My worry is that if there is not
> precise specification, then people are going to want to add things in
> the future, and there will be no way to analyze such requests in a
> principled way.
>
>
>


Also, should this be an extension? I'm dubious about including such
marginal uses in the core code unless there's a really good case for it.


cheers


andrew

On 2020-11-30 22:15, Pavel Stehule wrote:
>     I would like some supporting documentation on this.  So far we only
>     have
>     one stackoverflow question, and then this implementation, and they are
>     not even the same format.  My worry is that if there is not precise
>     specification, then people are going to want to add things in the
>     future, and there will be no way to analyze such requests in a
>     principled way.
> 
> 
> I checked this and it is "prefix backslash-u hex" used by Java, 
> JavaScript  or RTF - 
> https://billposer.org/Software/ListOfRepresentations.html

Heh.  The fact that there is a table of two dozen possible 
representations kind of proves my point that we should be deliberate in 
picking one.

I do see Oracle unistr() on that list, which appears to be very similar 
to what you are trying to do here.  Maybe look into aligning with that.

On 12/2/20 12:48 AM, Pavel Stehule wrote:
>
>
> st 2. 12. 2020 v 0:05 odesílatel Andrew Dunstan <andrew@dunslane.net
> <mailto:andrew@dunslane.net>> napsal:
>
>
>     On 11/30/20 8:14 AM, Peter Eisentraut wrote:
>     > On 2020-11-29 18:36, Pavel Stehule wrote:
>     >>
>     >>     I don't really get the point of this function.  There is
>     AFAICT no
>     >>     function to produce this escaped format, and it's not a
>     recognized
>     >>     interchange format.  So under what circumstances would one
>     need to
>     >>     use this?
>     >>
>     >>
>     >> Some corporate data can be in CSV format with escaped unicode
>     >> characters. Without this function it is not possible to decode
>     these
>     >> files without external application.
>     >
>     > I would like some supporting documentation on this.  So far we only
>     > have one stackoverflow question, and then this implementation, and
>     > they are not even the same format.  My worry is that if there is not
>     > precise specification, then people are going to want to add
>     things in
>     > the future, and there will be no way to analyze such requests in a
>     > principled way.
>     >
>     >
>     >
>
>
>     Also, should this be an extension? I'm dubious about including such
>     marginal uses in the core code unless there's a really good case
>     for it.
>
>
>
[...]
> 3. there are new disadvantages of extensions in current DBaaS times.
> Until the extension is not directly accepted by a cloud provider, then
> the extension is not available for users. The acceptance of extensions
> is not too agile - so moving this code to extension doesn't solve this
> problem. Without DBaaS the implementation of this feature as the
> extensions can be good enough.
>
>

That argument can apply to any extension someone wants to use. If your
DBaaS provider doesn't support some extension you need to lobby them or
find another that does support it, rather than try to put it in core
code. Some extensions, such as untrusted PLs,  will naturally almost
never be supported by DBaaS providers because they are inherently
unsafe. That's not the case here.


cheers


andrew

On 12/02/20 05:37, Pavel Stehule wrote:
> 2. there can be optional parameter "prefix" with default "\". But with "\u"
> it can be compatible with Java or Python.

Java's unicode escape form is one of those early ones that lack
a six-digit form, and where any character outside of the basic multilingual
plane has to be represented by two four-digit escapes in a row, encoding
the two surrogates that would make up the character's representation
in UTF-16.

Obviously that's an existing form that's out there, so it's not a bad
thing to have some kind of support for it, but it's not a great
representation to encourage people to use.

Python, by contrast, has both \uxxxx and \Uxxxxxxxx where you would use
the latter to represent a non-BMP character directly. So the Java and
Python schemes should be considered distinct.

In Perl, there is a useful extension to regexp substitution where
you specify the replacement not as a string or even a string with &
and \1 \2 ... magic, but as essentially a lambda that is passed the
match and returns a computed replacement. That makes conversions of
the sort discussed here generally trivial to implement. Would it be
worth considering to add something of general utility like that, and
then there could be a small library of pure SQL functions (or a wiki
page or GitHub gist) covering a bunch of the two dozen representations
on that page linked above?

Regards,
-Chap

On 12/02/20 09:55, Chapman Flack wrote:
> In Perl, there is a useful extension to regexp substitution where
> you specify the replacement not as a string or even a string with &
> and \1 \2 ... magic, but as essentially a lambda that is passed the
> match and returns a computed replacement. That makes conversions of
> the sort discussed here generally trivial to implement.

Python, I should have added, allows that also. Java too, since release 9.

Regards,
-Chap

On Wed, Dec  2, 2020 at 07:30:39PM +0100, Pavel Stehule wrote:
> postgres=# select
>  'Arabic     : ' || unistr( '\0627\0644\0639\0631\0628\064A\0629' )      || '
>   Chinese    : ' || unistr( '\4E2D\6587' )                               || '
>   English    : ' || unistr( 'English' )                                  || '
>   French     : ' || unistr( 'Fran\00E7ais' )                             || '
>   German     : ' || unistr( 'Deutsch' )                                  || '
>   Greek      : ' || unistr( '\0395\03BB\03BB\03B7\03BD\03B9\03BA\03AC' ) || '
>   Hebrew     : ' || unistr( '\05E2\05D1\05E8\05D9\05EA' )                || '
>   Japanese   : ' || unistr( '\65E5\672C\8A9E' )                          || '
>   Korean     : ' || unistr( '\D55C\AD6D\C5B4' )                          || '
>   Portuguese : ' || unistr( 'Portugu\00EAs' )                            || '
>   Russian    : ' || unistr( '\0420\0443\0441\0441\043A\0438\0439' )      || '
>   Spanish    : ' || unistr( 'Espa\00F1ol' )                              || '
>   Thai       : ' || unistr( '\0E44\0E17\0E22' )
>   as unicode_test_string;
> ┌──────────────────────────┐
> │   unicode_test_string    │
> ╞══════════════════════════╡
> │ Arabic     : العربية    ↵│
> │   Chinese    : 中文     ↵│
> │   English    : English  ↵│
> │   French     : Français ↵│
> │   German     : Deutsch  ↵│
> │   Greek      : Ελληνικά ↵│
> │   Hebrew     : עברית    ↵│
> │   Japanese   : 日本語   ↵│
> │   Korean     : 한국어   ↵│
> │   Portuguese : Português↵│
> │   Russian    : Русский  ↵│
> │   Spanish    : Español  ↵│
> │   Thai       : ไทย       │
> └──────────────────────────┘

Offlist, this table output is super-cool!

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee

On 10.03.21 14:52, David Steele wrote:
>> I thought about it a little bit more, and  the prefix specification 
>> has not too much sense (more if we implement this functionality as 
>> function "unistr"). I removed the optional argument and renamed the 
>> function to "unistr". The functionality is the same. Now it supports 
>> Oracle convention, Java and Python (for Python UXXXXXXXX) and 
>> \+XXXXXX. These formats was already supported.The compatibility witth 
>> Oracle is nice.
> 
> Peter, it looks like Pavel has aligned this function with unistr() as 
> you suggested. Thoughts?

I haven't read through the patch in detail yet, but I support the 
proposed details of the functionality.

On 25.03.21 10:44, Peter Eisentraut wrote:
> 
> On 10.03.21 14:52, David Steele wrote:
>>> I thought about it a little bit more, and  the prefix specification 
>>> has not too much sense (more if we implement this functionality as 
>>> function "unistr"). I removed the optional argument and renamed the 
>>> function to "unistr". The functionality is the same. Now it supports 
>>> Oracle convention, Java and Python (for Python UXXXXXXXX) and 
>>> \+XXXXXX. These formats was already supported.The compatibility witth 
>>> Oracle is nice.
>>
>> Peter, it looks like Pavel has aligned this function with unistr() as 
>> you suggested. Thoughts?
> 
> I haven't read through the patch in detail yet, but I support the 
> proposed details of the functionality.

Committed.

I made two major changes:  I moved the tests from unicode.sql to 
strings.sql.  The first file is for tests that only work in UTF8 
encoding, which is not the case here.  Also, I wasn't comfortable with 
exposing little utility functions from the parser in an ad hoc way.  So 
I made local copies, which also allows us to make more 
locally-appropriate error messages.  I think there is some potential for 
refactoring here (see also src/common/hex.c), but that's perhaps better 
done separately and more comprehensively.