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

Commit6688d28

Browse files
committed
Add COLLATION FOR expression
reviewed by Jaime Casanova
1 parentd41f510 commit6688d28

File tree

9 files changed

+103
-3
lines changed

9 files changed

+103
-3
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -13698,6 +13698,10 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
1369813698
<primary>pg_typeof</primary>
1369913699
</indexterm>
1370013700

13701+
<indexterm>
13702+
<primary>collation for</primary>
13703+
</indexterm>
13704+
1370113705
<para>
1370213706
<xref linkend="functions-info-catalog-table"> lists functions that
1370313707
extract information from the system catalogs.
@@ -13859,6 +13863,11 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
1385913863
<entry><type>regtype</type></entry>
1386013864
<entry>get the data type of any value</entry>
1386113865
</row>
13866+
<row>
13867+
<entry><literal><function>collation for (<parameter>any</parameter>)</function></literal></entry>
13868+
<entry><type>text</type></entry>
13869+
<entry>get the collation of the argument</entry>
13870+
</row>
1386213871
</tbody>
1386313872
</tgroup>
1386413873
</table>
@@ -13983,6 +13992,27 @@ SELECT typlen FROM pg_type WHERE oid = pg_typeof(33);
1398313992
4
1398413993
(1 row)
1398513994
</programlisting>
13995+
</para>
13996+
13997+
<para>
13998+
The expression <literal>collation for</literal> returns the collation of the
13999+
value that is passed to it. Example:
14000+
<programlisting>
14001+
SELECT collation for (description) FROM pg_description LIMIT 1;
14002+
pg_collation_for
14003+
------------------
14004+
"default"
14005+
(1 row)
14006+
14007+
SELECT collation for ('foo' COLLATE "de_DE");
14008+
pg_collation_for
14009+
------------------
14010+
"de_DE"
14011+
(1 row)
14012+
</programlisting>
14013+
The value might be quoted and schema-qualified. If no collation is derived
14014+
for the argument expression, then a null value is returned. If the argument
14015+
is not of a collatable data type, then an error is raised.
1398614016
</para>
1398714017

1398814018
<indexterm>

‎src/backend/parser/gram.y

