Dynamic collation support

2016-01-19 16:07 GMT+01:00 Merlin Moncure <mmoncure@gmail.com>:

For database functions, is there any way to manage collations besides
dynamic sql? It doesn't look like there is but I thought I'd ask.

For example, I want to do something like:

DO
$$
DECLARE
c name DEFAULT 'en_GB';
BEGIN
SELECT * FROM foo ORDER BY a COLLATE c;
END;
$$;

and not

DO
$$
DECLARE
c name DEFAULT 'en_GB';
BEGIN
EXECUTE 'SELECT * FROM foo ORDER BY a COLLATE ' || quote_ident(c);
END;
$$;

I understand this would disable all index supported sorting; that's ok.

Not being able to specify collation in a parameterized manner presents
some issues...I'm curious if there are workarounds.

Different collates requires different plans - so using dynamic SQL is much
more correct.

It is same like using variables as columns or tablenames.

Regards

Pavel

On Tue, Jan 19, 2016 at 9:15 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2016-01-19 16:07 GMT+01:00 Merlin Moncure <mmoncure@gmail.com>:

For database functions, is there any way to manage collations besides
dynamic sql? It doesn't look like there is but I thought I'd ask.

For example, I want to do something like:

DO
$$
DECLARE
c name DEFAULT 'en_GB';
BEGIN
SELECT * FROM foo ORDER BY a COLLATE c;
END;
$$;

and not

DO
$$
DECLARE
c name DEFAULT 'en_GB';
BEGIN
EXECUTE 'SELECT * FROM foo ORDER BY a COLLATE ' || quote_ident(c);
END;
$$;

I understand this would disable all index supported sorting; that's ok.

Not being able to specify collation in a parameterized manner presents
some issues...I'm curious if there are workarounds.

Different collates requires different plans - so using dynamic SQL is much
more correct.

It is same like using variables as columns or tablenames.

Right -- I get it, and I understand the planner issues. But the
amount of revision that goes into a database that internationalizes
can be pretty large. To do it right, any static sql that involves
string ordering can't be used. pl/sql also can't be used. ISTM this
is impolite to certain coding styles.

merlin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Jan 19, 2016 at 9:42 AM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Tue, Jan 19, 2016 at 9:15 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Different collates requires different plans - so using dynamic SQL is much
more correct.

It is same like using variables as columns or tablenames.

Right -- I get it, and I understand the planner issues. But the
amount of revision that goes into a database that internationalizes
can be pretty large. To do it right, any static sql that involves
string ordering can't be used. pl/sql also can't be used. ISTM this
is impolite to certain coding styles.

Hm, an extension wrapper to strxrm() might do the trick when
flexibility is favored over performance. You'd have to constantly
mange the locale to make it work though.

merlin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Merlin Moncure <mmoncure@gmail.com> writes:

On Tue, Jan 19, 2016 at 9:15 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Different collates requires different plans - so using dynamic SQL is much
more correct.
It is same like using variables as columns or tablenames.

Right -- I get it, and I understand the planner issues. But the
amount of revision that goes into a database that internationalizes
can be pretty large. To do it right, any static sql that involves
string ordering can't be used. pl/sql also can't be used. ISTM this
is impolite to certain coding styles.

Well, it's the way the SQL committee specified collations to work, so
we're pretty much stuck with that syntax.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Jan 19, 2016 at 11:11 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Merlin Moncure <mmoncure@gmail.com> writes:

On Tue, Jan 19, 2016 at 9:15 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Different collates requires different plans - so using dynamic SQL is much
more correct.
It is same like using variables as columns or tablenames.

Right -- I get it, and I understand the planner issues. But the
amount of revision that goes into a database that internationalizes
can be pretty large. To do it right, any static sql that involves
string ordering can't be used. pl/sql also can't be used. ISTM this
is impolite to certain coding styles.

Well, it's the way the SQL committee specified collations to work, so
we're pretty much stuck with that syntax.

I understand. It's water under the bridge if a strxfrm() wrapper
could deliver the goods here. Changing:

ORDER BY foo
to
ORDER BY strxfrm(foo, _CollationLocale)

is a nice escape route where _CollationLocale gets suddenly brought on
to the table. It's going to be awfully slow, but in many cases that's
acceptable. At least I think so -- I have to play with it.

merlin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2016-01-19 20:04 GMT+01:00 Merlin Moncure <mmoncure@gmail.com>:

On Tue, Jan 19, 2016 at 11:11 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Merlin Moncure <mmoncure@gmail.com> writes:

On Tue, Jan 19, 2016 at 9:15 AM, Pavel Stehule <pavel.stehule@gmail.com>

wrote:

Different collates requires different plans - so using dynamic SQL is

much

more correct.
It is same like using variables as columns or tablenames.

Right -- I get it, and I understand the planner issues. But the
amount of revision that goes into a database that internationalizes
can be pretty large. To do it right, any static sql that involves
string ordering can't be used. pl/sql also can't be used. ISTM this
is impolite to certain coding styles.

Well, it's the way the SQL committee specified collations to work, so
we're pretty much stuck with that syntax.

I understand. It's water under the bridge if a strxfrm() wrapper
could deliver the goods here. Changing:

ORDER BY foo
to
ORDER BY strxfrm(foo, _CollationLocale)

this mechanism was used more time in Czech multilanguage applications

Orafce.nlssort use it.

https://github.com/orafce/orafce/blob/master/others.c

Regards

Pavel

On Tue, Jan 19, 2016 at 1:15 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2016-01-19 20:04 GMT+01:00 Merlin Moncure <mmoncure@gmail.com>:

On Tue, Jan 19, 2016 at 11:11 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Merlin Moncure <mmoncure@gmail.com> writes:

On Tue, Jan 19, 2016 at 9:15 AM, Pavel Stehule
<pavel.stehule@gmail.com> wrote:

Different collates requires different plans - so using dynamic SQL is
much
more correct.
It is same like using variables as columns or tablenames.

Right -- I get it, and I understand the planner issues. But the
amount of revision that goes into a database that internationalizes
can be pretty large. To do it right, any static sql that involves
string ordering can't be used. pl/sql also can't be used. ISTM this
is impolite to certain coding styles.

Well, it's the way the SQL committee specified collations to work, so
we're pretty much stuck with that syntax.

I understand. It's water under the bridge if a strxfrm() wrapper
could deliver the goods here. Changing:

ORDER BY foo
to
ORDER BY strxfrm(foo, _CollationLocale)

this mechanism was used more time in Czech multilanguage applications

Orafce.nlssort use it.

https://github.com/orafce/orafce/blob/master/others.c

wow! that's perfect! -- thanks.

merlin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general