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

Commit615c384

Browse files
committed
Implement the collation columns of various information schema views
Fill in the collation columns of the views attributes, columns,domains, and element_types. Also update collation information insql_implementation_info.
1 parent5594d14 commit615c384

File tree

2 files changed

+132
-54
lines changed

2 files changed

+132
-54
lines changed

‎doc/src/sgml/information_schema.sgml

Lines changed: 93 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -379,6 +379,53 @@
379379
</entry>
380380
</row>
381381

382+
<row>
383+
<entry><literal>character_set_catalog</literal></entry>
384+
<entry><type>sql_identifier</type></entry>
385+
<entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
386+
</row>
387+
388+
<row>
389+
<entry><literal>character_set_schema</literal></entry>
390+
<entry><type>sql_identifier</type></entry>
391+
<entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
392+
</row>
393+
394+
<row>
395+
<entry><literal>character_set_name</literal></entry>
396+
<entry><type>sql_identifier</type></entry>
397+
<entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
398+
</row>
399+
400+
<row>
401+
<entry><literal>collation_catalog</literal></entry>
402+
<entry><type>sql_identifier</type></entry>
403+
<entry>
404+
Name of the database containing the collation of the attribute
405+
(always the current database), null if default or the data type
406+
of the attribute is not collatable
407+
</entry>
408+
</row>
409+
410+
<row>
411+
<entry><literal>collation_schema</literal></entry>
412+
<entry><type>sql_identifier</type></entry>
413+
<entry>
414+
Name of the schema containing the collation of the attribute,
415+
null if default or the data type of the attribute is not
416+
collatable
417+
</entry>
418+
</row>
419+
420+
<row>
421+
<entry><literal>collation_name</literal></entry>
422+
<entry><type>sql_identifier</type></entry>
423+
<entry>
424+
Name of the collation of the attribute, null if default or the
425+
data type of the attribute is not collatable
426+
</entry>
427+
</row>
428+
382429
<row>
383430
<entry><literal>numeric_precision</literal></entry>
384431
<entry><type>cardinal_number</type></entry>
@@ -1321,19 +1368,29 @@
13211368
<row>
13221369
<entry><literal>collation_catalog</literal></entry>
13231370
<entry><type>sql_identifier</type></entry>
1324-
<entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1371+
<entry>
1372+
Name of the database containing the collation of the column
1373+
(always the current database), null if default or the data type
1374+
of the column is not collatable
1375+
</entry>
13251376
</row>
13261377

13271378
<row>
13281379
<entry><literal>collation_schema</literal></entry>
13291380
<entry><type>sql_identifier</type></entry>
1330-
<entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1381+
<entry>
1382+
Name of the schema containing the collation of the column, null
1383+
if default or the data type of the column is not collatable
1384+
</entry>
13311385
</row>
13321386

13331387
<row>
13341388
<entry><literal>collation_name</literal></entry>
13351389
<entry><type>sql_identifier</type></entry>
1336-
<entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1390+
<entry>
1391+
Name of the collation of the column, null if default or the
1392+
data type of the column is not collatable
1393+
</entry>
13371394
</row>
13381395

13391396
<row>
@@ -1996,19 +2053,29 @@
19962053
<row>
19972054
<entry><literal>collation_catalog</literal></entry>
19982055
<entry><type>sql_identifier</type></entry>
1999-
<entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
2056+
<entry>
2057+
Name of the database containing the collation of the domain
2058+
(always the current database), null if default or the data type
2059+
of the domain is not collatable
2060+
</entry>
20002061
</row>
20012062

20022063
<row>
20032064
<entry><literal>collation_schema</literal></entry>
20042065
<entry><type>sql_identifier</type></entry>
2005-
<entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
2066+
<entry>
2067+
Name of the schema containing the collation of the domain, null
2068+
if default or the data type of the domain is not collatable
2069+
</entry>
20062070
</row>
20072071

