Skip to content

Commit c947325

Browse files
tglsfdctglsfdc
committed
Support a COLLATE clause in plpgsql variable declarations.
This allows the usual rules for assigning a collation to a local variable to be overridden. Per discussion, it seems appropriate to support this rather than forcing all local variables to have the argument-derived collation.
1 parent 88dc6fa commit c947325

File tree

5 files changed

+136
-12
lines changed

5 files changed

+136
-12
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 26 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -328,15 +328,17 @@ arow RECORD;
328328
<para>
329329
The general syntax of a variable declaration is:
330330
<synopsis>
331-
<replaceable>name</replaceable> <optional> CONSTANT </optional> <replaceable>type</replaceable> <optional> NOT NULL </optional> <optional> { DEFAULT | := } <replaceable>expression</replaceable> </optional>;
331+
<replaceable>name</replaceable> <optional> CONSTANT </optional> <replaceable>type</replaceable> <optional> COLLATE <replaceable>collation_name</replaceable> </optional> <optional> NOT NULL </optional> <optional> { DEFAULT | := } <replaceable>expression</replaceable> </optional>;
332332
</synopsis>
333333
The <literal>DEFAULT</> clause, if given, specifies the initial value assigned
334334
to the variable when the block is entered. If the <literal>DEFAULT</> clause
335335
is not given then the variable is initialized to the
336336
<acronym>SQL</acronym> null value.
337337
The <literal>CONSTANT</> option prevents the variable from being
338-
assigned to, so that its value will remain constant for the duration of
339-
the block.
338+
assigned to after initialization, so that its value will remain constant
339+
for the duration of the block.
340+
The <literal>COLLATE</> option specifies a collation to use for the
341+
variable (see <xref linkend="plpgsql-declaration-collation">).
340342
If <literal>NOT NULL</>
341343
is specified, an assignment of a null value results in a run-time
342344
error. All variables declared as <literal>NOT NULL</>
@@ -768,9 +770,23 @@ $$ LANGUAGE plpgsql;
768770
</para>
769771

770772
<para>
771-
Explicit <literal>COLLATE</> clauses can be written inside a function
772-
if it is desired to force a particular collation to be used regardless
773-
of what the function is called with. For example,
773+
A local variable of a collatable data type can have a different collation
774+
associated with it by including the <literal>COLLATE</> option in its
775+
declaration, for example
776+
777+
<programlisting>
778+
DECLARE
779+
local_a text COLLATE "en_US";
780+
</programlisting>
781+
782+
This option overrides the collation that would otherwise be
783+
given to the variable according to the rules above.
784+
</para>
785+
786+
<para>
787+
Also, of course explicit <literal>COLLATE</> clauses can be written inside
788+
a function if it is desired to force a particular collation to be used in
789+
a particular operation. For example,
774790

775791
<programlisting>
776792
CREATE FUNCTION less_than_c(a text, b text) RETURNS boolean AS $$
@@ -779,6 +795,10 @@ BEGIN
779795
END;
780796
$$ LANGUAGE plpgsql;
781797
</programlisting>
798+
799+
This overrides the collations associated with the table columns,
800+
parameters, or local variables used in the expression, just as would
801+
happen in a plain SQL command.
782802
</para>
783803
</sect2>
784804
</sect1>

‎src/pl/plpgsql/src/gram.y

Lines changed: 41 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -21,6 +21,7 @@
2121
#include "parser/parse_type.h"
2222
#include "parser/scanner.h"
2323
#include "parser/scansup.h"
24+
#include "utils/builtins.h"
2425

2526

