Localize.Ecto.Postgres (Localize SQL v1.0.0)

Locale-aware query expressions for Ecto on PostgreSQL.

The primary public API is the collate/1 and collate/2 macros, which apply a PostgreSQL COLLATE clause to a query expression — or to a comparison between two expressions — using the ICU collation that best matches a locale. The collation name is resolved by Localize.Ecto.Collation.resolve!/1 and emitted as a quoted identifier via Ecto's identifier/1 fragment, so it is never interpolated into the SQL as text.

Import this module (or import Localize.Ecto.Postgres, only: [collate: 1, collate: 2]) alongside Ecto.Query and use collate/1,2 anywhere a query expression is accepted — order_by, select, where, distinct and so on:

import Ecto.Query
import Localize.Ecto.Postgres

# Collate using the current locale (Localize.get_locale/0)
from p in Product, order_by: collate(p.name)

# Collate using an explicit locale
from p in Product, order_by: collate(p.name, "sv")

# Locale determined at runtime
def sorted_products(locale) do
  from p in Product, order_by: collate(p.name, ^locale)
end

# Collate a comparison: name < 'münchen' under German collation
from p in Product, where: collate(p.name < "münchen", "de"), select: p.name

# Use a collation created in a migration, by name
from p in Product, order_by: collate(p.name, collation: "german_phonebook")

Collations for locales carrying a BCP 47 collation type, such as de-u-co-phonebk, are not preloaded by PostgreSQL. Create them once in a migration with Localize.Ecto.Migration.create_collation/2 and they resolve automatically thereafter.

Localize.Ecto exposes the same macros for backwards compatibility. The SQLite equivalents are in Localize.Ecto.SQLite3.

Summary

Functions

at_time_zone(expression, zone)

Applies PostgreSQL's AT TIME ZONE with a validated time zone.

collate(expression)

Applies a COLLATE clause for the current locale.

collate(expression, locale_or_options)

Applies a COLLATE clause for the given locale or collation.

initcap(expression)

Locale-aware initcap() using the collation of the current locale.

initcap(expression, locale_or_options)

Locale-aware initcap(). See lower/2 for the arguments and semantics.

lower(expression)

Locale-aware lower() using the collation of the current locale.

lower(expression, locale_or_options)

Locale-aware lower().

ts_match(expression, query)

Locale-aware full-text search match using the current locale.

ts_match(expression, query, locale_or_options)

Locale-aware full-text search match.

upper(expression)

Locale-aware upper() using the collation of the current locale.

upper(expression, locale_or_options)

Locale-aware upper(). See lower/2 for the arguments and semantics.

Functions

at_time_zone(expression, zone)

(macro)

Applies PostgreSQL's AT TIME ZONE with a validated time zone.

The zone is canonicalized by Localize.Ecto.Type.TimeZone.canonicalize!/1 when the query is built, so an alias or BCP 47 short zone identifier is accepted and an unknown zone raises in the application instead of failing on the server.

Arguments

expression is an Ecto query expression that evaluates to a timestamp.
zone is a canonical IANA name, a CLDR-known alias, or a BCP 47 short zone identifier. A pinned expression (^zone) is also accepted.

Returns

A query fragment expression AT TIME ZONE 'zone'.

Examples

iex> import Ecto.Query
iex> query = from e in "events", select: at_time_zone(e.starts_at, "Australia/Sydney")
iex> match?(%Ecto.Query{}, query)
true

collate(expression)

(macro)

Applies a COLLATE clause for the current locale.

The collation is resolved from Localize.get_locale/0 at the time the query is built.

Arguments

expression is any Ecto query expression that evaluates to a string value, or a comparison (<, <=, >, >=, ==, !=) between two such expressions.

Returns

A query fragment expression COLLATE "collation", or for a comparison left OP right COLLATE "collation".

Examples

iex> import Ecto.Query
iex> query = from p in "products", order_by: collate(p.name), select: p.name
iex> match?(%Ecto.Query{}, query)
true

collate(expression, locale_or_options)

(macro)

Applies a COLLATE clause for the given locale or collation.

Arguments

expression is any Ecto query expression that evaluates to a string value, or a comparison (<, <=, >, >=, ==, !=) between two such expressions.
locale_or_options is a Localize.LanguageTag.t/0, any locale identifier accepted by Localize.validate_locale/1, or a keyword list of options. A pinned expression (^locale) is also accepted, so runtime locale values read naturally in query syntax.

Options

:collation is a collation name used verbatim, bypassing locale resolution — for example a collation created with Localize.Ecto.Migration.create_collation/2 under a custom name.
Any other options are passed to Localize.Ecto.Collation.collation_for!/2.

Returns

A query fragment expression COLLATE "collation", or for a comparison left OP right COLLATE "collation".

Examples

iex> import Ecto.Query
iex> query = from p in "products", order_by: collate(p.name, "sv"), select: p.name
iex> match?(%Ecto.Query{}, query)
true

iex> import Ecto.Query
iex> query = from p in "products", select: collate(p.name < p.description, "de")
iex> match?(%Ecto.Query{}, query)
true

initcap(expression)

(macro)

Locale-aware initcap() using the collation of the current locale.

See lower/2.

initcap(expression, locale_or_options)

(macro)

Locale-aware initcap(). See lower/2 for the arguments and semantics.

lower(expression)

(macro)

Locale-aware lower() using the collation of the current locale.

See lower/2.

lower(expression, locale_or_options)

(macro)

Locale-aware lower().

PostgreSQL's lower(), upper() and initcap() follow the collation of their argument, so the locale determines the case mapping — under a Turkish collation lower("I") is the dotless "ı", which the default collation gets wrong.

Arguments

expression is an Ecto query expression that evaluates to a string.
locale_or_options is a locale or keyword list as accepted by collate/2. A pinned expression (^locale) is also accepted.

Returns

A query fragment lower(expression COLLATE "collation").

Examples

iex> import Ecto.Query
iex> query = from p in "products", select: lower(p.name, "tr")
iex> match?(%Ecto.Query{}, query)
true

ts_match(expression, query)

(macro)

Locale-aware full-text search match using the current locale.

See ts_match/3.

ts_match(expression, query, locale_or_options)

(macro)

Locale-aware full-text search match.

Expands to to_tsvector(config, expression) @@ websearch_to_tsquery(config, query) where config is the PostgreSQL text search configuration Localize.Ecto.TextSearch.config_for!/2 resolves for the locale — 'german' for "de-AT", 'simple' for languages PostgreSQL has no stemmer for.

Arguments

expression is an Ecto query expression that evaluates to the searched text.
query is the user's search input, in websearch_to_tsquery syntax.
locale_or_options is a locale accepted by Localize.Ecto.TextSearch.config_for!/2, or a keyword list with a :config option naming a text search configuration directly. A pinned expression (^locale) is also accepted.

Returns

A boolean query fragment for use in where.

Examples

iex> import Ecto.Query
iex> query = from p in "products", where: ts_match(p.description, "wooden chair", "de"), select: p.id
iex> match?(%Ecto.Query{}, query)
true

upper(expression)

(macro)

Locale-aware upper() using the collation of the current locale.

See lower/2.

upper(expression, locale_or_options)

(macro)

Locale-aware upper(). See lower/2 for the arguments and semantics.