20082072
<row>
20092073
<entry><literal>collation_name</literal></entry>
20102074
<entry><type>sql_identifier</type></entry>
2011-
<entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
2075+
<entry>
2076+
Name of the collation of the column, null if default or the
2077+
data type of the domain is not collatable
2078+
</entry>
20122079
</row>
20132080

20142081
<row>
@@ -2275,19 +2342,30 @@ ORDER BY c.ordinal_position;
22752342
<row>
22762343
<entry><literal>collation_catalog</literal></entry>
22772344
<entry><type>sql_identifier</type></entry>
2278-
<entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
2345+
<entry>
2346+
Name of the database containing the collation of the element
2347+
type (always the current database), null if default or the data
2348+
type of the element is not collatable
2349+
</entry>
22792350
</row>
22802351

22812352
<row>
22822353
<entry><literal>collation_schema</literal></entry>
22832354
<entry><type>sql_identifier</type></entry>
2284-
<entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
2355+
<entry>
2356+
Name of the schema containing the collation of the element
2357+
type, null if default or the data type of the element is not
2358+
collatable
2359+
</entry>
22852360
</row>
22862361

22872362
<row>
22882363
<entry><literal>collation_name</literal></entry>
22892364
<entry><type>sql_identifier</type></entry>
2290-
<entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
2365+
<entry>
2366+
Name of the collation of the element type, null if default or
2367+
the data type of the element is not collatable
2368+
</entry>
22912369
</row>
22922370

22932371
<row>
@@ -3025,19 +3103,19 @@ ORDER BY c.ordinal_position;
30253103
<row>
30263104
<entry><literal>collation_catalog</literal></entry>
30273105
<entry><type>sql_identifier</type></entry>
3028-
<entry>Applies to a featurenotavailable in <productname>PostgreSQL</></entry>
3106+
<entry>Always null, since this information isnotapplied to parameter data types in <productname>PostgreSQL</></entry>
30293107
</row>
30303108

30313109
<row>
30323110
<entry><literal>collation_schema</literal></entry>
30333111
<entry><type>sql_identifier</type></entry>
3034-
<entry>Applies to a featurenotavailable in <productname>PostgreSQL</></entry>
3112+
<entry>Always null, since this information isnotapplied to parameter data types in <productname>PostgreSQL</></entry>
30353113
</row>
30363114

30373115
<row>
30383116
<entry><literal>collation_name</literal></entry>
30393117
<entry><type>sql_identifier</type></entry>
3040-
<entry>Applies to a featurenotavailable in <productname>PostgreSQL</></entry>
3118+
<entry>Always null, since this information isnotapplied to parameter data types in <productname>PostgreSQL</></entry>
30413119
</row>
30423120

30433121
<row>
@@ -3912,19 +3990,19 @@ ORDER BY c.ordinal_position;
39123990
<row>
39133991
<entry><literal>collation_catalog</literal></entry>
39143992
<entry><type>sql_identifier</type></entry>
3915-
<entry>Applies to a featurenotavailable in <productname>PostgreSQL</></entry>
3993+
<entry>Always null, since this information isnotapplied to return data types in <productname>PostgreSQL</></entry>
39163994
</row>
39173995

39183996
<row>
39193997
<entry><literal>collation_schema</literal></entry>
39203998
<entry><type>sql_identifier</type></entry>
3921-
<entry>Applies to a featurenotavailable in <productname>PostgreSQL</></entry>
3999+
<entry>Always null, since this information isnotapplied to return data types in <productname>PostgreSQL</></entry>
39224000
</row>
39234001

39244002
<row>
39254003
<entry><literal>collation_name</literal></entry>
39264004
<entry><type>sql_identifier</type></entry>
3927-
<entry>Applies to a featurenotavailable in <productname>PostgreSQL</></entry>
4005+
<entry>Always null, since this information isnotapplied to return data types in <productname>PostgreSQL</></entry>
39284006
</row>
39294007

39304008
<row>

‎src/backend/catalog/information_schema.sql

