Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitc947325

Browse files
committed
Support a COLLATE clause in plpgsql variable declarations.
This allows the usual rules for assigning a collation to a local variableto be overridden. Per discussion, it seems appropriate to support thisrather than forcing all local variables to have the argument-derivedcollation.
1 parent88dc6fa commitc947325

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 @@ staticList*read_raise_options(void);
122123
PLcwordcword;
123124
PLwdatumwdatum;
124125
boolboolean;
126+
Oidoid;
125127
struct
126128
{
127129
char *name;
@@ -167,6 +169,7 @@ staticList*read_raise_options(void);
167169
%type<boolean>decl_constdecl_notnullexit_type
168170
%type<expr>decl_defvaldecl_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 @@ staticList*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_varnamedecl_constdecl_datatypedecl_notnulldecl_defval
435+
decl_statement:decl_varnamedecl_constdecl_datatypedecl_collatedecl_notnulldecl_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 BYa.b,b.b;
257257

258258

259+
-- collation override in plpgsql
260+
261+
CREATEFUNCTIONmylt2 (xtext, ytext) RETURNSboolean LANGUAGE plpgsqlAS $$
262+
declare
263+
xxtext := x;
264+
yytext := 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+
CREATEOR REPLACE FUNCTION
273+
mylt2 (xtext, ytext) RETURNSboolean LANGUAGE plpgsqlAS $$
274+
declare
275+
xxtext COLLATE"POSIX" := x;
276+
yytext := 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(bORDER BY b)FROM collate_test1))ORDER BY1;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp