Selecto v0.3.12

Selecto.Advanced.CaseExpression (Selecto v0.3.12)

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

 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"
    }
  ])

Link to this section 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.

Link to this section Functions

Link to this function

create_searched_case(when_clauses, opts \\ [])

Create a searched CASE expression specification.

parameters

 Parameters

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

examples

 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")
Link to this function

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

Create a simple CASE expression specification.

parameters

 Parameters

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

examples

 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")
Link to this function

validate_case(spec)

Validate a CASE expression specification.

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