How can I pass COLLATE in a request?

[sxynwh]

e.g. select * from people order by name **collate 'zh-x-icu'**
How can I pass collate 'zh-x-icu' in the request (GET /people?order=name)?

[steve-chavez]

Could you change your collation at the column level?

ALTER TABLE people ALTER COLUMN name SET DATA TYPE text COLLATE "zh-x-icu"

select * from people order by name collate 'zh-x-icu'

The problem with supporting collation expressions is that they invalidate indexes(see indexes & collations) - they would open a door for forcing inefficient queries at the API level.

[sxynwh]

Got you. Thanks.
How to set the data type for a return value of a function?
Actually I want to sort the values which are computed values.

Here is an example:

CREATE OR REPLACE FUNCTION test.format_message(name text, hobby text)
RETURNS TEXT AS $$

......
......
RETURN new_string;
$$
STABLE LANGUAGE 'plpgsql';

Then I create a view with a computed column.

CREATE VIEW "test.student_hobby" AS
  SELECT student.id,
  (SELECT "test". "format_message"(student.name, student.hobby)) AS description FROM test.people student;

I query the results via the request ( GET /student_hobby?order=description), but the order is wrong. I try to run the command showed as below. It works.

  SELECT student.id,
  (SELECT "test". "format_message"(student.name, student.hobby)) AS description FROM test.people student order by test.format_message(student.name, student.hobby) collate 'zh-x-icu';

Seems we can not pass collate through the endpoint. Do you know how can I set the collate for a computed column? Thanks.

[steve-chavez]

Hm, why don't you include the collate in the VIEW definition?

[wolfgangwalther]

@sxynwh Did that work for you?

[sxynwh]

I tried to add collate for a column in a view. It works. But I have no idea about how to add a collation for a function. Do you have any suggestions?

[wolfgangwalther]

Does something like this not work?

CREATE VIEW "test.student_hobby" AS
  SELECT
    student.id,
    "test". "format_message"(student.name, student.hobby) COLLATE "..." AS description
  FROM test.people student;