Lines changed: 39 additions & 39 deletions
Original file line numberDiff line numberDiff line change
@@ -297,9 +297,9 @@ CREATE VIEW attributes AS
297297
CAST(nullAS sql_identifier)AS character_set_schema,
298298
CAST(nullAS sql_identifier)AS character_set_name,
299299

300-
CAST(nullAS sql_identifier)AS collation_catalog,
301-
CAST(nullAS sql_identifier)AS collation_schema,
302-
CAST(nullAS sql_identifier)AS collation_name,
300+
CAST(CASE WHENnco.nspnameIS NOT NULL THEN current_database() ENDAS sql_identifier)AS collation_catalog,
301+
CAST(nco.nspnameAS sql_identifier)AS collation_schema,
302+
CAST(co.collnameAS sql_identifier)AS collation_name,
303303

304304
CAST(
305305
_pg_numeric_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
@@ -336,14 +336,13 @@ CREATE VIEW attributes AS
336336
CAST(a.attnumAS sql_identifier)AS dtd_identifier,
337337
CAST('NO'AS yes_or_no)AS is_derived_reference_attribute
338338

339-
FROM (pg_attribute aLEFT JOIN pg_attrdef adON attrelid= adrelidAND attnum= adnum),
340-
pg_class c, pg_namespace nc,
341-
(pg_type tJOIN pg_namespace ntON (t.typnamespace=nt.oid))
339+
FROM (pg_attribute aLEFT JOIN pg_attrdef adON attrelid= adrelidAND attnum= adnum)
340+
JOIN (pg_class cJOIN pg_namespace ncON (c.relnamespace=nc.oid))ONa.attrelid=c.oid
341+
JOIN (pg_type tJOIN pg_namespace ntON (t.typnamespace=nt.oid))ONa.atttypid=t.oid
342+
LEFT JOIN (pg_collation coJOIN pg_namespace ncoON (co.collnamespace=nco.oid))
343+
ONa.attcollation=co.oidAND (nco.nspname,co.collname)<> ('pg_catalog','default')
342344

343-
WHEREa.attrelid=c.oid
344-
ANDa.atttypid=t.oid
345-
ANDnc.oid=c.relnamespace
346-
ANDa.attnum>0AND NOTa.attisdropped
345+
WHEREa.attnum>0AND NOTa.attisdropped
347346
ANDc.relkindin ('c');
348347

349348
GRANTSELECTON attributes TO PUBLIC;
@@ -678,9 +677,9 @@ CREATE VIEW columns AS
678677
CAST(nullAS sql_identifier)AS character_set_schema,
679678
CAST(nullAS sql_identifier)AS character_set_name,
680679

681-
CAST(nullAS sql_identifier)AS collation_catalog,
682-
CAST(nullAS sql_identifier)AS collation_schema,
683-
CAST(nullAS sql_identifier)AS collation_name,
680+
CAST(CASE WHENnco.nspnameIS NOT NULL THEN current_database() ENDAS sql_identifier)AS collation_catalog,
681+
CAST(nco.nspnameAS sql_identifier)AS collation_schema,
682+
CAST(co.collnameAS sql_identifier)AS collation_name,
684683

685684
CAST(CASE WHENt.typtype='d' THEN current_database() ELSEnull END
686685
AS sql_identifier)AS domain_catalog,
@@ -718,16 +717,15 @@ CREATE VIEW columns AS
718717
AND EXISTS (SELECT1FROM pg_rewriteWHERE ev_class=c.oidAND ev_type='4'AND is_instead))
719718
THEN'YES' ELSE'NO' ENDAS yes_or_no)AS is_updatable
720719

