From 9b19c12e1d930a237817dd432100388990ec082a Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 25 Mar 2011 18:21:25 -0400 Subject: [PATCH] Document collation handling in SQL and plpgsql functions. This is pretty minimal but covers the bare facts. --- doc/src/sgml/charset.sgml | 2 ++ doc/src/sgml/plpgsql.sgml | 75 +++++++++++++++++++++++++++++++++++++++ doc/src/sgml/xfunc.sgml | 55 ++++++++++++++++++++++++++++ 3 files changed, 132 insertions(+) diff --git a/doc/src/sgml/charset.sgml b/doc/src/sgml/charset.sgml index 66f02c619e..72ba333790 100644 --- a/doc/src/sgml/charset.sgml +++ b/doc/src/sgml/charset.sgml @@ -316,6 +316,8 @@ initdb --locale=sv_SE Collation Support + collation + The collation feature allows specifying the sort order and certain other locale aspects of data per-column, or even per-operation. diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 8da093b738..3e0d2d15b2 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -706,6 +706,81 @@ SELECT merge_fields(t.*) FROM table1 t WHERE ... ; structure on-the-fly. + + + Collation of <application>PL/pgSQL</application> Variables + + + collation + in PL/pgSQL + + + + When a PL/pgSQL function has one or more + parameters of collatable data types, a collation is identified for each + function call depending on the collations assigned to the actual + arguments, as described in . If a collation is + successfully identified (i.e., there are no conflicts of implicit + collations among the arguments) then all the collatable parameters are + treated as having that collation implicitly. This will affect the + behavior of collation-sensitive operations within the function. + For example, consider + + +CREATE FUNCTION less_than(a text, b text) RETURNS boolean AS $$ +BEGIN + RETURN a < b; +END; +$$ LANGUAGE plpgsql; + +SELECT less_than(text_field_1, text_field_2) FROM table1; +SELECT less_than(text_field_1, text_field_2 COLLATE "C") FROM table1; + + + The first use of less_than will use the common collation + of text_field_1 and text_field_2 for + the comparison, while the second use will use C collation. + + + + Furthermore, the identified collation is also assumed as the collation of + any local variables that are of collatable types. Thus this function + would not work any differently if it were written as + + +CREATE FUNCTION less_than(a text, b text) RETURNS boolean AS $$ +DECLARE + local_a text := a; + local_b text := b; +BEGIN + RETURN local_a < local_b; +END; +$$ LANGUAGE plpgsql; + + + + + If there are no parameters of collatable data types, or no common + collation can be identified for them, then parameters and local variables + use the default collation of their data type (which is usually the + database's default collation, but could be different for variables of + domain types). + + + + Explicit COLLATE clauses can be written inside a function + if it is desired to force a particular collation to be used regardless + of what the function is called with. For example, + + +CREATE FUNCTION less_than_c(a text, b text) RETURNS boolean AS $$ +BEGIN + RETURN a < b COLLATE "C"; +END; +$$ LANGUAGE plpgsql; + + + diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml index c8d8999659..58b83bbf12 100644 --- a/doc/src/sgml/xfunc.sgml +++ b/doc/src/sgml/xfunc.sgml @@ -1100,6 +1100,61 @@ SELECT concat_values('|', 1, 4, 2); + + + <acronym>SQL</acronym> Functions with Collations + + + collation + in SQL functions + + + + When a SQL function has one or more parameters of collatable data types, + a collation is identified for each function call depending on the + collations assigned to the actual arguments, as described in . If a collation is successfully identified + (i.e., there are no conflicts of implicit collations among the arguments) + then all the collatable parameters are treated as having that collation + implicitly. This will affect the behavior of collation-sensitive + operations within the function. For example, using the + anyleast function described above, the result of + +SELECT anyleast('abc'::text, 'ABC'); + + will depend on the database's default collation. In C locale + the result will be ABC, but in many other locales it will + be abc. The collation to use can be forced by adding + a COLLATE clause to any of the arguments, for example + +SELECT anyleast('abc'::text, 'ABC' COLLATE "C"); + + Alternatively, if you wish a function to operate with a particular + collation regardless of what it is called with, insert + COLLATE clauses as needed in the function definition. + This version of anyleast would always use en_US + locale to compare strings: + +CREATE FUNCTION anyleast (VARIADIC anyarray) RETURNS anyelement AS $$ + SELECT min($1[i] COLLATE "en_US") FROM generate_subscripts($1, 1) g(i); +$$ LANGUAGE SQL; + + But note that this will throw an error if applied to a non-collatable + data type. + + + + If no common collation can be identified among the actual arguments, + then a SQL function treats its parameters as having their data types' + default collation (which is usually the database's default collation, + but could be different for parameters of domain types). + + + + The behavior of collatable parameters can be thought of as a limited + form of polymorphism, applicable only to textual data types. + + -- 2.40.0