2627
/* Location tracking support --- simpler than bison's default */
@@ -122,6 +123,7 @@ static List *read_raise_options(void);
122123
PLcword cword;
123124
PLwdatum wdatum;
124125
bool boolean;
126+
Oid oid;
125127
struct
126128
{
127129
char *name;
@@ -167,6 +169,7 @@ static List *read_raise_options(void);
167169
%type <boolean> decl_const decl_notnull exit_type
168170
%type <expr> decl_defval decl_cursor_query
169171
%type <dtype> decl_datatype
172+
%type <oid> decl_collate
170173
%type <datum> decl_cursor_args
171174
%type <list> decl_cursor_arglist
172175
%type <nsitem> decl_aliasitem
@@ -245,6 +248,7 @@ static List *read_raise_options(void);
245248
%token <keyword> K_BY
246249
%token <keyword> K_CASE
247250
%token <keyword> K_CLOSE
251+
%token <keyword> K_COLLATE
248252
%token <keyword> K_CONSTANT
249253
%token <keyword> K_CONTINUE
250254
%token <keyword> K_CURSOR
@@ -428,10 +432,27 @@ decl_stmt : decl_statement
428432
}
429433
;
430434

431-
decl_statement : decl_varname decl_const decl_datatype decl_notnull decl_defval
435+
decl_statement : decl_varname decl_const decl_datatype decl_collate decl_notnull decl_defval
432436
{
433437
PLpgSQL_variable *var;
434438

439+
/*
440+
* If a collation is supplied, insert it into the
441+
* datatype. We assume decl_datatype always returns
442+
* a freshly built struct not shared with other
443+
* variables.
444+
*/
445+
if (OidIsValid($4))
446+
{
447+
if (!OidIsValid($3->collation))
448+
ereport(ERROR,
449+
(errcode(ERRCODE_DATATYPE_MISMATCH),
450+
errmsg("collations are not supported by type %s",
451+
format_type_be($3->typoid)),
452+
parser_errposition(@4)));
453+
$3->collation = $4;
454+
}
455+
435456
var = plpgsql_build_variable($1.name, $1.lineno,
436457
$3, true);
437458
if ($2)
@@ -444,21 +465,21 @@ decl_statement : decl_varname decl_const decl_datatype decl_notnull decl_defval
444465
errmsg("row or record variable cannot be CONSTANT"),
445466
parser_errposition(@2)));
446467
}
447-
if ($4)
468+
if ($5)
448469
{
449470
if (var->dtype == PLPGSQL_DTYPE_VAR)
450-
((PLpgSQL_var *) var)->notnull = $4;
471+
((PLpgSQL_var *) var)->notnull = $5;
451472
else
452473
ereport(ERROR,
453474
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
454475
errmsg("row or record variable cannot be NOT NULL"),
455476
parser_errposition(@4)));
456477

457478
}
458-
if ($5 != NULL)
479+
if ($6 != NULL)
459480
{
460481
if (var->dtype == PLPGSQL_DTYPE_VAR)
461-
((PLpgSQL_var *) var)->default_val = $5;
482+
((PLpgSQL_var *) var)->default_val = $6;
462483
else
463484
ereport(ERROR,
464485
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -685,6 +706,19 @@ decl_datatype :
685706
}
686707
;
687708

709+
decl_collate :
710+
{ $$ = InvalidOid; }
711+
| K_COLLATE T_WORD
712+
{
713+
$$ = get_collation_oid(list_make1(makeString($2.ident)),
714+
false);
715+
}
716+
| K_COLLATE T_CWORD
717+
{
718+
$$ = get_collation_oid($2.idents, false);
719+
}
720+
;
721+
688722
decl_notnull :
689723
{ $$ = false; }
690724
| K_NOT K_NULL
@@ -2432,7 +2466,8 @@ read_datatype(int tok)
24322466
yyerror("incomplete data type declaration");
24332467
}
24342468
/* Possible followers for datatype in a declaration */
2435-
if (tok == K_NOT || tok == '=' || tok == COLON_EQUALS || tok == K_DEFAULT)
2469+
if (tok == K_COLLATE || tok == K_NOT ||
2470+
tok == '=' || tok == COLON_EQUALS || tok == K_DEFAULT)
24362471
break;
24372472
/* Possible followers for datatype in a cursor_arg list */
24382473
if ((tok == ',' || tok == ')') && parenlevel == 0)

