Extensions
- pg_xenophile 0.5.10
README
Contents
pg_extension_name: pg_xenophile pg_extension_version: 0.5.10 pg_readme_generated_at: 2023-03-16 11:53:01.705416+00
pg_readme_version: 0.6.0
pg_xenophile
PostgreSQL extension
The pg_xenophile
PostgreSQL extension bundles a bunch of data, data
structures and routines that you often end up needing when working on an
international project:
- tables with the usual data that you need on countries, regions, languages and currencies;
- functions to easily store and access translated strings; and
- (trigger) functions to set up one-to-many translation tables with easy-to-use views on top.
It's perfectly valid to just use pg_xenophile
as a repository for
up-to-date lists of countries and languages and such. But, the extension
becomes especially worthwhile if you want some comfort on top of the common
many-to-one translation-table pattern.
All your ethnocentrism are belong to us.
Using pg_xenophile
To use the list of countries (from the country
table) or
languages (from the lang
table, just use them. And don't be
afraid of using the natural keys in your foreign keys! If you've been told to
fear them, you will soon be attached to the convenience of not needing to join
to know what a foreign key value means.
If you want a translatable table, you have to register the base table with the
l10n_table
meta table. See the l10n_table
documentation in the reference for details. From the
l10n_table
documentation, you should also be able to learn how to work with
the lang_l10n
, lang_l10n_en
, country_l10n
and country_l10n_en
tables
and views that are manintained via the triggers on this meta table.
Extension-specific settings
| Extenion-hooked setting name | app.
-hooked setting name | Default setting value |
| -------------------------------- | -------------------------------------- | ------------------------------- |
| pg_xenophile.base_lang_code
| app.settings.i18n.base_lang_code
| 'en'::xeno.lang_code_alpha2
|
| pg_xenophile.user_lang_code
| app.settings.i18n.user_lang_code
| 'en'::xeno.lang_code_alpha2
|
| pg_xenophile.target_lang_codes
| app.settings.i18n.target_lang_codes
| '{}'::xeno.lang_code_alpha2[]
|
The reason that each pg_xenophile
setting has an equivalent setting with an
app.settings.i18n
prefix is because the powerful PostgREST can pass on such
settings from environment variables: PGRST_APP_SETTINGS_*
maps to
app.settings.*
. The app.settings.
-prefixed settings take precedence over
pg_xenophile.
-prefixed settings.
Supporting only the app.settings.
-prefixed settings would not be a good
idea, because, in the circumstance that you would be running an extension
called “app
”, these settings might disappear, as per the relevant
documentation:
[…] Such variables are treated as placeholders and have no function until the module that defines them is loaded. When an extension module is loaded, it will add its variable definitions and convert any placeholder values according to those definitions. If there are any unrecognized placeholders that begin with its extension name, warnings are issued and those placeholders are removed.
In addition to the above, the user_lang_code
setting, if set as neither
app.settings.i18n.user_lang_code
and pg_xenophile.user_lang_code
, falls
back to the first two letters of the lc_messages
setting.
Internal settings
| Setting name | Default setting value |
| -------------------------------------------- | ------------------------------- |
| pg_xenophile.in_l10n_table_event_trigger
| false
|
| pg_xenophile.in_l10n_table_row_trigger
| false
|
Object reference
Schema: xeno
pg_xenophile
must be installed in the xeno
schema. Hence, it is not relocatable.
The xeno
schema belongs to the pg_xenophile
extension.
Postgres (as of Pg 15) doesn't allow one to specify a default schema, and do
something like schema = 'xeno'
combined with relocatable = true
in the
.control
file. Therefore I decided to bluntly force the xeno
schema name
upon you, even though you might have very well (and justifyingly so) preferred
something like i18n
.
Tables
There are 8 tables that directly belong to the pg_xenophile
extension.
Table: currency
The currency
table contains the currencies known to pg_xenophile
.
The currency
table has 5 attributes:
currency.currency_code
currency_code
currency_code
is a 3-letter ISO 4217 currency code.NOT NULL
PRIMARY KEY (currency_code)
currency.currency_code_num
text
currency_code
is the numeric 3-digit ISO 4217 currency code.NOT NULL
CHECK (currency_code_num ~ '^[0-9]{3}$'::text)
UNIQUE (currency_code_num)
currency.currency_symbol
text
NOT NULL
CHECK (length(currency_symbol) = 1)
currency.decimal_digits
integer
NOT NULL
DEFAULT 2
currency.currency_belongs_to_pg_xenophile
boolean
Does this currency belong to the
pg_xenophile
extension or not.If
NOT currency_belongs_to_pg_xenophile
, it is considered a custom currency inserted by the extension user rather than the extension developer. Instead (or in addition) of adding such custom rows, please feel free to submit patches with all the currencies that you wish forpg_xenophile
to embrace.NOT NULL
DEFAULT false
Table: country
The ISO 3166-1 alpha-2, alpha3 and numeric country codes, as well as some auxillary information.
The country
table has 6 attributes:
country.country_code
country_code_alpha2
NOT NULL
PRIMARY KEY (country_code)
country.country_code_alpha3
text
CHECK (country_code_alpha3 ~ '^[A-Z]{3}$'::text)
UNIQUE (country_code_alpha3)
country.country_code_num
text
NOT NULL
CHECK (country_code_num ~ '^[0-9]{3}$'::text)
country.calling_code
integer
NOT NULL
country.currency_code
text
NOT NULL
DEFAULT 'EUR'::text
FOREIGN KEY (currency_code) REFERENCES currency(currency_code) ON UPDATE CASCADE ON DELETE RESTRICT
country.country_belongs_to_pg_xenophile
boolean
NOT NULL
DEFAULT false
Table: country_postal_code_pattern
The country_postal_code_pattern
table has 8 attributes:
country_postal_code_pattern.country_code
country_code_alpha2
NOT NULL
PRIMARY KEY (country_code)
FOREIGN KEY (country_code) REFERENCES country(country_code)
country_postal_code_pattern.valid_postal_code_regexp
text
NOT NULL
country_postal_code_pattern.clean_postal_code_regexp
text
country_postal_code_pattern.clean_postal_code_replace
text
country_postal_code_pattern.postal_code_example
text
NOT NULL
country_postal_code_pattern.postal_code_pattern_checked_on
date
country_postal_code_pattern.postal_code_pattern_information_source
text
country_postal_code_pattern.postal_code_pattern_belongs_to_pg_xenophile
boolean
NOT NULL
DEFAULT false
Table: eu_country
The eu_country
table has 3 attributes:
eu_country.country_code
country_code_alpha2
NOT NULL
PRIMARY KEY (country_code)
FOREIGN KEY (country_code) REFERENCES country(country_code)
eu_country.eu_membership_checked_on
date
eu_country.eu_country_belongs_to_pg_xenophile
boolean
NOT NULL
DEFAULT false
Table: l10n_table
The l10n_table
table is meant to keep track and manage all the _l10n
-suffixed tables.
By inserting a row in this table, with just the
details of the base table, a many-to-one l10n table called
<base_table_name>_l10n
will be created by the maintain_l10n_objects
trigger. This trigger will also take care of creating the
<base_table_name>_l10n_<base_lang_code>
view as well as one such view for
all the target_lang_codes
. These views combine the columns of the base
table with the columns of the l10n table, filtered by the language code
specific to that particular view.
One of the reasons to manage this through a table rather than through a stored
procedure is that a list of such enhance l10n tables needs to be kept by
pg_xenophile
anyway: in the likely case that updates necessitate the
upgrading of (the views and/or triggers around) these tables, the extension
update script will know where to find everything.
It may not immediately be obvious why, besides the base_table_regclass
and
the l10n_table_regclass
columns, schema_name
, base_table_name
and
l10n_table_name
also exist. After all, PostgreSQL has some very comfortable
magic surrounding regclass
and related object identifier
types. Two reasons:
- Even though
pg_dump
has the ability to dump OIDs, tables belonging to extensions are not dumped at all, except for any part exempted from this using thepg_catalog.pg_extension_config_dump()
function. Forl10n_table
, only the columns for whichl10n_table_belongs_to_pg_xenophile = false
are included in the dump. - OIDs of tables and other catalog objects are not guaranteed to remain the
same between
pg_dump
andpg_restore
.
The l10n_table
table has 11 attributes:
l10n_table.schema_name
name
NOT NULL
DEFAULT CURRENT_SCHEMA
l10n_table.base_table_name
name
NOT NULL
l10n_table.base_table_regclass
regclass
NOT NULL
PRIMARY KEY (base_table_regclass)
l10n_table.base_column_definitions
text[]
NOT NULL
l10n_table.l10n_table_name
name
NOT NULL
l10n_table.l10n_table_regclass
regclass
NOT NULL
UNIQUE (l10n_table_regclass)
l10n_table.l10n_column_definitions
text[]
NOT NULL
l10n_table.l10n_table_constraint_definitions
text[]
NOT NULL
DEFAULT ARRAY[]::text[]
l10n_table.base_lang_code
lang_code_alpha2
NOT NULL
DEFAULT pg_xenophile_base_lang_code()
l10n_table.target_lang_codes
lang_code_alpha2[]
NOT NULL
DEFAULT pg_xenophile_target_lang_codes()
l10n_table.l10n_table_belongs_to_pg_xenophile
boolean
If this is
true
, then the created localization (l10n) table will be managed (and thus recreated after a restore) by thepg_xenophile
extension. That is not the same as saying that the l10n table's rows will belong topg_xenophile
. To determine the latter, al10n_columns_belong_to_pg_xenophile
column will be added to the l10n table ifcreate_l10n_table()
was called with thewill_belong_to_pg_xenophile$ => true
argument.Only developers of this extension need to worry about these booleans. For users, the default of
false
assures that they will lose none of their precious data.NOT NULL
DEFAULT false
Table: lang
The lang
table has 2 attributes:
lang.lang_code
lang_code_alpha2
ISO 639-1 two-letter (lowercase) language code.
NOT NULL
PRIMARY KEY (lang_code)
lang.lang_belongs_to_pg_xenophile
boolean
NOT NULL
DEFAULT false
Table: lang_l10n
This table is managed by the pg_xenophile
extension, which has delegated its creation to the maintain_l10n_objects
trigger on the l10n_table
table. To alter this table, just ALTER
it as you normally would. The l10n_table__track_alter_table_events
event trigger will detect such changes, as well as changes to the base table (lang
) referenced by the foreign key (that doubles as primary key) on lang_l10n
. When any ALTER TABLE lang_l10n
or ALTER TABLE lang
events are detected, l10n_table
will be updated—the base_column_definitions
, l10n_column_definitions
and l10n_table_constraint_definitions
columns—with the latest information from the pg_catalog
.
These changes to l10n_table
in turn trigger the maintain_l10n_objects
trigger, which ensures that the language-specific convenience views that (left) join lang
to lang_l10n
are kept up-to-date with the columns in these tables.
To drop this table, either just DROP TABLE
it (and the l10n_table__track_drop_table_events
will take care of the book-keeping or delete its bookkeeping row from l10n_table
.
The lang_l10n
table has 4 attributes:
lang_l10n.lang_code
lang_code_alpha2
NOT NULL
FOREIGN KEY (lang_code) REFERENCES lang(lang_code) ON UPDATE CASCADE ON DELETE CASCADE
lang_l10n.l10n_lang_code
lang_code_alpha2
NOT NULL
FOREIGN KEY (l10n_lang_code) REFERENCES lang(lang_code) ON UPDATE RESTRICT ON DELETE RESTRICT
lang_l10n.l10n_columns_belong_to_pg_xenophile
boolean
NOT NULL
DEFAULT false
lang_l10n.name
text
NOT NULL
Table: country_l10n
This table is managed by the pg_xenophile
extension, which has delegated its creation to the maintain_l10n_objects
trigger on the l10n_table
table. To alter this table, just ALTER
it as you normally would. The l10n_table__track_alter_table_events
event trigger will detect such changes, as well as changes to the base table (country
) referenced by the foreign key (that doubles as primary key) on country_l10n
. When any ALTER TABLE country_l10n
or ALTER TABLE country
events are detected, l10n_table
will be updated—the base_column_definitions
, l10n_column_definitions
and l10n_table_constraint_definitions
columns—with the latest information from the pg_catalog
.
These changes to l10n_table
in turn trigger the maintain_l10n_objects
trigger, which ensures that the language-specific convenience views that (left) join country
to country_l10n
are kept up-to-date with the columns in these tables.
To drop this table, either just DROP TABLE
it (and the l10n_table__track_drop_table_events
will take care of the book-keeping or delete its bookkeeping row from l10n_table
.
The country_l10n
table has 4 attributes:
country_l10n.country_code
country_code_alpha2
NOT NULL
FOREIGN KEY (country_code) REFERENCES country(country_code) ON UPDATE CASCADE ON DELETE CASCADE
country_l10n.l10n_lang_code
lang_code_alpha2
NOT NULL
FOREIGN KEY (l10n_lang_code) REFERENCES lang(lang_code) ON UPDATE RESTRICT ON DELETE RESTRICT
country_l10n.l10n_columns_belong_to_pg_xenophile
boolean
NOT NULL
DEFAULT false
country_l10n.name
text
NOT NULL
Views
View: lang_l10n_en
sql
SELECT lang.lang_code, lang.lang_belongs_to_pg_xenophile,
lang_l10n.l10n_lang_code, lang_l10n.l10n_columns_belong_to_pg_xenophile,
lang_l10n.name
FROM lang
LEFT JOIN lang_l10n ON lang.lang_code::text = lang_l10n.lang_code::text AND lang_l10n.l10n_lang_code::text = 'en'::text;
View: country_l10n_en
sql
SELECT country.country_code, country.country_code_alpha3,
country.country_code_num, country.calling_code, country.currency_code,
country.country_belongs_to_pg_xenophile, country_l10n.l10n_lang_code,
country_l10n.l10n_columns_belong_to_pg_xenophile, country_l10n.name
FROM country
LEFT JOIN country_l10n ON country.country_code::text = country_l10n.country_code::text AND country_l10n.l10n_lang_code::text = 'en'::text;
Routines
Procedure: create_l10n_view (name, name, name, lang_code_alpha2, boolean)
Procedure arguments:
| Arg. # | Arg. mode | Argument name | Argument type | Default expression |
| ------ | ---------- | ----------------------------------------------------------------- | -------------------------------------------------------------------- | ------------------- |
| $1
| IN
| table_schema$
| name
| |
| $2
| IN
| base_table$
| name
| |
| $3
| IN
| l10n_table$
| name
| |
| $4
| IN
| lang_code$
| lang_code_alpha2
| |
| $5
| IN
| temp$
| boolean
| false
|
Procedure-local settings:
SET search_path TO xeno, public, pg_temp
Function: fkey_guard (regclass, name, anyelement)
Function arguments:
| Arg. # | Arg. mode | Argument name | Argument type | Default expression |
| ------ | ---------- | ----------------------------------------------------------------- | -------------------------------------------------------------------- | ------------------- |
| $1
| IN
| foreign_table$
| regclass
| |
| $2
| IN
| fkey_column$
| name
| |
| $3
| IN
| fkey_value$
| anyelement
| |
Function return type: anyelement
Function attributes: STABLE
, RETURNS NULL ON NULL INPUT
, PARALLEL SAFE
Function: l10n_table__maintain_l10n_objects()
The l10n_table__maintain_l10n_objects()
trigger function is meant to actuate
changes to the l10_table
to the actual l10n tables and views tracked by that
meta table.
Function return type: trigger
Function-local settings:
SET search_path TO xeno, public, pg_temp
SET pg_xenophile.in_l10n_table_row_trigger TO true
Function: l10n_table__track_alter_table_events()
Function return type: event_trigger
Function attributes: SECURITY DEFINER
Function-local settings:
SET search_path TO xeno, public, pg_temp
SET pg_xenophile.in_l10n_table_event_trigger TO true
Function: l10n_table__track_drop_table_events()
Function return type: event_trigger
Function attributes: SECURITY DEFINER
Function-local settings:
SET search_path TO xeno, public, pg_temp
SET pg_xenophile.in_l10n_table_event_trigger TO true
Function: l10n_table_with_fresh_ddl (l10n_table)
Function arguments:
| Arg. # | Arg. mode | Argument name | Argument type | Default expression |
| ------ | ---------- | ----------------------------------------------------------------- | -------------------------------------------------------------------- | ------------------- |
| $1
| INOUT
| | l10n_table
| |
Function return type: l10n_table
Function attributes: STABLE
Function-local settings:
SET search_path TO xeno, public, pg_temp
Function: pg_xenophile_base_lang_code()
Function return type: lang_code_alpha2
Function attributes: STABLE
, LEAKPROOF
Function-local settings:
SET pg_readme.include_this_routine_definition TO true
SET search_path TO xeno, public, pg_temp
sql
CREATE OR REPLACE FUNCTION xeno.pg_xenophile_base_lang_code()
RETURNS lang_code_alpha2
LANGUAGE sql
STABLE LEAKPROOF
SET "pg_readme.include_this_routine_definition" TO 'true'
SET search_path TO 'xeno', 'public', 'pg_temp'
RETURN (COALESCE(current_setting('app_settings.i18n.base_lang_code'::text, true), current_setting('pg_xenophile.base_lang_code'::text, true), 'en'::text))::lang_code_alpha2
Function: pg_xenophile_meta_pgxn()
Returns the JSON meta data that has to go into the META.json
file needed for
PGXN—PostgreSQL Extension Network packages.
The Makefile
includes a recipe to allow the developer to: make META.json
to
refresh the meta file with the function's current output, including the
default_version
.
And indeed, pg_xenophile
can be found on PGXN:
https://pgxn.org/dist/pg_xenophile/
Function return type: jsonb
Function attributes: STABLE
Function: pg_xenophile_readme()
Generates a README in Markdown format using the amazing power of the
pg_readme
extension. Temporarily installs pg_readme
if it is not already
installed in the current database.
Function return type: text
Function-local settings:
SET search_path TO xeno, public, pg_temp
SET pg_readme.include_view_definitions TO true
SET pg_readme.include_routine_definitions_like TO {test__%}
Function: pg_xenophile_target_lang_codes()
Function return type: lang_code_alpha2[]
Function attributes: STABLE
, LEAKPROOF
Function-local settings:
SET pg_readme.include_this_routine_definition TO true
SET search_path TO xeno, public, pg_temp
sql
CREATE OR REPLACE FUNCTION xeno.pg_xenophile_target_lang_codes()
RETURNS lang_code_alpha2[]
LANGUAGE sql
STABLE LEAKPROOF
SET "pg_readme.include_this_routine_definition" TO 'true'
SET search_path TO 'xeno', 'public', 'pg_temp'
RETURN (COALESCE(current_setting('app.settings.i18n.target_lang_codes'::text, true), current_setting('pg_xenophile.target_lang_codes'::text, true), '{}'::text))::lang_code_alpha2[]
Function: pg_xenophile_user_lang_code()
Function return type: lang_code_alpha2
Function attributes: STABLE
, LEAKPROOF
Function-local settings:
SET pg_readme.include_this_routine_definition TO true
SET search_path TO xeno, public, pg_temp
sql
CREATE OR REPLACE FUNCTION xeno.pg_xenophile_user_lang_code()
RETURNS lang_code_alpha2
LANGUAGE sql
STABLE LEAKPROOF
SET "pg_readme.include_this_routine_definition" TO 'true'
SET search_path TO 'xeno', 'public', 'pg_temp'
RETURN (COALESCE(current_setting('app_settings.i18n.user_lang_code'::text, true), current_setting('pg_xenophile.user_lang_code'::text, true), regexp_replace(current_setting('lc_messages'::text), '^([a-z]{2}).*$'::text, ''::text), 'en'::text))::lang_code_alpha2
Procedure: test__l10n_table()
Procedure-local settings:
SET search_path TO xeno, public, pg_temp
SET pg_readme.include_this_routine_definition TO true
```sql CREATE OR REPLACE PROCEDURE xeno.test__l10n_table() LANGUAGE plpgsql SET search_path TO 'xeno', 'public', 'pg_temp' SET "pg_readme.include_this_routine_definition" TO 'true' AS $procedure$ declare row record; l10ntable l10ntable; begin -- Create the table that will be translated. create table test_uni ( id bigint primary key generated always as identity ,uni_abbr text not null unique -- We need to have more than one non-PK column, to ensure that we're hitting the requirement to agg. -- Also, let's put a space in the column name, so that we're testing proper quoting as well. ,"student rating" bigint default 5 );
<<with_redundant_target_lang>>
begin
-- This tests that the trigger(s) on `l10n_table` tries to create the `_l10n_nl`-suffixed view
-- only once and doesn't crash because of trying to create it twice.
insert into l10n_table
(base_table_name, l10n_column_definitions, base_lang_code, target_lang_codes)
values (
'test_uni'
,array['name TEXT NOT NULL', '"description (short)" TEXT NOT NULL']
,'nl'::lang_code_alpha2 -- Apologies for the Dutch East India Company mentality.
,array['nl']::lang_code_alpha2[]
);
raise transaction_rollback;
exception
when transaction_rollback then
end with_redundant_target_lang;
-- Register `test_uni` with the meta table, to activate all the l10n magic.
insert into l10n_table
(base_table_name, l10n_column_definitions, base_lang_code, target_lang_codes)
values (
'test_uni'
,array['name TEXT NOT NULL', '"description (short)" TEXT NOT NULL']
,'nl'::lang_code_alpha2 -- Apologies for the Dutch East India Company mentality.
,array['en', 'fr']::lang_code_alpha2[]
);
assert to_regclass('test_uni_l10n') is not null,
'The `_l10n` table should have been created as result of the preceding INSERT into the meta table.';
assert (
select
array_agg(pg_class.relname order by pg_class.relname)::name[]
from
pg_catalog.pg_class
where
pg_class.relkind = 'v'
and pg_class.relnamespace = current_schema::regnamespace
and pg_class.relname like 'test\_uni\_l10n\___'
) = array['test_uni_l10n_en', 'test_uni_l10n_fr', 'test_uni_l10n_nl']::name[]
,'3 `_l10n_<lang_code>`-suffixed views should have been created, one for the base language'
|| ' and 2 for the target languages.';
<<upsert_into_l10n_lang_view>>
declare
_nl_expected record := row(
1, 'AX-UNI', 5, 'nl', 'Bijl Universiteit', 'De trainingsleider in bijlonderhoud en gebruik'
)::test_uni_l10n_nl;
_en_expected record := row(
1, 'AX-UNI', 5, 'en', 'Axe University', 'The leader in axe maintenance and usage training'
)::test_uni_l10n_en;
begin
insert into test_uni_l10n_nl
(uni_abbr, name, "description (short)")
values
(_nl_expected.uni_abbr, _nl_expected.name, _nl_expected."description (short)")
returning
*
into
_row
;
assert _row = _nl_expected, format(
'The `RETURNING` clause did not return the data as inserted; %s ≠ %s'
,_row, _nl_expected
);
assert _nl_expected = (select row(tbl.*)::test_uni_l10n_nl from test_uni_l10n_nl as tbl),
'The `RETURNING` clause should have returned the same row data as this separate `SELECT`.';
assert exists(select from test_uni_l10n_en where id = _nl_expected.id),
'Even though the row for English doesn''t exist in `test_uni_l10n` yet, it should exist in the'
' `test_l10n_en` view, with NULL values for all the l10n columns.';
update
test_uni_l10n_en
set
"name" = _en_expected."name"
,"description (short)" = _en_expected."description (short)"
where
id = _nl_expected.id
returning
*
into
_row
;
assert found, 'The `UPDATE` should have found a row to update in the `test_uni_l10n_en` view.';
assert _row = _en_expected,
format('%s ≠ %s; the `RETURNING` clause did not return the data as upserted.', _row, _en_expected);
assert _en_expected = (select row(tbl.*)::test_uni_l10n_en from test_uni_l10n_en as tbl),
'The `RETURNING` clause should have returned the same row data as this separate `SELECT`.';
end upsert_into_l10n_lang_view;
<<try_to_override_generated_pk>>
declare
_expected_id bigint := currval('test_uni_id_seq') + 1;
_nl_expected record := row(
_expected_id, 'SIMP-UNI', 2, 'nl', 'Simpschool', 'Simpen voor Elon en Jeff'
)::test_uni_l10n_nl;
begin
insert into test_uni_l10n_nl
(id, uni_abbr, "student rating", name, "description (short)")
values (
_nl_expected.id
,_nl_expected.uni_abbr
,_nl_expected."student rating"
,_nl_expected.name
,_nl_expected."description (short)"
)
returning
*
into
_row
;
raise assert_failure using
message = 'It should not be possible to explicitly specify a PK value on insert'
'if that PK is `GENERATED ALWAYS`.';
exception
when generated_always then
end try_to_override_generated_pk;
<<insert_instead_of_update_on_missing_l10n_record>>
declare
_expected_id bigint := currval('test_uni_id_seq') + 1;
_nl_expected record := row(
_expected_id, 'PO-UNI', 7, 'nl', 'Poep-Universiteit', 'De Beste Plek om Te Leren Legen'
)::test_uni_l10n_nl;
_en_expected record := row(
_expected_id, 'PO-UNI', 7, 'en', 'Pooversity', 'The Best Place To Empty Yourself'
)::test_uni_l10n_nl;
begin
insert into test_uni_l10n_nl
(uni_abbr, "student rating", name, "description (short)")
values (
_nl_expected.uni_abbr
,_nl_expected."student rating"
,_nl_expected.name
,_nl_expected."description (short)"
)
returning
*
into
_row
;
-- Test that the trigger `test_uni_l10n_en` does an INSERT instead of an UPDATE if no row for this
-- PK + lang_code combo exists yet in `test_uni_l10n`.
update
test_uni_l10n_en
set
uni_abbr = _en_expected.uni_abbr
,name = _en_expected.name
,"description (short)" = _en_expected."description (short)"
where
id = _en_expected.id
returning
*
into
_row
;
assert _row = _en_expected, format('%s ≠ %s', _row, _en_expected);
end insert_instead_of_update_on_missing_l10n_record;
<<delete_via_l10n_view>>
declare
_expected_id bigint := currval('test_uni_id_seq') + 1;
_fr_expected record := row(
_expected_id, 'MOI-UNI', null, 'fr', 'Moiversitee', 'La Premier Bla'
)::test_uni_l10n_fr;
begin
insert into test_uni_l10n_fr
(uni_abbr, name, "description (short)")
values
(_fr_expected.uni_abbr, _fr_expected.name, _fr_expected."description (short)")
returning
*
into
_row
;
delete from test_uni_l10n_fr where id = _row.id;
assert found;
assert not exists (select from test_uni where id = _row.id),
'The base table record should have been deleted.';
assert not exists (
select from test_uni_l10n where id = _row.id and l10n_lang_code = _row.l10n_lang_code
)
,'The l10n record should have been deleted, via the `ON DELETE CASCADE` to the base table.';
end delete_via_l10n_view;
<<trigger_alter_table_events>>
begin
alter table test_uni_l10n
add description2 text;
update test_uni_l10n
set description2 = 'Something to satisfy NOT NULL'; -- Because we want to make it NOT NULL.
alter table test_uni_l10n
alter column description2 set not null;
select * into _l10n_table from l10n_table where base_table_name = 'test_uni';
assert _l10n_table.l10n_column_definitions[3] = 'description2 text NOT NULL',
'The `l10n_table__track_alter_table_events` event trigger should have updated the list of l10n'
' columns.';
assert exists(
select
from pg_attribute
where attrelid = 'test_uni_l10n_fr'::regclass
and attname = 'description2'
), 'The `description2` column should have been added to the view.';
alter table test_uni_l10n
drop column description2
cascade;
select * into _l10n_table from l10n_table where base_table_name = 'test_uni';
assert array_length(_l10n_table.l10n_column_definitions, 1) = 2,
'The dropped column should have been removed from the `l10n_table` meta table.';
assert not exists(
select
from pg_attribute
where attrelid = 'test_uni_l10n_nl'::regclass
and attname = 'description2'
), 'The `description2` column should have disappeared from the views.';
alter table test_uni
add non_l10n_col int
not null
default 6;
select * into _l10n_table from l10n_table where base_table_name = 'test_uni';
assert _l10n_table.base_column_definitions[4] = 'non_l10n_col integer NOT NULL DEFAULT 6',
'The `l10n_table__track_alter_table_events` event trigger should have updated the list of base'
' columns.';
assert (select non_l10n_col from test_uni_l10n_nl where id = 2) = 6;
alter table test_uni
drop column non_l10n_col
cascade;
assert not exists(
select
from pg_attribute
where attrelid = 'test_uni_l10n_nl'::regclass
and attname = 'non_l10n_col'
), 'The `non_l10n_col` column should have disappeared from the views.';
<<add_base_column_with_default_value>>
declare
_nl_expected record;
begin
alter table test_uni
add column base_col_with_default text
not null
default 'I am default.';
alter table test_uni_l10n
add column localized_image text
not null
default 'fallback.png';
select * into _l10n_table from l10n_table where base_table_name = 'test_uni';
assert _l10n_table.base_column_definitions[4]
= 'base_col_with_default text NOT NULL DEFAULT ''I am default.''::text',
format(
'The `l10n_table__track_alter_table_events` event trigger should have updated the list of'
' base columns; base_column_definitions = ''%s'''
,_l10n_table.base_column_definitions
);
assert _l10n_table.l10n_column_definitions[3]
= 'localized_image text NOT NULL DEFAULT ''fallback.png''::text',
format(
'The `l10n_table__track_alter_table_events` event trigger should have updated the list of'
' l10n columns; l10n_column_definitions = ''%s'''
,_l10n_table.l10n_column_definitions
);
-- Now, let's test how the defaults behave on insert…
_nl_expected := row(
1, 'HOF', 5, 'I am default.', 'nl', 'Wim Hof', 'De Ijsman', 'fallback.png'
)::test_uni_l10n_nl;
insert into test_uni_l10n_nl
(uni_abbr, name, "description (short)")
values
(_nl_expected.uni_abbr, _nl_expected.name, _nl_expected."description (short)")
returning
*
into
_row
;
assert _row.base_col_with_default = _nl_expected.base_col_with_default,
'Default should have propegated from the base table to view.';
assert _row.localized_image = _nl_expected.localized_image,
'Default should have propegated from the l10n table to view.';
end add_base_column_with_default_value;
<<drop_base_table>>
begin
drop table test_uni cascade;
assert not exists (select from l10n_table where base_table_name = 'test_uni');
raise transaction_rollback; -- I could have used any error code, but this one seemed to fit best.
exception
when transaction_rollback then
end drop_base_table;
end trigger_alter_table_events;
-- DELETE-ing the meta info for our l10n table should cascade cleanly, without crashing.
delete from l10n_table where base_table_regclass = 'test_uni'::regclass;
assert to_regclass('test_uni_l10n') is null,
'The actual `_l10n` table should have been removed when deleting the meta row from `l10n_table`.';
<<insert_natural_key>>
declare
_expected record;
begin
-- Let's make a table with a natural primary key that is _not_ `GENERATED ALWAYS`.
create table test_species (
scientific_name text
primary key
-- Just so you know: without the `year_first_described` column, the `INSERT INTO l10n_table`
-- would not trigger a certain bug, so please do not allow regressions to occur by removing
-- this column.
,year_first_described int
);
-- Register `test_species` with the meta table, to activate all the l10n magic.
insert into l10n_table
(base_table_name, l10n_column_definitions, base_lang_code)
values
('test_species' ,'{common_name TEXT}' ,'en')
;
insert into test_species_l10n_en
(scientific_name, common_name, year_first_described)
values
('Taraxacum officinale', 'common dandelion', 1753)
;
<<insert_duplicate_natural_key>>
begin
insert into test_species_l10n_en
(scientific_name, common_name)
values
('Taraxacum officinale', 'uncommon dandelion')
;
raise assert_failure using message = 'Duplicating a primary key shouldn''t have been possible.';
exception
when unique_violation then
end insert_duplicate_natural_key;
end insert_natural_key;
raise transaction_rollback; -- I could have used any error code, but this one seemed to fit best.
exception when transaction_rollback then end; $procedure$ ```
Function: updatable_l10_view()
Function return type: trigger
Function-local settings:
SET search_path TO xeno, public, pg_temp
Types
The following extra types have been defined besides the implicit composite types of the tables and views in this extension.
Domain: currency_code
Using this domain instead of its underlying text
type ensures that only
uppercase, 3-letter currency codes are allowed. It does not enforce that the
currency_code
exists in the currency
table.
sql
CREATE DOMAIN currency_code AS text
CHECK ((VALUE ~ '^[A-Z]{3}$'::text));
Domain: country_code_alpha2
Using this domain instead of its underlying text
type ensures that only
2-letter, uppercase country codes are allowed.
sql
CREATE DOMAIN country_code_alpha2 AS text
CHECK ((VALUE ~ '^[A-Z]{2}$'::text));
Domain: lang_code_alpha2
sql
CREATE DOMAIN lang_code_alpha2 AS text
CHECK ((VALUE ~ '^[a-z]{2}$'::text));
Colophon
This README.md
for the pg_xenophile
extension was automatically generated using the pg_readme
PostgreSQL extension.