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

Generates the SQL that installs a tagged decimal type in PostgreSQL.

A tagged decimal — described by a `t:Localize.Ecto.TaggedDecimal.t/0`
specification — is stored as a composite type pairing a tag with a
decimal, such as `money_with_currency(currency_code, amount)` or
`cldr_unit(unit, value)`. This module returns the SQL creating that
type together with the aggregate functions and operators that make it
usable in queries.

Aggregates over a tagged decimal are *tag-guarded*: summing a column
whose rows carry different tags raises rather than returning a
meaningless number. The generated functions compare the tag of each
incoming row against the tag accumulated so far and raise SQLSTATE
`22033` (`invalid_parameter_value`) on a mismatch, so a query that adds
euros to yen fails in the database rather than silently succeeding.

The SQL is generated from the specification rather than kept as static
files so that one implementation serves every domain. The names of the
composite type, its fields and the generated functions come from the
specification, because a deployed database already contains them and an
aggregate must name the same fields as the type it was created against.

### Statements

Each function returns one or more SQL statements in a single string,
separated by two blank lines. `execute/1,2` and `execute_each/1,2`
wrap them for use in an Ecto migration — `execute_each/1,2` emits one
`execute` per statement, which is what a migration needs when the
statements must run in order.

### Examples

    iex> spec = Localize.Ecto.TaggedDecimal.new!(
    ...>   type_name: "cldr_unit", tag_field: "unit", value_field: "value",
    ...>   function_prefix: "unit", tag_description: "unit name"
    ...> )
    iex> Localize.Ecto.TaggedDecimal.DDL.create_type(spec)
    "CREATE TYPE public.cldr_unit AS (unit varchar, value numeric);"

# `create_type`

```elixir
@spec create_type(Localize.Ecto.TaggedDecimal.t()) :: String.t()
```

Returns the SQL creating the composite type.

### Arguments

* `spec` is a `t:Localize.Ecto.TaggedDecimal.t/0`.

### Returns

* The SQL statement as a string.

### Examples

    iex> spec = Localize.Ecto.TaggedDecimal.new!(
    ...>   type_name: "money_with_currency", tag_field: "currency_code",
    ...>   value_field: "amount", function_prefix: "money",
    ...>   tag_description: "currency code"
    ...> )
    iex> Localize.Ecto.TaggedDecimal.DDL.create_type(spec)
    "CREATE TYPE public.money_with_currency AS (currency_code varchar, amount numeric);"

# `define_avg_function`

```elixir
@spec define_avg_function(Localize.Ecto.TaggedDecimal.t()) :: String.t()
```

Returns the SQL defining a tag-guarded `avg` aggregate.

The aggregate accumulates into an intermediate composite type holding
a running sum and count, which the final function divides. That type is
created by this SQL and removed by `drop_avg_function/1`.

### Arguments

* `spec` is a `t:Localize.Ecto.TaggedDecimal.t/0`.

### Returns

* The SQL statements as a string, separated by two blank lines.

### Examples

    iex> spec = Localize.Ecto.TaggedDecimal.new!(
    ...>   type_name: "cldr_unit", tag_field: "unit", value_field: "value",
    ...>   function_prefix: "unit", tag_description: "unit name"
    ...> )
    iex> Localize.Ecto.TaggedDecimal.DDL.define_avg_function(spec) =~ "CREATE TYPE public.unit_avg_state"
    true

# `define_minmax_functions`

```elixir
@spec define_minmax_functions(Localize.Ecto.TaggedDecimal.t()) :: String.t()
```

Returns the SQL defining tag-guarded `min` and `max` aggregates.

### Arguments

* `spec` is a `t:Localize.Ecto.TaggedDecimal.t/0`.

### Returns

* The SQL statements as a string, separated by two blank lines.

