Selecto.Advanced.CaseExpression (Selecto v0.4.5)

Copy Markdown

CASE expression support for PostgreSQL conditional logic.

Provides comprehensive support for both simple and searched CASE expressions, enabling conditional data transformation within SELECT clauses.

Examples

# Simple CASE expression
selecto
|> Selecto.select([
    "film.title",
    {:case, "film.rating",
      when: [
        {"G", "General Audience"},
        {"PG", "Parental Guidance"},
        {"PG-13", "Parents Strongly Cautioned"},
        {"R", "Restricted"}
      ],
      else: "Not Rated",
      as: "rating_description"
    }
  ])

# Searched CASE expression
selecto
|> Selecto.select([
    "customer.first_name",
    {:case_when, [
        {[{"payment_total", {:>, 100}}], "Premium"},
        {[{"payment_total", {:between, 50, 100}}], "Standard"},
        {[{"payment_total", {:>, 0}}], "Basic"}
      ],
      else: "No Purchases",
      as: "customer_tier"
    }
  ])

Summary

Functions

create_searched_case(when_clauses, opts \\ [])

Create a searched CASE expression specification.

create_simple_case(column, when_clauses, opts \\ [])

Create a simple CASE expression specification.

validate_case(spec)

Validate a CASE expression specification.

Functions

create_searched_case(when_clauses, opts \\ [])

Create a searched CASE expression specification.

Parameters

  • when_clauses - List of {conditions, result} tuples
  • opts - Options including :else, :as

Examples

# Searched CASE with multiple conditions
CaseExpression.create_searched_case([
  {[{"payment_total", {:>, 100}}], "Premium"},
  {[{"payment_total", {:between, 50, 100}}], "Standard"},
  {[{"payment_total", {:>, 0}}], "Basic"}
], else: "No Purchases", as: "customer_tier")

create_simple_case(column, when_clauses, opts \\ [])

Create a simple CASE expression specification.

Parameters

  • column - Column to test against
  • when_clauses - List of {value, result} tuples
  • opts - Options including :else, :as

Examples

# Simple CASE with alias
CaseExpression.create_simple_case("film.rating", [
  {"G", "General Audience"},
  {"PG", "Parental Guidance"},
  {"R", "Restricted"}
], else: "Not Rated", as: "rating_description")

validate_case(spec)

Validate a CASE expression specification.

Ensures the CASE expression structure is valid and all conditions are properly formed.