NLS functions v17

EDB Postgres Advanced Server version 17 and later provides the support for NLS functions. These functions uses the NLS config file to map the NLS parameters with an equivalent PostgreSQL Collation.

NLS config file

The edb_redwood_nls.config file maintains the list of NLS parameters mapping with an equivalent collation. The NLS parameters and an equivalent collation are listed in each row in the config file separated by commas respectively. The config file contains a default list of NLS parameters and an equivalent collation pairs. The NLS config file is located at EPAS_INSTALLATION_DIRECTORY/share/postgresql/contrib/.

The config file can contain multiple mappings for a single NLS parameter. You can add a new mapping for an existing NLS parameter. The latest added mapping will be used for the NLS function operations.

Before using NLS functions for a NLS parameter, add a new mapping in the NLS config file,

• manually by editing the config file in the format NLS_parameter,<schema_name>.<collation>.
• or by using the edb_nls_cf_insert function. When using edb_nls_cf_insert function it's crucial to ensure that the file has the correct write permissions. Without the appropriate permissions, attempts to modify the file will fail, potentially leading to errors or incomplete configurations.

where <schema_name>.<collation> is to be obtained from pg_collation catalog and mapped to a valid NLS parameter. Only the collations with an encoding of -1 (which represents "any encoding") or the database encoding are allowed.

If you're encountering NOTICE messages due to invalid entries in the NLS config file, the best approach is to manually remove those invalid entries. These messages typically occur when the database is trying to reference collations that doesn't exist or are incorrectly specified in the configuration file.

It's important to recognize that collation values are platform-dependent, meaning a collation available on one platform might not be present on another. To avoid compatibility issues, always use collations that are available in the pg_collation table within your environment.

Each client session reads the config file and stores the NLS parameters and collation values in in-memory HASH data structure when any of the NLS function is called for the first time. The parsing logic will read each row sequentially, segregate NLS parameters and collation values, and then store them in the right data structure.

Making changes to the config file manually requires you to reload the NLS config files in all sessions or restart the session to work with the updated mappings. Using the edb_nls_cf_insert function to modify the config file in a session will update the in-memory HASH data structure with new mappings. Changes made in a session will only affect other sessions after the config file is reloaded or after the other sessions are restarted.

The available functions for managing the NLS config file are listed in the following table:

Supported NLS functions

• NLS_UPPER

• NLS_LOWER

• NLS_INITCAP

Examples

This example shows how to add a new mapping in the NLS config file:

SELECT edb_nls_cf_insert('xgerman', '"pg_catalog"."de-x-icu"');

 edb_nls_cf_insert 
-------------------
 
(1 row)

This example shows how to reload the NLS config file:

SELECT edb_nls_cf_reload();

 edb_nls_cf_reload 
-------------------
 
(1 row)

• On this page
• NLS config file
• Examples