# `Localize.Ecto.Postgres`
[🔗](https://github.com/elixir-localize/localize_sql/blob/v1.0.1/lib/localize/ecto/postgres.ex#L1)

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`.

# `at_time_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`
*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`
*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 `t: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`
*macro* 

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

See `lower/2`.

# `initcap`
*macro* 

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

# `lower`
*macro* 

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

See `lower/2`.

# `lower`
*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`
*macro* 

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

See `ts_match/3`.

# `ts_match`
*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`
*macro* 

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

See `lower/2`.

# `upper`
*macro* 

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

---

*Consult [api-reference.md](api-reference.md) for complete listing*