721-
FROM (pg_attribute aLEFT JOIN pg_attrdef adON attrelid= adrelidAND attnum= adnum),
722-
pg_class c,pg_namespace nc,
723-
(pg_type tJOIN pg_namespace ntON (t.typnamespace=nt.oid))
724-
LEFT JOIN (pg_type btJOIN pg_namespace nbtON (bt.typnamespace=nbt.oid))
720+
FROM (pg_attribute aLEFT JOIN pg_attrdef adON attrelid= adrelidAND attnum= adnum)
721+
JOIN (pg_class cJOINpg_namespace ncON (c.relnamespace=nc.oid))ONa.attrelid=c.oid
722+
JOIN(pg_type tJOIN pg_namespace ntON (t.typnamespace=nt.oid))ONa.atttypid=t.oid
723+
LEFT JOIN (pg_type btJOIN pg_namespace nbtON (bt.typnamespace=nbt.oid))
725724
ON (t.typtype='d'ANDt.typbasetype=bt.oid)
725+
LEFT JOIN (pg_collation coJOIN pg_namespace ncoON (co.collnamespace=nco.oid))
726+
ONa.attcollation=co.oidAND (nco.nspname,co.collname)<> ('pg_catalog','default')
726727

727-
WHEREa.attrelid=c.oid
728-
ANDa.atttypid=t.oid
729-
ANDnc.oid=c.relnamespace
730-
AND (NOT pg_is_other_temp_schema(nc.oid))
728+
WHERE (NOT pg_is_other_temp_schema(nc.oid))
731729

732730
ANDa.attnum>0AND NOTa.attisdroppedANDc.relkindin ('r','v','f')
733731

@@ -914,9 +912,9 @@ CREATE VIEW domains AS
914912
CAST(nullAS sql_identifier)AS character_set_schema,
915913
CAST(nullAS sql_identifier)AS character_set_name,
916914

917-
CAST(nullAS sql_identifier)AS collation_catalog,
918-
CAST(nullAS sql_identifier)AS collation_schema,
919-
CAST(nullAS sql_identifier)AS collation_name,
915+
CAST(CASE WHENnco.nspnameIS NOT NULL THEN current_database() ENDAS sql_identifier)AS collation_catalog,
916+
CAST(nco.nspnameAS sql_identifier)AS collation_schema,
917+
CAST(co.collnameAS sql_identifier)AS collation_name,
920918