Lines changed: 14 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -10701,6 +10701,19 @@ func_expr:func_name '(' ')' over_clause
1070110701
n->location = @1;
1070210702
$$ = (Node *)n;
1070310703
}
10704+
| COLLATION FOR'(' a_expr')'
10705+
{
10706+
FuncCall *n =makeNode(FuncCall);
10707+
n->funcname =SystemFuncName("pg_collation_for");
10708+
n->args =list_make1($4);
10709+
n->agg_order = NIL;
10710+
n->agg_star =FALSE;
10711+
n->agg_distinct =FALSE;
10712+
n->func_variadic =FALSE;
10713+
n->over =NULL;
10714+
n->location = @1;
10715+
$$ = (Node *)n;
10716+
}
1070410717
| CURRENT_DATE
1070510718
{
1070610719
/*
@@ -12152,7 +12165,6 @@ unreserved_keyword:
1215212165
| CLASS
1215312166
| CLOSE
1215412167
| CLUSTER
12155-
| COLLATION
1215612168
| COMMENT
1215712169
| COMMENTS
1215812170
| COMMIT
@@ -12491,6 +12503,7 @@ reserved_keyword:
1249112503
| CAST
1249212504
| CHECK
1249312505
| COLLATE
12506+
| COLLATION
1249412507
| COLUMN
1249512508
| CONSTRAINT
1249612509
| CREATE

‎src/backend/utils/adt/misc.c

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -32,6 +32,7 @@
3232
#include"storage/pmsignal.h"
3333
#include"storage/proc.h"
3434
#include"storage/procarray.h"
35+
#include"utils/lsyscache.h"
3536
#include"tcop/tcopprot.h"
3637
#include"utils/builtins.h"
3738
#include"utils/timestamp.h"
@@ -492,3 +493,29 @@ pg_typeof(PG_FUNCTION_ARGS)
492493
{
493494
PG_RETURN_OID(get_fn_expr_argtype(fcinfo->flinfo,0));
494495
}
496+
497+
498+
/*
499+
* Implementation of the COLLATE FOR expression; returns the collation
500+
* of the argument.
501+
*/
502+
Datum
503+
pg_collation_for(PG_FUNCTION_ARGS)
504+
{
505+
Oidtypeid;
506+
Oidcollid;
507+
508+
typeid=get_fn_expr_argtype(fcinfo->flinfo,0);
509+
if (!typeid)
510+
PG_RETURN_NULL();
511+
if (!type_is_collatable(typeid)&&typeid!=UNKNOWNOID)
512+
ereport(ERROR,
513+
(errcode(ERRCODE_DATATYPE_MISMATCH),
514+
errmsg("collations are not supported by type %s",
515+
format_type_be(typeid))));
516+
517+
collid=PG_GET_COLLATION();
518+
if (!collid)
519+
PG_RETURN_NULL();
520+
PG_RETURN_TEXT_P(cstring_to_text(generate_collation_name(collid)));
521+
}

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO201203011
56+
#defineCATALOG_VERSION_NO201203021
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1953,6 +1953,8 @@ DESCR("convert generic options array to name/value table");
19531953

19541954
DATA(insertOID=1619 (pg_typeofPGNSPPGUID121000ffffffs102206"2276"_null__null__null__null_pg_typeof_null__null__null_ ));
19551955
DESCR("type of the argument");
1956+
DATA(insertOID=3162 (pg_collation_forPGNSPPGUID121000ffffffs1025"2276"_null__null__null__null_pg_collation_for_null__null__null_ ));
1957+
DESCR("collation of the argument; implementation of the COLLATION FOR expression");
19561958

19571959
/* Deferrable unique constraint trigger */
19581960
DATA(insertOID=1250 (unique_key_recheckPGNSPPGUID121000fffftfv002279""_null__null__null__null_unique_key_recheck_null__null__null_ ));

‎src/include/parser/kwlist.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -79,7 +79,7 @@ PG_KEYWORD("close", CLOSE, UNRESERVED_KEYWORD)
7979
PG_KEYWORD("cluster",CLUSTER,UNRESERVED_KEYWORD)
8080
PG_KEYWORD("coalesce",COALESCE,COL_NAME_KEYWORD)
8181
PG_KEYWORD("collate",COLLATE,RESERVED_KEYWORD)
82-
PG_KEYWORD("collation",COLLATION,UNRESERVED_KEYWORD)
82+
PG_KEYWORD("collation",COLLATION,RESERVED_KEYWORD)
8383
PG_KEYWORD("column",COLUMN,RESERVED_KEYWORD)
8484
PG_KEYWORD("comment",COMMENT,UNRESERVED_KEYWORD)
8585
PG_KEYWORD("comments",COMMENTS,UNRESERVED_KEYWORD)

‎src/include/utils/builtins.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -480,6 +480,7 @@ extern Datum pg_rotate_logfile(PG_FUNCTION_ARGS);
480480
externDatumpg_sleep(PG_FUNCTION_ARGS);
481481
externDatumpg_get_keywords(PG_FUNCTION_ARGS);
482482
externDatumpg_typeof(PG_FUNCTION_ARGS);
483+
externDatumpg_collation_for(PG_FUNCTION_ARGS);
483484

484485
/* oid.c */
485486
externDatumoidin(PG_FUNCTION_ARGS);

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

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -577,6 +577,26 @@ RESET enable_nestloop;
577577
-- 9.1 bug with useless COLLATE in an expression subject to length coercion
578578
CREATE TEMP TABLE vctable (f1 varchar(25));
579579
INSERT INTO vctable VALUES ('foo' COLLATE "C");
580+
SELECT collation for ('foo'); -- unknown type - null
581+
pg_collation_for
582+
------------------
583+
584+
(1 row)
585+
586+
SELECT collation for ('foo'::text);
587+
pg_collation_for
588+
------------------
589+
"default"
590+
(1 row)
591+
592+
SELECT collation for ((SELECT a FROM collate_test1 LIMIT 1)); -- non-collatable type - error
593+
ERROR: collations are not supported by type integer
594+
SELECT collation for ((SELECT b FROM collate_test1 LIMIT 1));
595+
pg_collation_for
596+
------------------
597+
"C"
598+
(1 row)
599+
580600
--
581601
-- Clean up. Many of these table names will be re-used if the user is
582602
-- trying to run any platform-specific collation tests later, so we

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

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -219,6 +219,13 @@ RESET enable_nestloop;
219219
CREATE TEMP TABLE vctable (f1varchar(25));
220220
INSERT INTO vctableVALUES ('foo' COLLATE"C");
221221

222+
223+
SELECT collation for ('foo');-- unknown type - null
224+
SELECT collation for ('foo'::text);
225+
SELECT collation for ((SELECT aFROM collate_test1LIMIT1));-- non-collatable type - error
226+
SELECT collation for ((SELECT bFROM collate_test1LIMIT1));
227+
228+
222229
--
223230
-- Clean up. Many of these table names will be re-used if the user is
224231
-- trying to run any platform-specific collation tests later, so we

0 commit comments

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