From b313bca0afce3ab9dab0a77c64c0982835854b9a Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Sat, 12 Feb 2011 15:54:13 +0200 Subject: DDL support for collations - collowner field - CREATE COLLATION - ALTER COLLATION - DROP COLLATION - COMMENT ON COLLATION - integration with extensions - pg_dump support for the above - dependency management - psql tab completion - psql \dO command --- doc/src/sgml/catalogs.sgml | 21 +++- doc/src/sgml/charset.sgml | 11 ++- doc/src/sgml/ref/allfiles.sgml | 3 + doc/src/sgml/ref/alter_collation.sgml | 128 ++++++++++++++++++++++++ doc/src/sgml/ref/alter_extension.sgml | 1 + doc/src/sgml/ref/comment.sgml | 2 + doc/src/sgml/ref/create_collation.sgml | 175 +++++++++++++++++++++++++++++++++ doc/src/sgml/ref/drop_collation.sgml | 110 +++++++++++++++++++++ doc/src/sgml/ref/psql-ref.sgml | 19 ++++ doc/src/sgml/reference.sgml | 3 + 10 files changed, 467 insertions(+), 6 deletions(-) create mode 100644 doc/src/sgml/ref/alter_collation.sgml create mode 100644 doc/src/sgml/ref/create_collation.sgml create mode 100644 doc/src/sgml/ref/drop_collation.sgml (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index a373829d39d..e93347992f2 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -2114,11 +2114,30 @@ + + collowner + oid + pg_authid.oid + Owner of the collation + + collencoding int4 - Encoding to which the collation is applicable + + Encoding to which the collation is applicable. SQL-level + commands such as ALTER COLLATION only + operate on the collation belonging to the current database + encoding. But this field is necessary because when this + catalog is initialized, the encoding of future databases is not + yet known. For practical purposes, collations that do not + match the current database encoding should be considered + invalid or invisible. It could be useful, however, to create + collations whose encoding does not match the database encoding + in template databases. This would currently have to be done + manually. + diff --git a/doc/src/sgml/charset.sgml b/doc/src/sgml/charset.sgml index 49e1bd25b43..046c3d14168 100644 --- a/doc/src/sgml/charset.sgml +++ b/doc/src/sgml/charset.sgml @@ -459,11 +459,12 @@ SELECT a || ('foo' COLLATE "y") FROM test1; In case a collation is needed that has different values for - LC_COLLATE and LC_CTYPE, or a - different name is needed for a collation (for example, for - compatibility with existing applications), a new collation may be - created. But there is currently no SQL-level support for creating - or changing collations. + LC_COLLATE and LC_CTYPE, a new + collation may be created using + the command. That command + can also be used to create a new collation from an existing + collation, which can be useful to be able to use operating-system + independent collation names in applications. diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml index ba85cae0837..ac6ac5b3d2a 100644 --- a/doc/src/sgml/ref/allfiles.sgml +++ b/doc/src/sgml/ref/allfiles.sgml @@ -7,6 +7,7 @@ Complete list of usable sgml source files in this directory. + @@ -48,6 +49,7 @@ Complete list of usable sgml source files in this directory. + @@ -85,6 +87,7 @@ Complete list of usable sgml source files in this directory. + diff --git a/doc/src/sgml/ref/alter_collation.sgml b/doc/src/sgml/ref/alter_collation.sgml new file mode 100644 index 00000000000..3aef656a0e9 --- /dev/null +++ b/doc/src/sgml/ref/alter_collation.sgml @@ -0,0 +1,128 @@ + + + + + ALTER COLLATION + 7 + SQL - Language Statements + + + + ALTER COLLATION + change the definition of a collation + + + + ALTER COLLATION + + + + +ALTER COLLATION name RENAME TO new_name +ALTER COLLATION name OWNER TO new_owner +ALTER COLLATION name SET SCHEMA new_schema + + + + + Description + + + ALTER COLLATION changes the definition of a + collation. + + + + You must own the collation to use ALTER COLLATION. + To alter the owner, you must also be a direct or indirect member of the new + owning role, and that role must have CREATE privilege on + the collation's schema. (These restrictions enforce that altering the + owner doesn't do anything you couldn't do by dropping and recreating the + collation. However, a superuser can alter ownership of any collation + anyway.) + + + + + Parameters + + + + name + + + The name (optionally schema-qualified) of an existing collation. + + + + + + new_name + + + The new name of the collation. + + + + + + new_owner + + + The new owner of the collation. + + + + + + new_schema + + + The new schema for the collation. + + + + + + + + Examples + + + To rename the collation de_DE to + german: + +ALTER COLLATION "de_DE" RENAME TO german; + + + + + To change the owner of the collation en_US to + joe: + +ALTER COLLATION "en_US" OWNER TO joe; + + + + + + Compatibility + + + There is no ALTER COLLATION statement in the SQL + standard. + + + + + See Also + + + + + + + diff --git a/doc/src/sgml/ref/alter_extension.sgml b/doc/src/sgml/ref/alter_extension.sgml index a6c0062fe24..d12aee251b5 100644 --- a/doc/src/sgml/ref/alter_extension.sgml +++ b/doc/src/sgml/ref/alter_extension.sgml @@ -32,6 +32,7 @@ ALTER EXTENSION extension_name DROP AGGREGATE agg_name (agg_type [, ...] ) | CAST (source_type AS target_type) | + COLLATION object_name | CONVERSION object_name | DOMAIN object_name | FOREIGN DATA WRAPPER object_name | diff --git a/doc/src/sgml/ref/comment.sgml b/doc/src/sgml/ref/comment.sgml index e1fe0c16f9a..2610fd5b8d5 100644 --- a/doc/src/sgml/ref/comment.sgml +++ b/doc/src/sgml/ref/comment.sgml @@ -27,6 +27,7 @@ COMMENT ON COLUMN table_name.column_name | AGGREGATE agg_name (agg_type [, ...] ) | CAST (source_type AS target_type) | + COLLATION object_name | CONSTRAINT constraint_name ON table_name | CONVERSION object_name | DATABASE object_name | @@ -245,6 +246,7 @@ COMMENT ON TABLE mytable IS NULL; COMMENT ON AGGREGATE my_aggregate (double precision) IS 'Computes sample variance'; COMMENT ON CAST (text AS int4) IS 'Allow casts from text to int4'; +COMMENT ON COLLATION "fr_CA" IS 'Canadian French'; COMMENT ON COLUMN my_table.my_column IS 'Employee ID number'; COMMENT ON CONVERSION my_conv IS 'Conversion to UTF8'; COMMENT ON DATABASE my_database IS 'Development Database'; diff --git a/doc/src/sgml/ref/create_collation.sgml b/doc/src/sgml/ref/create_collation.sgml new file mode 100644 index 00000000000..9d03ca5a4eb --- /dev/null +++ b/doc/src/sgml/ref/create_collation.sgml @@ -0,0 +1,175 @@ + + + + + CREATE COLLATION + 7 + SQL - Language Statements + + + + CREATE COLLATION + define a new collation + + + + CREATE COLLATION + + + + +CREATE COLLATION name ( + [ LOCALE = locale, ] + [ LC_COLLATE = lc_collate, ] + [ LC_CTYPE = lc_ctype, ] +) +CREATE COLLATION name FROM existing_collation + + + + + Description + + + CREATE COLLATION defines a new collation using + the specified operating system locales or from an existing collation. + + + + To be able to create a collation, you must + have CREATE privilege on the destination schema. + + + + + + Parameters + + + + name + + + + The name of the collation. The collation name can be + schema-qualified. If it is not, the collation is defined in the + current schema. The collation name must be unique within a + schema. (The system catalogs can contain collations with the + same name for other encodings, but these are not usable if the + database encoding does not match.) + + + + + + existing_collation + + + + The name of an existing collation to copy. The new collation + will have the same properties as the existing one, but they + will become independent objects. + + + + + + locale + + + + This is a shortcut for setting LC_COLLATE + and LC_CTYPE at once. If you specify this, + you cannot specify either of the other parameters. + + + + + + lc_collate + + + + Use the specified operating system locale for + the LC_COLLATE locale category. The locale + must be applicable to the current database encoding. + (See for the precise + rules.) + + + + + + lc_ctype + + + + Use the specified operating system locale for + the LC_CTYPE locale category. The locale + must be applicable to the current database encoding. + (See for the precise + rules.) + + + + + + + + + Notes + + + Use DROP COLLATION to remove user-defined collations. + + + + See for more information about collation + support in PostgreSQL. + + + + + Examples + + + To create a collation from the locale fr_FR.utf8 + (assuming the current database encoding is UTF8): + +CREATE COLLATION french (LOCALE = 'fr_FR.utf8'); + + + + + To create a collation from an existing collation: + +CREATE COLLATION german FROM "de_DE"; + + This can be convenient to be able to use operating-system + independent collation names in applications. + + + + + + Compatibility + + + There is a CREATE COLLATION statement in the SQL + standard, but it is limited to copying an existing collation. The + syntax to create a new collation is + a PostgreSQL extension. + + + + + + See Also + + + + + + + + diff --git a/doc/src/sgml/ref/drop_collation.sgml b/doc/src/sgml/ref/drop_collation.sgml new file mode 100644 index 00000000000..7be9317932c --- /dev/null +++ b/doc/src/sgml/ref/drop_collation.sgml @@ -0,0 +1,110 @@ + + + + + DROP COLLATION + 7 + SQL - Language Statements + + + + DROP COLLATION + remove a collation + + + + DROP COLLATION + + + + +DROP COLLATION [ IF EXISTS ] name [ CASCADE | RESTRICT ] + + + + + Description + + + DROP COLLATION removes a previously defined collation. + To be able to drop a collation, you must own the collation. + + + + + Parameters + + + + IF EXISTS + + + Do not throw an error if the collation does not exist. + A notice is issued in this case. + + + + + + name + + + + The name of the collation. The collation name can be + schema-qualified. + + + + + + CASCADE + + + Automatically drop objects that depend on the collation. + + + + + + RESTRICT + + + Refuse to drop the collation if any objects depend on it. This + is the default. + + + + + + + + Examples + + + To drop the collation named german: + +DROP COLLATION german; + + + + + + Compatibility + + + The DROP COLLATION command conforms to the + SQL standard, apart from the IF + EXISTS option, which is a PostgreSQL extension.. + + + + + See Also + + + + + + + + diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index cdf1abfa956..ff60a72059e 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1265,6 +1265,7 @@ testdb=> + \dn[S+] [ pattern ] @@ -1297,6 +1298,24 @@ testdb=> + + \dO[S+] [ pattern ] + + + + Lists collations. + If pattern is + specified, only collations whose names match the pattern are + listed. By default, only user-created objects are shown; + supply a pattern or the S modifier to + include system objects. If + is appended + to the command name, each object is listed with its associated + description, if any. + + + + + \dp [ pattern ] diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml index 47cd01f58e2..9ae80005cdf 100644 --- a/doc/src/sgml/reference.sgml +++ b/doc/src/sgml/reference.sgml @@ -35,6 +35,7 @@ &abort; &alterAggregate; + &alterCollation; &alterConversion; &alterDatabase; &alterDefaultPrivileges; @@ -76,6 +77,7 @@ ©Table; &createAggregate; &createCast; + &createCollation; &createConversion; &createDatabase; &createDomain; @@ -113,6 +115,7 @@ &do; &dropAggregate; &dropCast; + &dropCollation; &dropConversion; &dropDatabase; &dropDomain; -- cgit v1.2.3