proposal: unescape_text function

po 22. 6. 2020 v 5:48 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

Hi

There is one user request for unescape function in core.

https://stackoverflow.com/questions/20124393/convert-escaped-unicode-character-back-to-actual-character-in-postgresql/20125412?noredirect=1#comment110502526_20125412

This request is about possibility that we do with string literal via
functional interface instead string literals only

I wrote plpgsql function, but built in function can be simpler:

CREATE OR REPLACE FUNCTION public.unescape(text, text)
RETURNS text
LANGUAGE plpgsql
AS $function$
DECLARE result text;
BEGIN
EXECUTE format('SELECT U&%s UESCAPE %s',
quote_literal(replace($1, '\u','^')),
quote_literal($2)) INTO result;
RETURN result;
END;
$function$

postgres=# select unescape('Odpov\u011Bdn\u00E1 osoba','^');
unescape -----------------
Odpovědná osoba(1 row)

What do you think about this?

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

Regards

Pavel

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

čt 2. 7. 2020 v 17:27 odesílatel Daniel Gustafsson <daniel@yesql.se> napsal:

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")));
+}

yes, it is my error

I am sending fixed patch

Thank you for check

Pavel

cheers ./daniel

On Thu, Jul 2, 2020 at 10:10 PM Pavel Stehule <pavel.stehule@gmail.com>
wrote:

čt 2. 7. 2020 v 17:27 odesílatel Daniel Gustafsson <daniel@yesql.se>
napsal:

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")));
+}

yes, it is my error

I am sending fixed patch

Thank you for check

Pavel

cheers ./daniel

Hi Pavel,

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?

--
Asif Rehman
Highgo Software (Canada/China/Pakistan)
URL : www.highgo.ca

Hi

Hi Pavel,

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

Regards

Pavel

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

st 29. 7. 2020 v 8:18 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

Hi

Hi Pavel,

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

Regards

rebase

Regards

Pavel

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?

pá 27. 11. 2020 v 15:37 odesílatel Peter Eisentraut <
peter.eisentraut@enterprisedb.com> napsal:

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?

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.

Postgres has support for this conversion, but only for string literals.

CREATE OR REPLACE FUNCTION public.unescape(text, text)
RETURNS text
LANGUAGE plpgsql
AS $function$
DECLARE result text;
BEGIN
EXECUTE format('SELECT U&%s UESCAPE %s',
quote_literal(replace($1, '\u','^')),
quote_literal($2)) INTO result;
RETURN result;
END;
$function$

Because unicode is major encoding, I think this conversion should be
supported. There is another question about implementation like in this
patch implemented unicode_unescape function, or with some new conversion.
Using conversion
https://www.postgresql.org/docs/current/sql-createconversion.html is
probably better, but I am not sure how intuitive it is, and it is hard to
use it (without not nice workarounds) in plpgsql.

I don't expect so Postgres should produce data in unicode escaped format,
but can be useful, if Postgres can do some work with data in special format
of major encoding.

postgres=# select convert_from(E'Odpov\u011Bdn\u00E1 osoba', 'UTF8');
┌─────────────────┐
│ convert_from │
╞═════════════════╡
│ Odpovědná osoba │
└─────────────────┘
(1 row)

I can do this with bytea, but it is hard to use it with text fields.

I didn't find any way how to do it without ugly steps.

Regards

Pavel

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 14:14 odesílatel Peter Eisentraut <
peter.eisentraut@enterprisedb.com> napsal:

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.

I checked this and it is "prefix backslash-u hex" used by Java, JavaScript
or RTF - https://billposer.org/Software/ListOfRepresentations.html

In some languages (Python), there is decoder "unicode-escape". Java has a
method escapeJava, for conversion from unicode to ascii. I can imagine so
these data are from Java systems exported to 8bit strings - so this
implementation can be accepted as referential. This format is used by
https://docs.oracle.com/javase/8/docs/technotes/tools/unix/native2ascii.html
tool too.

Postgres can decode this format too, and the patch is based on Postgres
implementation. I just implemented a different interface.

Currently decode function does only text->bytea transformation. Maybe a
more generic function "decode_text" and "encode_text" for similar cases can
be better (here we need text->text transformation). But it looks like
overengineering now.

Maybe we introduce new encoding "ascii" and we can implement new
conversions "ascii_to_utf8" and "utf8_to_ascii". It looks like the most
clean solution. What do you think about it?

Regards

Pavel

po 30. 11. 2020 v 22:15 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

po 30. 11. 2020 v 14:14 odesílatel Peter Eisentraut <
peter.eisentraut@enterprisedb.com> napsal:

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.

I checked this and it is "prefix backslash-u hex" used by Java,
JavaScript or RTF -
https://billposer.org/Software/ListOfRepresentations.html

In some languages (Python), there is decoder "unicode-escape". Java has a
method escapeJava, for conversion from unicode to ascii. I can imagine so
these data are from Java systems exported to 8bit strings - so this
implementation can be accepted as referential. This format is used by
https://docs.oracle.com/javase/8/docs/technotes/tools/unix/native2ascii.html
tool too.