921919
CAST(
922920
_pg_numeric_precision(t.typbasetype,t.typtypmod)
@@ -954,13 +952,13 @@ CREATE VIEW domains AS
954952
CAST(nullAS cardinal_number)AS maximum_cardinality,
955953
CAST(1AS sql_identifier)AS dtd_identifier
956954

957-
FROM pg_type t, pg_namespace nt,
958-
pg_type bt, pg_namespace nbt
955+
FROM (pg_type tJOIN pg_namespace ntONt.typnamespace=nt.oid)
956+
JOIN (pg_type btJOIN pg_namespace nbtONbt.typnamespace=nbt.oid)
957+
ON (t.typbasetype=bt.oidANDt.typtype='d')
958+
LEFT JOIN (pg_collation coJOIN pg_namespace ncoON (co.collnamespace=nco.oid))
959+
ONt.typcollation=co.oidAND (nco.nspname,co.collname)<> ('pg_catalog','default')
959960

960-
WHEREt.typnamespace=nt.oid
961-
ANDt.typbasetype=bt.oid
962-
ANDbt.typnamespace=nbt.oid
963-
ANDt.typtype='d';
961+
;
964962

965963
GRANTSELECTON domains TO PUBLIC;
966964

@@ -1515,7 +1513,7 @@ CREATE TABLE sql_implementation_info (
15151513
) WITHOUT OIDS;
15161514

15171515
INSERT INTO sql_implementation_infoVALUES ('10003','CATALOG NAME',NULL,'Y',NULL);
1518-
INSERT INTO sql_implementation_infoVALUES ('10004','COLLATING SEQUENCE',NULL,'','not supported');
1516+
INSERT INTO sql_implementation_infoVALUES ('10004','COLLATING SEQUENCE',NULL,(SELECT default_collate_nameFROM character_sets),NULL);
15191517
INSERT INTO sql_implementation_infoVALUES ('23','CURSOR COMMIT BEHAVIOR',1,NULL,'close cursors and retain prepared statements');
15201518
INSERT INTO sql_implementation_infoVALUES ('2','DATA SOURCE NAME',NULL,'',NULL);
15211519
INSERT INTO sql_implementation_infoVALUES ('17','DBMS NAME',NULL, (selecttrim(trailing''fromsubstring(version()from'^[^0-9]*'))),NULL);
@@ -2442,9 +2440,9 @@ CREATE VIEW element_types AS
24422440
CAST(nullAS sql_identifier)AS character_set_catalog,
24432441
CAST(nullAS sql_identifier)AS character_set_schema,
24442442
CAST(nullAS sql_identifier)AS character_set_name,
2445-
CAST(nullAS sql_identifier)AS collation_catalog,
2446-
CAST(nullAS sql_identifier)AS collation_schema,
2447-
CAST(nullAS sql_identifier)AS collation_name,
2443+
CAST(CASE WHENnco.nspnameIS NOT NULL THEN current_database() ENDAS sql_identifier)AS collation_catalog,
2444+
CAST(nco.nspnameAS sql_identifier)AS collation_schema,
2445+
CAST(co.collnameAS sql_identifier)AS collation_name,
24482446
CAST(nullAS cardinal_number)AS numeric_precision,
24492447
CAST(nullAS cardinal_number)AS numeric_precision_radix,
24502448
CAST(nullAS cardinal_number)AS numeric_scale,
@@ -2470,7 +2468,7 @@ CREATE VIEW element_types AS
24702468
/* columns, attributes*/
24712469
SELECTc.relnamespace, CAST(c.relnameAS sql_identifier),
24722470
CASE WHENc.relkind='c' THEN'USER-DEFINED TYPE'::text ELSE'TABLE'::text END,
2473-
a.attnum,a.atttypid
2471+
a.attnum,a.atttypid,a.attcollation
24742472
FROM pg_class c, pg_attribute a
24752473
WHEREc.oid=a.attrelid
24762474
ANDc.relkindIN ('r','v','f','c')
@@ -2480,15 +2478,15 @@ CREATE VIEW element_types AS
24802478

24812479
/* domains*/
24822480
SELECTt.typnamespace, CAST(t.typnameAS sql_identifier),
2483-
'DOMAIN'::text,1,t.typbasetype
2481+
'DOMAIN'::text,1,t.typbasetype,t.typcollation
24842482
FROM pg_type t
24852483
WHEREt.typtype='d'
24862484

24872485
UNION ALL
24882486

24892487
/* parameters*/
24902488
SELECT pronamespace, CAST(proname||'_'|| CAST(oidAStext)AS sql_identifier),
2491-
'ROUTINE'::text, (ss.x).n, (ss.x).x
2489+
'ROUTINE'::text, (ss.x).n, (ss.x).x,0
24922490
FROM (SELECTp.pronamespace,p.proname,p.oid,
24932491
_pg_expandarray(coalesce(p.proallargtypes,p.proargtypes::oid[]))AS x
24942492
FROM pg_proc p)AS ss
@@ -2497,10 +2495,12 @@ CREATE VIEW element_types AS
24972495

24982496
/* result types*/
24992497
SELECTp.pronamespace, CAST(p.proname||'_'|| CAST(p.oidAStext)AS sql_identifier),
2500-
'ROUTINE'::text,0,p.prorettype
2498+
'ROUTINE'::text,0,p.prorettype,0
25012499
FROM pg_proc p
25022500

2503-
)AS x (objschema, objname, objtype, objdtdid, objtypeid)
2501+
)AS x (objschema, objname, objtype, objdtdid, objtypeid, objcollation)
2502+
LEFT JOIN (pg_collation coJOIN pg_namespace ncoON (co.collnamespace=nco.oid))
2503+
ONx.objcollation=co.oidAND (nco.nspname,co.collname)<> ('pg_catalog','default')
25042504

25052505
WHEREn.oid=x.objschema
25062506
ANDat.oid=x.objtypeid

0 commit comments

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