Thread: Casting a collation in an ORDER BY ... COLLATE

Casting a collation in an ORDER BY ... COLLATE

From
Kip Cole
Date:
I’m trying to cast a string value to a collation name without success. My suspicion is this isn’t possible
based upon the error message. But perhaps wiser heads than mine have a suggestion on how to do
this?

cldr_sql=# select * from models order by name collate 'en-x-icu'::regcollation;
ERROR: syntax error at or near "'en-x-icu'"
LINE 1: select * from models order by name collate 'en-x-icu'::regco…

Utilmately the objective is to interpolate the collation value into a prepared query so
this is just the first step to validate that casting a COLLATE name  is possible or not.

Suggestions and ideas most welcome.






Re: Casting a collation in an ORDER BY ... COLLATE

From
"David G. Johnston"
Date:
On Sat, May 21, 2022 at 4:38 PM Kip Cole <kipcole9@gmail.com> wrote:
I’m trying to cast a string value to a collation name without success. My suspicion is this isn’t possible
based upon the error message.

You cannot cast between a token that interpreted as a literal and one that is interpreted as a name.  For example:  SELECT * FROM 'table_name'::???; you need to supply an identifier in the FROM clause, not a literal.

Identifiers cannot be parameterized, so if you want to interpolate you need to do so while building a dynamic query string.  See the "format()" function and "EXECUTE" command for ways to do this within the server.  Or use whatever client-side facilities you have at your disposal.

David J.

Re: Casting a collation in an ORDER BY ... COLLATE

From
Tom Lane
Date:
Kip Cole <kipcole9@gmail.com> writes:
> cldr_sql=# select * from models order by name collate 'en-x-icu'::regcollation;
> ERROR: syntax error at or near "'en-x-icu'"
> LINE 1: select * from models order by name collate 'en-x-icu'::regco…

You've got the syntax off a bit.  The argument of COLLATE is an
identifier, not a string literal (or expression), so you should write

select * from models order by name collate "en-x-icu";

The double quotes are needed because most collation names don't
follow SQL identifier rules.

> Utilmately the objective is to interpolate the collation value into a prepared query so
> this is just the first step to validate that casting a COLLATE name  is possible or not.

If you mean that you want to inject a run-time-variable collation
name, you can't, any more than you can inject (say) a run-time-variable
table name.  You'd have to construct and execute a dynamic SQL string.

            regards, tom lane