Postgres can decode this format too, and the patch is based on Postgres
implementation. I just implemented a different interface.

Currently decode function does only text->bytea transformation. Maybe a
more generic function "decode_text" and "encode_text" for similar cases can
be better (here we need text->text transformation). But it looks like
overengineering now.

Maybe we introduce new encoding "ascii" and we can implement new
conversions "ascii_to_utf8" and "utf8_to_ascii". It looks like the most
clean solution. What do you think about it?

a better name of new encoding can be "unicode-escape" than "ascii". We use
"to_ascii" function for different use case.

set client_encoding to unicode-escape;
copy tab from xxx;
...

but it doesn't help when only a few columns from the table are in
unicode-escape format.

po 30. 11. 2020 v 22:56 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

po 30. 11. 2020 v 22:15 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

po 30. 11. 2020 v 14:14 odesílatel Peter Eisentraut <
peter.eisentraut@enterprisedb.com> napsal:

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.

I checked this and it is "prefix backslash-u hex" used by Java,
JavaScript or RTF -
https://billposer.org/Software/ListOfRepresentations.html

In some languages (Python), there is decoder "unicode-escape". Java has
a method escapeJava, for conversion from unicode to ascii. I can imagine so
these data are from Java systems exported to 8bit strings - so this
implementation can be accepted as referential. This format is used by
https://docs.oracle.com/javase/8/docs/technotes/tools/unix/native2ascii.html
tool too.

Postgres can decode this format too, and the patch is based on Postgres
implementation. I just implemented a different interface.

Currently decode function does only text->bytea transformation. Maybe a
more generic function "decode_text" and "encode_text" for similar cases can
be better (here we need text->text transformation). But it looks like
overengineering now.

Maybe we introduce new encoding "ascii" and we can implement new
conversions "ascii_to_utf8" and "utf8_to_ascii". It looks like the most
clean solution. What do you think about it?

a better name of new encoding can be "unicode-escape" than "ascii". We use
"to_ascii" function for different use case.

set client_encoding to unicode-escape;
copy tab from xxx;
...

but it doesn't help when only a few columns from the table are in
unicode-escape format.

probably the most complete solution can be from two steps:

1. introducing new encoding - "ascii_unicode_escape" with related
conversions
2. introducing two new functions - text_escape and text_unescape with two
parameters - source text and conversion name

select text_convert_to('Тимати', 'ascii_unicode_escape')
\u0422\u0438\u043c\u0430\u0442\u0438 .. result is text

select text_convert_from('\u0422\u0438\u043c\u0430\u0442\u0438',
'ascii_unicode_escape')
┌──────────┐
│ ?column? │
╞══════════╡
│ Тимати │
└──────────┘
(1 row)

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

út 1. 12. 2020 v 20:20 odesílatel Chapman Flack <chap@anastigmatix.net>
napsal:

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?

the detection of decimal or hexadecimal codes can be a hard problem -
string "12" is valid in both systems, but the numbers are different. So
there should be external specification as an argument.

Regards

Pavel

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

st 2. 12. 2020 v 0:05 odesílatel Andrew Dunstan <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.

I am not sure, and I am inclined so it should be core functionality.

1. Although this use case is marginal, this is related to most used
encodings - ascii and unicode. 8 bit encodings enhanced about escaped
multibyte chars will be used for a very long time. Unfortunately - this
will be worse, because Postgres will be used more in the corporate
environment, where there is a bigger press to conserve very legacy
technologies without correct multibyte support. The core problem so this
issue is out of concept bytea -> text or text -> bytea transformations
supported by Postgres. This is text -> text transformation (for almost all
encoding based on ascii), that is not supported by Postgres now.

2. Postgres already has this functionality - but unfortunately there is a
limit just only literal constants.

create or replace function uunescape(text)
returns text as $$
declare r text;
begin
-- don't use this code!!!
execute 'select e''' || $1 || '''' into r;
return r;
end;
$$ language plpgsql immutable;

But one way how anybody can use it is SQL injection vulnerable and slow. So
some simple buildin solution can be protection against some future security
issues. Personally I am happy with just this limited function that will be
safe (although the design based on introducing new encoding and conversions
can be more complete and accurate). I agree so this case is marginal, but
it is a fully valid use case, and supporting unicode escaped codes just by
parser is a needless limit.

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.

Regards

Pavel

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.

st 2. 12. 2020 v 9:23 odesílatel Peter Eisentraut <
peter.eisentraut@enterprisedb.com> napsal:

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.

unistr is a primitive form of proposed function. But it can be used as a
base. The format is compatible with our "4.1.2.3. String Constants with
Unicode Escapes".

What do you think about the following proposal?

1. unistr(text) .. compatible with Postgres unicode escapes - it is
enhanced against Oracle, because Oracle's unistr doesn't support 6 digits
unicodes.

2. there can be optional parameter "prefix" with default "\". But with "\u"
it can be compatible with Java or Python.

What do you think about it?

Pavel