Language & Translation Functions¶
Functions for managing languages and translations -- creating, updating, deleting, querying languages and their associated translations, copying translations between languages, and searching translation records with pagination. Languages are categorized as frontend, backend, or communication (or any combination), and each category supports a default language and custom sort ordering.
Translations provide localized strings keyed by data group, object identifier, and context (e.g., title, description). A single object can have multiple translated values via different contexts. All const tables (event_code, resource_type, resource_role, etc.) store their display text in translations rather than inline columns.
For read performance, the public.mv_translation materialized view pre-aggregates all contexts into a single jsonb per object. Write-path functions refresh the MV after mutations; read-path functions join the MV for single-index-probe lookups.
Source: 030_tables_language.sql, 031_functions_language.sql, 045_translation_context.sql
Language CRUD¶
public.create_language¶
Creates a new language record with category flags, logical ordering, and optional default designation. If the new language is marked as default for any category, the previous default in that category (within the same tenant) is automatically unset.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text | -- | Identifier of the user or system performing the action |
_user_id |
bigint | -- | ID of the user performing the action (used for permission check) |
_correlation_id |
text | -- | Correlation ID for tracing and audit logging |
_code |
text | -- | Unique language code (e.g. 'en', 'cs', 'de') |
_value |
text | -- | Human-readable language name (e.g. 'English', 'Czech') |
_is_frontend_language |
boolean | false |
Whether this language is available for frontend/UI use |
_is_backend_language |
boolean | false |
Whether this language is available for backend/system use |
_is_communication_language |
boolean | false |
Whether this language is available for communication (emails, notifications) |
_frontend_logical_order |
integer | 0 |
Sort order when listing frontend languages |
_backend_logical_order |
integer | 0 |
Sort order when listing backend languages |
_communication_logical_order |
integer | 0 |
Sort order when listing communication languages |
_is_default_frontend |
boolean | false |
Set as the default frontend language (unsets previous default) |
_is_default_backend |
boolean | false |
Set as the default backend language (unsets previous default) |
_is_default_communication |
boolean | false |
Set as the default communication language (unsets previous default) |
_custom_data |
jsonb | null |
Arbitrary JSON data for application-specific needs |
_tenant_id |
integer | 1 |
Tenant scope for the language |
Returns: setof const.language -- the newly created language row.
Permission required: languages.create_language
Journal event: 20001 (language_created)
Source: 031_functions_language.sql:26
public.update_language¶
Updates an existing language identified by _code. All fields except _code use coalesce semantics -- pass null to leave a field unchanged. If the language is being set as default for any category, the previous default in that category is automatically unset.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text | -- | Identifier of the user or system performing the update |
_user_id |
bigint | -- | ID of the user performing the action (used for permission check) |
_correlation_id |
text | -- | Correlation ID for tracing and audit logging |
_code |
text | -- | Language code of the language to update |
_value |
text | null |
New human-readable name, or null to keep current |
_is_frontend_language |
boolean | null |
New frontend flag, or null to keep current |
_is_backend_language |
boolean | null |
New backend flag, or null to keep current |
_is_communication_language |
boolean | null |
New communication flag, or null to keep current |
_frontend_logical_order |
integer | null |
New frontend sort order, or null to keep current |
_backend_logical_order |
integer | null |
New backend sort order, or null to keep current |
_communication_logical_order |
integer | null |
New communication sort order, or null to keep current |
_is_default_frontend |
boolean | null |
Set as default frontend language, or null to keep current |
_is_default_backend |
boolean | null |
Set as default backend language, or null to keep current |
_is_default_communication |
boolean | null |
Set as default communication language, or null to keep current |
_custom_data |
jsonb | null |
New custom data, or null to keep current |
_tenant_id |
integer | 1 |
Tenant scope |
Returns: setof const.language -- the updated language row.
Permission required: languages.update_language
Journal event: 20002 (language_updated)
Raises: 37001 -- "Language (code: ...) does not exist" if the language code is not found.
Source: 031_functions_language.sql:92
public.delete_language¶
Deletes a language by its code. Translations referencing this language are automatically deleted via the on delete cascade foreign key constraint on public.translation.language_code.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text | -- | Identifier of the user or system performing the deletion |
_user_id |
bigint | -- | ID of the user performing the action (used for permission check) |
_correlation_id |
text | -- | Correlation ID for tracing and audit logging |
_code |
text | -- | Language code to delete |
_tenant_id |
integer | 1 |
Tenant scope |
Returns: void
Permission required: languages.delete_language
Journal event: 20003 (language_deleted)
Raises: 37001 -- "Language (code: ...) does not exist" if the language code is not found.
Warning
Deleting a language will cascade-delete all translations associated with it. This operation is irreversible.
Source: 031_functions_language.sql:165
Language Queries¶
public.get_language¶
Returns a single language by its code. No permission check is performed -- this is a simple lookup function.
| Parameter | Type | Default | Description |
|---|---|---|---|
_code |
text | -- | Language code to look up |
Returns: setof const.language -- zero or one row matching the code.
Permission required: None
Source: 031_functions_language.sql:197
public.get_languages¶
Returns all languages for a tenant with optional category filters. Each row includes a __display_name resolved from translations (falling back to the language's own value if no translation exists for the display language).
| Parameter | Type | Default | Description |
|---|---|---|---|
_display_language_code |
text | 'en' |
Language code used to resolve display names from translations |
_is_frontend |
boolean | null |
Filter by frontend flag; null returns all |
_is_backend |
boolean | null |
Filter by backend flag; null returns all |
_is_communication |
boolean | null |
Filter by communication flag; null returns all |
_tenant_id |
integer | 1 |
Tenant scope |
Returns: table(__code text, __value text, __display_name text, __is_frontend_language boolean, __is_backend_language boolean, __is_communication_language boolean, __frontend_logical_order integer, __backend_logical_order integer, __communication_logical_order integer, __is_default_frontend boolean, __is_default_backend boolean, __is_default_communication boolean, __custom_data jsonb)
Results are ordered by value.
Permission required: None
Source: 031_functions_language.sql:210
public.get_frontend_languages¶
Returns all languages marked as frontend languages for a tenant, ordered by frontend_logical_order then value.
| Parameter | Type | Default | Description |
|---|---|---|---|
_display_language_code |
text | 'en' |
Language code used to resolve display names from translations |
_tenant_id |
integer | 1 |
Tenant scope |
Returns: table(__code text, __value text, __display_name text, __frontend_logical_order integer, __is_default_frontend boolean, __custom_data jsonb)
Permission required: None
Source: 031_functions_language.sql:266
public.get_backend_languages¶
Returns all languages marked as backend languages for a tenant, ordered by backend_logical_order then value.
| Parameter | Type | Default | Description |
|---|---|---|---|
_display_language_code |
text | 'en' |
Language code used to resolve display names from translations |
_tenant_id |
integer | 1 |
Tenant scope |
Returns: table(__code text, __value text, __display_name text, __backend_logical_order integer, __is_default_backend boolean, __custom_data jsonb)
Permission required: None
Source: 031_functions_language.sql:303
public.get_communication_languages¶
Returns all languages marked as communication languages for a tenant, ordered by communication_logical_order then value.
| Parameter | Type | Default | Description |
|---|---|---|---|
_display_language_code |
text | 'en' |
Language code used to resolve display names from translations |
_tenant_id |
integer | 1 |
Tenant scope |
Returns: table(__code text, __value text, __display_name text, __communication_logical_order integer, __is_default_communication boolean, __custom_data jsonb)
Permission required: None
Source: 031_functions_language.sql:340
public.get_default_language¶
Returns the default language for a given category (frontend, backend, or communication). Pass exactly one of the boolean filters as true to find the default for that category.
| Parameter | Type | Default | Description |
|---|---|---|---|
_display_language_code |
text | 'en' |
Language code used to resolve display names from translations |
_is_frontend |
boolean | null |
Pass true to get the default frontend language |
_is_backend |
boolean | null |
Pass true to get the default backend language |
_is_communication |
boolean | null |
Pass true to get the default communication language |
_tenant_id |
integer | 1 |
Tenant scope |
Returns: table(__code text, __value text, __display_name text, __custom_data jsonb) -- zero or one row.
Permission required: None
Source: 031_functions_language.sql:377
Translation CRUD¶
public.create_translation¶
Creates a new translation record for a specific language, data group, and object identifier. Translations can be keyed by either data_object_code (text key) or data_object_id (numeric key), or both.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text | -- | Identifier of the user or system performing the action |
_user_id |
bigint | -- | ID of the user performing the action (used for permission check) |
_correlation_id |
text | -- | Correlation ID for tracing and audit logging |
_language_code |
text | -- | Language code for the translation (must exist in const.language) |
_data_group |
text | -- | Logical grouping key (e.g. 'language', 'button', 'error_message') |
_value |
text | -- | The translated text value |
_data_object_code |
text | null |
Text key identifying the translated object within the group |
_data_object_id |
bigint | null |
Numeric key identifying the translated object within the group |
_context |
text | null |
Context discriminator (e.g., 'title', 'description'). Allows multiple translations per object |
_tenant_id |
integer | 1 |
Tenant scope |
Returns: setof public.translation -- the newly created translation row.
Permission required: translations.create_translation
Journal event: 21001 (translation_created)
Raises: 37001 -- "Language (code: ...) does not exist" if the language code is not found.
Note
A trigger (trg_calculate_translation) automatically populates nrm_search_data (accent-normalized text) and ts_search_data (full-text search vector) on insert and update. The regconfig used for full-text indexing is derived from the language code. After insert, unsecure.refresh_translation_cache() is called to update the materialized view.
Source: 045_translation_context.sql:35
public.update_translation¶
Updates the value of an existing translation identified by its translation_id.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text | -- | Identifier of the user or system performing the update |
_user_id |
bigint | -- | ID of the user performing the action (used for permission check) |
_correlation_id |
text | -- | Correlation ID for tracing and audit logging |
_translation_id |
integer | -- | ID of the translation to update |
_value |
text | -- | New translated text value |
_tenant_id |
integer | 1 |
Tenant scope |
Returns: setof public.translation -- the updated translation row.
Permission required: translations.update_translation
Journal event: 21002 (translation_updated)
Raises: 37002 -- "Translation (id: ...) does not exist" if the translation ID is not found.
Source: 032_functions_translation.sql:66
public.delete_translation¶
Deletes a translation record by its ID.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text | -- | Identifier of the user or system performing the deletion |
_user_id |
bigint | -- | ID of the user performing the action (used for permission check) |
_correlation_id |
text | -- | Correlation ID for tracing and audit logging |
_translation_id |
integer | -- | ID of the translation to delete |
_tenant_id |
integer | 1 |
Tenant scope |
Returns: void
Permission required: translations.delete_translation
Journal event: 21003 (translation_deleted)
Raises: 37002 -- "Translation (id: ...) does not exist" if the translation ID is not found.
Source: 032_functions_translation.sql:105
Copy & Bulk Operations¶
public.copy_translations¶
Copies translations from one language to another, optionally filtering by data group and supporting cross-tenant copies. The operation has two phases: (1) if _overwrite is true, existing translations in the target language are updated with values from the source; (2) translations that exist in the source but not in the target are inserted.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text | -- | Identifier of the user or system performing the copy |
_user_id |
bigint | -- | ID of the user performing the action (used for permission check) |
_correlation_id |
text | -- | Correlation ID for tracing and audit logging |
_from_language_code |
text | -- | Source language code to copy translations from |
_to_language_code |
text | -- | Target language code to copy translations to |
_overwrite |
boolean | false |
If true, existing translations in the target are updated with source values; if false, only missing translations are inserted |
_data_group |
text | null |
Limit the copy to a specific data group; null copies all groups |
_from_tenant_id |
integer | 1 |
Tenant ID of the source translations |
_to_tenant_id |
integer | 1 |
Tenant ID for the target translations |
_tenant_id |
integer | 1 |
Tenant scope for the permission check |
Returns: table(__operation text, __count bigint) -- two rows: one with __operation = 'updated' and the count of overwritten translations, and one with __operation = 'inserted' and the count of newly inserted translations.
Permission required: translations.copy_translations
Journal event: 21004 (translations_copied)
Raises: 37001 -- "Language (code: ...) does not exist" if either language code is not found.
Tip
Use _overwrite := false (the default) to safely fill in missing translations without touching existing ones. Set _overwrite := true when you want to fully synchronize the target language from the source.
Source: 032_functions_translation.sql:136
Query & Search¶
public.get_group_translations¶
Returns all translations for a given language, data group, and tenant as a single JSON object. Reads from the mv_translation materialized view for single-index-probe performance. Only translations with a non-null data_object_code are included.
When _context is specified, returns a flat map filtered to that context. When _context is null, returns nested objects with all contexts as keys.
| Parameter | Type | Default | Description |
|---|---|---|---|
_language_code |
text | -- | Language code to retrieve translations for |
_data_group |
text | -- | Data group to retrieve (e.g. 'button', 'resource_type') |
_context |
text | null |
Filter to a specific context (returns flat map). null returns all contexts as nested objects |
_tenant_id |
integer | 1 |
Tenant scope |
Returns: jsonb -- a JSON object, or '{}'::jsonb if no translations exist.
Permission required: None
Example
-- Flat: single context
select public.get_group_translations('en', 'button');
-- Returns: {"save": "Save", "cancel": "Cancel", "delete": "Delete"}
-- Nested: multiple contexts per object
select public.get_group_translations('en', 'resource_type');
-- Returns: {"folder": {"title": "Folder", "description": "A folder resource"}}
-- Filtered to one context
select public.get_group_translations('en', 'resource_type', 'title');
-- Returns: {"folder": "Folder"}
Source: 045_translation_context.sql:205
public.search_translations¶
Paginated search across translations with accent-insensitive text matching. Supports filtering by language, data group, object code, and object ID. The search text is normalized (accents removed, lowercased) and matched against the pre-computed nrm_search_data column.
| Parameter | Type | Default | Description |
|---|---|---|---|
_user_id |
bigint | -- | ID of the user performing the action (used for permission check) |
_correlation_id |
text | -- | Correlation ID for tracing and audit logging |
_display_language_code |
text | 'en' |
Language code used to resolve the language display name in results |
_search_text |
text | null |
Free-text search string (accent-insensitive); null or empty returns all |
_language_code |
text | null |
Filter by specific language code; null returns all languages |
_data_group |
text | null |
Filter by data group; null returns all groups |
_data_object_code |
text | null |
Filter by object code; null returns all |
_data_object_id |
bigint | null |
Filter by object ID; null returns all |
_context |
text | null |
Filter by context (e.g., 'title'); null returns all contexts |
_page |
integer | 1 |
Page number (1-based) |
_page_size |
integer | 10 |
Number of results per page (capped at 100) |
_tenant_id |
integer | 1 |
Tenant scope |
Returns: table(__translation_id integer, __language_code text, __language_value text, __data_group text, __data_object_code text, __data_object_id bigint, __context text, __value text, __created_at timestamptz, __created_by text, __updated_at timestamptz, __updated_by text, __total_items bigint)
Results are ordered by data_group, data_object_code, context, language_code. Every row includes __total_items (the total count before pagination) to support UI paging controls.
Permission required: translations.read_translations
Source: 045_translation_context.sql:253
Table Structures¶
const.language¶
The language registry table.
| Column | Type | Default | Description |
|---|---|---|---|
code |
text | -- | Primary key. Unique language code (e.g. 'en', 'de') |
value |
text | -- | Human-readable language name |
tenant_id |
integer | 1 |
FK to auth.tenant |
is_frontend_language |
boolean | false |
Available for frontend/UI use |
is_backend_language |
boolean | false |
Available for backend/system use |
is_communication_language |
boolean | false |
Available for communication (emails, notifications) |
frontend_logical_order |
integer | 0 |
Sort order for frontend language lists |
backend_logical_order |
integer | 0 |
Sort order for backend language lists |
communication_logical_order |
integer | 0 |
Sort order for communication language lists |
is_default_frontend |
boolean | false |
Default language for frontend |
is_default_backend |
boolean | false |
Default language for backend |
is_default_communication |
boolean | false |
Default language for communication |
custom_data |
jsonb | null |
Arbitrary application-specific data |
created_at |
timestamptz | now() |
Creation timestamp |
created_by |
text | 'unknown' |
Creator identifier |
updated_at |
timestamptz | now() |
Last update timestamp |
updated_by |
text | 'unknown' |
Last updater identifier |
Seed data: An 'en' (English) language is created by default, marked as default for all three categories.
Source: 030_tables_language.sql:23
public.translation¶
The translation storage table with full-text search support.
| Column | Type | Default | Description |
|---|---|---|---|
translation_id |
integer | auto-generated identity | Primary key |
language_code |
text | -- | FK to const.language(code) with on delete cascade |
tenant_id |
integer | 1 |
FK to auth.tenant |
data_group |
text | -- | Logical grouping (e.g. 'button', 'label', 'language') |
data_object_code |
text | null |
Text key for the translated object |
data_object_id |
bigint | null |
Numeric key for the translated object |
context |
text | null |
Context discriminator (e.g., 'title', 'description'). Enables multiple translations per object |
value |
text | -- | The translated text |
nrm_search_data |
text | trigger-generated | Accent-normalized, lowercased text for search |
ts_search_data |
tsvector | trigger-generated | Full-text search vector (language-aware) |
created_at |
timestamptz | now() |
Creation timestamp |
created_by |
text | 'unknown' |
Creator identifier |
updated_at |
timestamptz | now() |
Last update timestamp |
updated_by |
text | 'unknown' |
Last updater identifier |
Indexes:
uq_translation_code-- unique on(language_code, data_group, data_object_code, coalesce(context, ''))wheredata_object_code is not nulluq_translation_id-- unique on(language_code, data_group, data_object_id, coalesce(context, ''))wheredata_object_id is not nullix_translation_ts_search-- GIN index onts_search_dataix_translation_nrm_search-- GIN trigram index onnrm_search_dataix_translation_group-- B-tree index on(data_group, language_code)
Source: 030_tables_language.sql:48
public.mv_translation (Materialized View)¶
Pre-aggregated translation cache. One row per (language, group, object) with all contexts merged into a single jsonb. Used by all read-path functions for single-index-probe performance.
| Column | Type | Description |
|---|---|---|
language_code |
text | Language code |
tenant_id |
integer | Tenant scope |
data_group |
text | Logical grouping |
data_object_code |
text | Text key |
data_object_id |
bigint | Numeric key |
values |
jsonb | All contexts merged: {"title": "...", "description": "..."} |
Indexes:
uq_mv_translation_code-- unique on(language_code, data_group, data_object_code)wheredata_object_code is not nulluq_mv_translation_id-- unique on(language_code, data_group, data_object_id)wheredata_object_id is not nullix_mv_translation_group-- B-tree on(data_group, language_code)
Refresh: unsecure.refresh_translation_cache() — called automatically by write-path functions. Uses REFRESH MATERIALIZED VIEW CONCURRENTLY (non-blocking).
Source: 030_tables_language.sql:97
Error Codes¶
| Code | Constant | Description |
|---|---|---|
37001 |
err_language_not_found |
Language with the given code does not exist |
37002 |
err_translation_not_found |
Translation with the given ID does not exist |
Event Codes¶
| Event ID | Code | Category | Description |
|---|---|---|---|
20001 |
language_created |
Language Events | New language was created |
20002 |
language_updated |
Language Events | Language was updated |
20003 |
language_deleted |
Language Events | Language was deleted |
21001 |
translation_created |
Translation Events | New translation was created |
21002 |
translation_updated |
Translation Events | Translation was updated |
21003 |
translation_deleted |
Translation Events | Translation was deleted |
21004 |
translations_copied |
Translation Events | Translations were copied between languages |
Permission Summary¶
| Function | Required Permission |
|---|---|
public.create_language |
languages.create_language |
public.update_language |
languages.update_language |
public.delete_language |
languages.delete_language |
public.get_language |
None |
public.get_languages |
None |
public.get_frontend_languages |
None |
public.get_backend_languages |
None |
public.get_communication_languages |
None |
public.get_default_language |
None |
public.create_translation |
translations.create_translation |
public.update_translation |
translations.update_translation |
public.delete_translation |
translations.delete_translation |
public.copy_translations |
translations.copy_translations |
public.get_group_translations |
None |
public.search_translations |
translations.read_translations |