### Examples

    iex> spec = Localize.Ecto.TaggedDecimal.new!(
    ...>   type_name: "cldr_unit", tag_field: "unit", value_field: "value",
    ...>   function_prefix: "unit", tag_description: "unit name"
    ...> )
    iex> Localize.Ecto.TaggedDecimal.DDL.define_minmax_functions(spec) =~ "AGGREGATE min(cldr_unit)"
    true

# `define_operator`

```elixir
@spec define_operator(Localize.Ecto.TaggedDecimal.t(), :add | :subtract | :negate) ::
  String.t()
```

Returns the SQL defining a tag-guarded operator over the type.

### Arguments

* `spec` is a `t:Localize.Ecto.TaggedDecimal.t/0`.

* `operator` is `:add`, `:subtract` or `:negate`. `:negate` defines a
  unary `-`; the others define the binary `+` and `-`.

### Returns

* The SQL statements as a string, separated by two blank lines.

### Examples

    iex> spec = Localize.Ecto.TaggedDecimal.new!(
    ...>   type_name: "cldr_unit", tag_field: "unit", value_field: "value",
    ...>   function_prefix: "unit", tag_description: "unit name"
    ...> )
    iex> Localize.Ecto.TaggedDecimal.DDL.define_operator(spec, :add) =~ "CREATE OPERATOR + ("
    true

# `define_sum_function`

```elixir
@spec define_sum_function(Localize.Ecto.TaggedDecimal.t()) :: String.t()
```

Returns the SQL defining a tag-guarded `sum` aggregate.

Three statements: the state function, the combine function that makes
the aggregate safe under parallel query, and the aggregate itself.

### Arguments

* `spec` is a `t:Localize.Ecto.TaggedDecimal.t/0`.

### Returns

* The SQL statements as a string, separated by two blank lines.

### Examples

    iex> spec = Localize.Ecto.TaggedDecimal.new!(
    ...>   type_name: "cldr_unit", tag_field: "unit", value_field: "value",
    ...>   function_prefix: "unit", tag_description: "unit name"
    ...> )
    iex> Localize.Ecto.TaggedDecimal.DDL.define_sum_function(spec) =~ "CREATE OR REPLACE AGGREGATE sum(cldr_unit)"
    true

# `drop_avg_function`

```elixir
@spec drop_avg_function(Localize.Ecto.TaggedDecimal.t()) :: String.t()
```

Returns the SQL dropping the `avg` aggregate, its functions and its
intermediate state type.

### Arguments

* `spec` is a `t:Localize.Ecto.TaggedDecimal.t/0`.

### Returns

* The SQL statements as a string, separated by two blank lines.

### Examples

    iex> spec = Localize.Ecto.TaggedDecimal.new!(
    ...>   type_name: "cldr_unit", tag_field: "unit", value_field: "value",
    ...>   function_prefix: "unit", tag_description: "unit name"
    ...> )
    iex> Localize.Ecto.TaggedDecimal.DDL.drop_avg_function(spec) =~ "DROP TYPE IF EXISTS public.unit_avg_state;"
    true

# `drop_minmax_functions`

```elixir
@spec drop_minmax_functions(Localize.Ecto.TaggedDecimal.t()) :: String.t()
```

Returns the SQL dropping the `min` and `max` aggregates and their
functions.

### Arguments

* `spec` is a `t:Localize.Ecto.TaggedDecimal.t/0`.

### Returns

* The SQL statements as a string, separated by two blank lines.

### Examples

    iex> spec = Localize.Ecto.TaggedDecimal.new!(
    ...>   type_name: "cldr_unit", tag_field: "unit", value_field: "value",
    ...>   function_prefix: "unit", tag_description: "unit name"
    ...> )
    iex> Localize.Ecto.TaggedDecimal.DDL.drop_minmax_functions(spec) =~ "DROP AGGREGATE IF EXISTS max(cldr_unit);"
    true

# `drop_operator`