‎src/pl/plpgsql/src/pl_scanner.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -64,6 +64,7 @@ static const ScanKeyword reserved_keywords[] = {
6464
PG_KEYWORD("by", K_BY, RESERVED_KEYWORD)
6565
PG_KEYWORD("case", K_CASE, RESERVED_KEYWORD)
6666
PG_KEYWORD("close", K_CLOSE, RESERVED_KEYWORD)
67+
PG_KEYWORD("collate", K_COLLATE, RESERVED_KEYWORD)
6768
PG_KEYWORD("continue", K_CONTINUE, RESERVED_KEYWORD)
6869
PG_KEYWORD("declare", K_DECLARE, RESERVED_KEYWORD)
6970
PG_KEYWORD("default", K_DEFAULT, RESERVED_KEYWORD)

‎src/test/regress/expected/collate.linux.utf8.out

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -825,6 +825,46 @@ ORDER BY a.b, b.b;
825825
bbc | bbc | f | f | f | f
826826
(16 rows)
827827

828+
-- collation override in plpgsql
829+
CREATE FUNCTION mylt2 (x text, y text) RETURNS boolean LANGUAGE plpgsql AS $$
830+
declare
831+
xx text := x;
832+
yy text := y;
833+
begin
834+
return xx < yy;
835+
end
836+
$$;
837+
SELECT mylt2('a', 'B' collate "en_US") as t, mylt2('a', 'B' collate "C") as f;
838+
t | f
839+
---+---
840+
t | f
841+
(1 row)
842+
843+
CREATE OR REPLACE FUNCTION
844+
mylt2 (x text, y text) RETURNS boolean LANGUAGE plpgsql AS $$
845+
declare
846+
xx text COLLATE "POSIX" := x;
847+
yy text := y;
848+
begin
849+
return xx < yy;
850+
end
851+
$$;
852+
SELECT mylt2('a', 'B') as f;
853+
f
854+
---
855+
f
856+
(1 row)
857+
858+
SELECT mylt2('a', 'B' collate "C") as fail; -- conflicting collations
859+
ERROR: could not determine which collation to use for string comparison
860+
HINT: Use the COLLATE clause to set the collation explicitly.
861+
CONTEXT: PL/pgSQL function "mylt2" line 6 at RETURN
862+
SELECT mylt2('a', 'B' collate "POSIX") as f;
863+
f
864+
---
865+
f
866+
(1 row)
867+
828868
-- polymorphism
829869
SELECT * FROM unnest((SELECT array_agg(b ORDER BY b) FROM collate_test1)) ORDER BY 1;
830870
unnest

‎src/test/regress/sql/collate.linux.utf8.sql

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -256,6 +256,34 @@ FROM collate_test1 a, collate_test1 b
256256
ORDER BY a.b, b.b;
257257

258258

259+
-- collation override in plpgsql
260+
261+
CREATE FUNCTION mylt2 (x text, y text) RETURNS boolean LANGUAGE plpgsql AS $$
262+
declare
263+
xx text := x;
264+
yy text := y;
265+
begin
266+
return xx < yy;
267+
end
268+
$$;
269+
270+
SELECT mylt2('a', 'B' collate "en_US") as t, mylt2('a', 'B' collate "C") as f;
271+
272+
CREATE OR REPLACE FUNCTION
273+
mylt2 (x text, y text) RETURNS boolean LANGUAGE plpgsql AS $$
274+
declare
275+
xx text COLLATE "POSIX" := x;
276+
yy text := y;
277+
begin
278+
return xx < yy;
279+
end
280+
$$;
281+
282+
SELECT mylt2('a', 'B') as f;
283+
SELECT mylt2('a', 'B' collate "C") as fail; -- conflicting collations
284+
SELECT mylt2('a', 'B' collate "POSIX") as f;
285+
286+
259287
-- polymorphism
260288

261289
SELECT * FROM unnest((SELECT array_agg(b ORDER BY b) FROM collate_test1)) ORDER BY 1;

0 commit comments

Comments
 (0)