1

How can we pass the collation to a user-defined function?

e.g. I can use a hardcoded collation (e.g. "de-AT-x-icu") like this:

CREATE OR REPLACE FUNCTION test(agg_1 jsonb, agg_2 jsonb) RETURNS jsonb
    LANGUAGE SQL
AS
$$
SELECT  jsonb_build_object(
  'count', (agg_1 -> 'count')::int8 + (agg_2 -> 'count')::int8
, 'max', greatest(agg_1 ->> 'max' collate "de-AT-x-icu", agg_2 ->> 'max' collate "de-AT-x-icu")
)
$$;

and the test works:

select test(
  jsonb_build_object('count', 2 , 'max', 'H'),
  jsonb_build_object('count', 0, 'max', 'i')
);

We tried to use a text-argument,

CREATE OR REPLACE FUNCTION test2(agg_1 jsonb, agg_2 jsonb, text_collation text) RETURNS jsonb
    LANGUAGE SQL
AS
$$
SELECT  jsonb_build_object(
                'count', (agg_1 -> 'count')::int8 + (agg_2 -> 'count')::int8
            , 'max', greatest(agg_1 ->> 'max' collate text_collation, agg_2 ->> 'max' collate text_collation)
            )
$$;

but this fails with:

ERROR: collation "text_collation" for encoding "UTF8" does not exist

What is the best way to pass/use a collation in a UDF in such a case?

1

Collations are database objects, so their name is an identifier.

You cannot use a parameter for an identifier, you'd have to use dynamic SQL in a PL/pgSQL function:

RETURN QUERY EXECUTE
   format(
      'SELECT ... COLLATE %I ...'
      text_collation
   );
| improve this answer | |
  • I guess it could have bad performance to dynamically create a full query for every function call - so maybe it's better to hardcode specific versions of the function, when we only need 2 or 3 diffferent locales. Any thoughts? – TmTron Nov 6 '19 at 17:30
  • 1
    I fully agree. If you have very many very small statements, the cost of planning them every time can be forbidding, and using a CASE statement in PL/pgSQL that chooses between several hard-coded statements would be much better. – Laurenz Albe Nov 7 '19 at 6:47
0

thanks to the answer of @Laurenz Albe, I came up with a working version:

CREATE OR REPLACE FUNCTION test2(agg_1 jsonb, agg_2 jsonb, text_collation text) RETURNS jsonb
LANGUAGE plpgsql
AS
$$
DECLARE
    res jsonb;
BEGIN
    EXECUTE format('SELECT'
        || ' jsonb_build_object('
        || '   ''count'', (''%1$s''::jsonb -> ''count'')::int8 + (''%2$s''::jsonb -> ''count'')::int8 '
        || ' , ''max'', greatest(''%1$s''::jsonb ->> ''max'' collate %3$I, ''%2$s''::jsonb ->> ''max'' collate %3$I) '
        || ' )'
        , agg_1, agg_2, text_collation) into res;
    return res;
END
$$;

Example calls:

select test2(jsonb_build_object('count', 2 , 'max', 'K')
           , jsonb_build_object('count', 1, 'max', 'i')
           , 'de-AT-x-icu'
           ) as agg;


select test2(jsonb_build_object('count', 2 , 'max', 'K')
           , jsonb_build_object('count', 1, 'max', 'i')
           , 'C'
           ) as agg;

However, keep in mind that dynamically creating the query for every function call will impact the performance.

As an alternative, we consider hardcoding some locales - or maybe even use only the und-x-icu locale (see 23.2.2.2.2. ICU collations in the postgres-docs):

und-x-icu (for “undefined”)
ICU “root” collation. Use this to get a reasonable language-agnostic sort order.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.