```elixir
@spec drop_operator(Localize.Ecto.TaggedDecimal.t(), :add | :subtract | :negate) ::
  String.t()
```

Returns the SQL dropping an operator over the type and its function.

### Arguments

* `spec` is a `t:Localize.Ecto.TaggedDecimal.t/0`.

* `operator` is `:add`, `:subtract` or `:negate`.

### Returns

* The SQL statements as a string, separated by two blank lines.

### Examples

    iex> spec = Localize.Ecto.TaggedDecimal.new!(
    ...>   type_name: "cldr_unit", tag_field: "unit", value_field: "value",
    ...>   function_prefix: "unit", tag_description: "unit name"
    ...> )
    iex> Localize.Ecto.TaggedDecimal.DDL.drop_operator(spec, :negate)
    "DROP OPERATOR IF EXISTS - (NONE, cldr_unit);\n\n\nDROP FUNCTION IF EXISTS unit_negate(cldr_unit);"

# `drop_sum_function`

```elixir
@spec drop_sum_function(Localize.Ecto.TaggedDecimal.t()) :: String.t()
```

Returns the SQL dropping the `sum` aggregate and its functions.

### Arguments

* `spec` is a `t:Localize.Ecto.TaggedDecimal.t/0`.

### Returns

* The SQL statements as a string, separated by two blank lines.

### Examples

    iex> spec = Localize.Ecto.TaggedDecimal.new!(
    ...>   type_name: "cldr_unit", tag_field: "unit", value_field: "value",
    ...>   function_prefix: "unit", tag_description: "unit name"
    ...> )
    iex> Localize.Ecto.TaggedDecimal.DDL.drop_sum_function(spec) =~ "DROP AGGREGATE IF EXISTS sum(cldr_unit);"
    true

# `drop_type`

```elixir
@spec drop_type(Localize.Ecto.TaggedDecimal.t()) :: String.t()
```

Returns the SQL dropping the composite type.

### Arguments

* `spec` is a `t:Localize.Ecto.TaggedDecimal.t/0`.

### Returns

* The SQL statement as a string.

### Examples

    iex> spec = Localize.Ecto.TaggedDecimal.new!(
    ...>   type_name: "cldr_unit", tag_field: "unit", value_field: "value",
    ...>   function_prefix: "unit", tag_description: "unit name"
    ...> )
    iex> Localize.Ecto.TaggedDecimal.DDL.drop_type(spec)
    "DROP TYPE public.cldr_unit;"

# `execute`

```elixir
@spec execute(String.t(), String.t()) :: String.t()
```

Wraps SQL as a single `execute` call for an Ecto migration.

### Arguments

* `sql` is a SQL string.

* `append` is a string appended inside the generated `execute` call,
  such as a pipe into a migration-time adjustment. The default is `""`.

### Returns

* The `execute` call as a string.

### Examples

    iex> Localize.Ecto.TaggedDecimal.DDL.execute("DROP TYPE public.cldr_unit;")
    "execute \"DROP TYPE public.cldr_unit;\""

# `execute_each`

```elixir
@spec execute_each(String.t(), String.t()) :: String.t()
```

Wraps each statement of `sql` as its own `execute` call.

Statements are separated by two blank lines, as the generating
functions of this module emit them.

### Arguments

* `sql` is a SQL string possibly containing several statements.

* `append` is a string appended inside each generated `execute` call.
  The default is `""`.

### Returns

* The `execute` calls as a string, one per statement.

### Examples

    iex> spec = Localize.Ecto.TaggedDecimal.new!(
    ...>   type_name: "cldr_unit", tag_field: "unit", value_field: "value",
    ...>   function_prefix: "unit", tag_description: "unit name"
    ...> )
    iex> sql = Localize.Ecto.TaggedDecimal.DDL.drop_sum_function(spec)
    iex> Localize.Ecto.TaggedDecimal.DDL.execute_each(sql) |> String.split("\n") |> length()
    3

---

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