API Reference

render()

FunSQL.renderMethod
render(node; tables = Dict{Symbol, SQLTable}(),
             dialect = :default,
             cache = nothing)::SQLString

Create a SQLCatalog object and serialize the query node.

FunSQL.renderMethod
render(catalog::Union{SQLConnection, SQLCatalog}, node::SQLNode)::SQLString

Serialize the query node as a SQL statement.

Parameter catalog of SQLCatalog type encapsulates available database tables and the target SQL dialect. A SQLConnection object is also accepted.

Parameter node is a composite SQLNode object.

The function returns a SQLString value. The result is also cached (with the identity of node serving as the key) in the catalog cache.

Examples

julia> catalog = SQLCatalog(
           :person => SQLTable(:person, columns = [:person_id, :year_of_birth]),
           dialect = :postgresql);

julia> q = From(:person) |>
           Where(Get.year_of_birth .>= 1950);

julia> print(render(catalog, q))
SELECT
  "person_1"."person_id",
  "person_1"."year_of_birth"
FROM "person" AS "person_1"
WHERE ("person_1"."year_of_birth" >= 1950)
FunSQL.renderMethod
render(dialect::Union{SQLConnection, SQLCatalog, SQLDialect},
       clause::SQLClause)::SQLString

Serialize the syntax tree of a SQL query.

reflect()

FunSQL.reflectMethod
reflect(conn;
        schema = nothing,
        dialect = nothing,
        cache = 256)::SQLCatalog

Retrieve the information about available database tables.

The function returns a SQLCatalog object. The catalog will be populated with the tables from the given database schema, or, if parameter schema is not set, from the default database schema (e.g., schema public for PostgreSQL).

Parameter dialect specifies the target SQLDialect. If not set, dialect will be inferred from the type of the connection object.

SQLConnection and SQLStatement

FunSQL.SQLConnectionType
SQLConnection(conn; catalog)

Wrap a raw database connection object together with a SQLCatalog object containing information about database tables.

FunSQL.SQLStatementType
SQLStatement(conn, raw; vars = Symbol[])

Wrap a prepared SQL statement.

DBInterface.connectMethod
DBInterface.connect(DB{RawConnType},
                    args...;
                    schema = nothing,
                    dialect = nothing,
                    cache = 256,
                    kws...)

Connect to the database server, call reflect to retrieve the information about available tables and return a SQLConnection object.

Extra parameters args and kws are passed to the call:

DBInterface.connect(RawConnType, args...; kws...)
DBInterface.executeMethod
DBInterface.execute(conn::SQLConnection, sql::SQLNode, params)
DBInterface.execute(conn::SQLConnection, sql::SQLClause, params)

Serialize and execute the query node.

DBInterface.executeMethod
DBInterface.execute(conn::SQLConnection, sql::SQLNode; params...)
DBInterface.execute(conn::SQLConnection, sql::SQLClause; params...)

Serialize and execute the query node.

DBInterface.executeMethod
DBInterface.execute(stmt::SQLStatement, params)

Execute the prepared SQL statement.

DBInterface.prepareMethod
DBInterface.prepare(conn::SQLConnection, str::SQLString)::SQLStatement

Generate a prepared SQL statement.

DBInterface.prepareMethod
DBInterface.prepare(conn::SQLConnection, sql::SQLNode)::SQLStatement
DBInterface.prepare(conn::SQLConnection, sql::SQLClause)::SQLStatement

Serialize the query node and return a prepared SQL statement.

SQLCatalog and SQLTable

FunSQL.SQLCatalogType
SQLCatalog(; tables = Dict{Symbol, SQLTable}(),
             dialect = :default,
             cache = 256)
SQLCatalog(tables...; dialect = :default, cache = 256)

SQLCatalog encapsulates available database tables, the target SQL dialect, and a cache of serialized queries.

Parameter tables is either a dictionary or a vector of SQLTable objects, where the vector will be converted to a dictionary with table names as keys. A table in the catalog can be included to a query using the From node.

Parameter dialect is a SQLDialect object describing the target SQL dialect.

Parameter cache specifies the size of the LRU cache containing results of the render function. Set cache to nothing to disable the cache, or set cache to an arbitrary Dict-like object to provide a custom cache implementation.

Examples

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth, :location_id]);

julia> location = SQLTable(:location, columns = [:location_id, :state]);

julia> catalog = SQLCatalog(person, location, dialect = :postgresql)
SQLCatalog(:location => SQLTable(:location, columns = [:location_id, :state]),
           :person =>
               SQLTable(:person,
                        columns = [:person_id, :year_of_birth, :location_id]),
           dialect = SQLDialect(:postgresql))
FunSQL.SQLTableType
SQLTable(; qualifiers = [], name, columns)
SQLTable(name; qualifiers = [], columns)
SQLTable(name, columns...; qualifiers = [])

The structure of a SQL table or a table-like entity (TEMP TABLE, VIEW, etc) for use as a reference in assembling SQL queries.

The SQLTable constructor expects the table name, a vector columns of column names, and, optionally, a vector containing the name of the table schema and other qualifiers. A name can be a Symbol or a String value.

Examples

julia> person = SQLTable(qualifiers = ["public"],
                         name = "person",
                         columns = ["person_id", "year_of_birth"])
SQLTable(:person,
         qualifiers = [:public],
         columns = [:person_id, :year_of_birth])

SQLDialect

FunSQL.SQLDialectType
SQLDialect(; name = :default, kws...)
SQLDialect(template::SQLDialect; kws...)
SQLDialect(name::Symbol, kws...)
SQLDialect(ConnType::Type)

Properties and capabilities of a particular SQL dialect.

Use SQLDialect(name::Symbol) to create one of the known dialects. The following names are recognized:

  • :mysql
  • :postgresql
  • :redshift
  • :spark
  • :sqlite
  • :sqlserver

Keyword parameters override individual properties of a dialect. For details, check the source code.

Use SQLDialect(ConnType::Type) to detect the dialect based on the type of the database connection object. The following types are recognized:

  • LibPQ.Connection
  • MySQL.Connection
  • SQLite.DB

Examples

julia> postgresql_dialect = SQLDialect(:postgresql)
SQLDialect(:postgresql)

julia> postgresql_odbc_dialect = SQLDialect(:postgresql,
                                            variable_prefix = '?',
                                            variable_style = :positional)
SQLDialect(:postgresql, variable_prefix = '?', variable_style = :POSITIONAL)

SQLString

FunSQL.SQLStringType
SQLString(raw, vars = Symbol[])

Serialized SQL query.

Parameter vars is a vector of query parameters (created with Var) in the order they are expected by the DBInterface.execute() function.

Examples

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(person);

julia> render(q)
SQLString("""
          SELECT
            "person_1"."person_id",
            "person_1"."year_of_birth"
          FROM "person" AS "person_1\"""")

julia> q = From(person) |> Where(Fun.and(Get.year_of_birth .>= Var.YEAR,
                                         Get.year_of_birth .< Var.YEAR .+ 10));

julia> render(q, dialect = :mysql)
SQLString("""
          SELECT
            `person_1`.`person_id`,
            `person_1`.`year_of_birth`
          FROM `person` AS `person_1`
          WHERE
            (`person_1`.`year_of_birth` >= ?) AND
            (`person_1`.`year_of_birth` < (? + 10))""",
          vars = [:YEAR, :YEAR])

julia> render(q, dialect = :postgresql)
SQLString("""
          SELECT
            "person_1"."person_id",
            "person_1"."year_of_birth"
          FROM "person" AS "person_1"
          WHERE
            ("person_1"."year_of_birth" >= $1) AND
            ("person_1"."year_of_birth" < ($1 + 10))""",
          vars = [:YEAR])
FunSQL.packFunction
pack(sql::SQLString, vars::Union{Dict, NamedTuple})::Vector{Any}

Convert a dictionary or a named tuple of query parameters to the positional form expected by DBInterface.execute().

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(person) |> Where(Fun.and(Get.year_of_birth .>= Var.YEAR,
                                         Get.year_of_birth .< Var.YEAR .+ 10));

julia> sql = render(q, dialect = :mysql);

julia> pack(sql, (; YEAR = 1950))
2-element Vector{Any}:
 1950
 1950

julia> sql = render(q, dialect = :postgresql);

julia> pack(sql, (; YEAR = 1950))
1-element Vector{Any}:
 1950

SQLNode

FunSQL.@funsqlMacro

Convenient notation for assembling FunSQL queries.

Agg

FunSQL.AggMethod
Agg(; over = nothing, name, args = [], filter = nothing)
Agg(name; over = nothing, args = [], filter = nothing)
Agg(name, args...; over = nothing, filter = nothing)
Agg.name(args...; over = nothing, filter = nothing)

An application of an aggregate function.

An Agg node must be applied to the output of a Group or a Partition node. In a Group context, it is translated to a regular aggregate function, and in a Partition context, it is translated to a window function.

Examples

Number of patients per year of birth.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(:person) |>
           Group(Get.year_of_birth) |>
           Select(Get.year_of_birth, Agg.count());

julia> print(render(q, tables = [person]))
SELECT
  "person_1"."year_of_birth",
  count(*) AS "count"
FROM "person" AS "person_1"
GROUP BY "person_1"."year_of_birth"

Number of distinct states among all available locations.

julia> location = SQLTable(:location, columns = [:location_id, :state]);

julia> q = From(:location) |>
           Group() |>
           Select(Agg.count_distinct(Get.state));

julia> print(render(q, tables = [location]))
SELECT count(DISTINCT "location_1"."state") AS "count_distinct"
FROM "location" AS "location_1"

For each patient, show the date of their latest visit to a healthcare provider.

julia> person = SQLTable(:person, columns = [:person_id]);

julia> visit_occurrence =
           SQLTable(:visit_occurrence, columns = [:visit_occurrence_id, :person_id, :visit_start_date]);

julia> q = From(:person) |>
           LeftJoin(:visit_group => From(:visit_occurrence) |>
                                    Group(Get.person_id),
                    on = (Get.person_id .== Get.visit_group.person_id)) |>
           Select(Get.person_id,
                  :max_visit_start_date =>
                      Get.visit_group |> Agg.max(Get.visit_start_date));

julia> print(render(q, tables = [person, visit_occurrence]))
SELECT
  "person_1"."person_id",
  "visit_group_1"."max" AS "max_visit_start_date"
FROM "person" AS "person_1"
LEFT JOIN (
  SELECT
    max("visit_occurrence_1"."visit_start_date") AS "max",
    "visit_occurrence_1"."person_id"
  FROM "visit_occurrence" AS "visit_occurrence_1"
  GROUP BY "visit_occurrence_1"."person_id"
) AS "visit_group_1" ON ("person_1"."person_id" = "visit_group_1"."person_id")

For each visit, show the number of days passed since the previous visit.

julia> visit_occurrence =
           SQLTable(:visit_occurrence, columns = [:visit_occurrence_id, :person_id, :visit_start_date]);

julia> q = From(:visit_occurrence) |>
           Partition(Get.person_id,
                     order_by = [Get.visit_start_date]) |>
           Select(Get.person_id,
                  Get.visit_start_date,
                  :gap => Get.visit_start_date .- Agg.lag(Get.visit_start_date));

julia> print(render(q, tables = [visit_occurrence]))
SELECT
  "visit_occurrence_1"."person_id",
  "visit_occurrence_1"."visit_start_date",
  ("visit_occurrence_1"."visit_start_date" - (lag("visit_occurrence_1"."visit_start_date") OVER (PARTITION BY "visit_occurrence_1"."person_id" ORDER BY "visit_occurrence_1"."visit_start_date"))) AS "gap"
FROM "visit_occurrence" AS "visit_occurrence_1"

Append

FunSQL.AppendMethod
Append(; over = nothing, args)
Append(args...; over = nothing)

Append concatenates input datasets.

Only the columns that are present in every input dataset will be included to the output of Append.

An Append node is translated to a UNION ALL query:

SELECT ...
FROM $over
UNION ALL
SELECT ...
FROM $(args[1])
UNION ALL
...

Examples

Show the dates of all measuments and observations.

julia> measurement = SQLTable(:measurement, columns = [:measurement_id, :person_id, :measurement_date]);

julia> observation = SQLTable(:observation, columns = [:observation_id, :person_id, :observation_date]);

julia> q = From(:measurement) |>
           Define(:date => Get.measurement_date) |>
           Append(From(:observation) |>
                  Define(:date => Get.observation_date));

julia> print(render(q, tables = [measurement, observation]))
SELECT
  "measurement_1"."person_id",
  "measurement_1"."measurement_date" AS "date"
FROM "measurement" AS "measurement_1"
UNION ALL
SELECT
  "observation_1"."person_id",
  "observation_1"."observation_date" AS "date"
FROM "observation" AS "observation_1"

As

FunSQL.AsMethod
As(; over = nothing, name)
As(name; over = nothing)
name => over

In a scalar context, As specifies the name of the output column. When applied to tabular data, As wraps the data in a nested record.

The arrow operator (=>) is a shorthand notation for As.

Examples

Show all patient IDs.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(:person) |> Select(:id => Get.person_id);

julia> print(render(q, tables = [person]))
SELECT "person_1"."person_id" AS "id"
FROM "person" AS "person_1"

Show all patients together with their state of residence.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth, :location_id]);

julia> location = SQLTable(:location, columns = [:location_id, :state]);

julia> q = From(:person) |>
           Join(From(:location) |> As(:location),
                on = Get.location_id .== Get.location.location_id) |>
           Select(Get.person_id, Get.location.state);

julia> print(render(q, tables = [person, location]))
SELECT
  "person_1"."person_id",
  "location_1"."state"
FROM "person" AS "person_1"
JOIN "location" AS "location_1" ON ("person_1"."location_id" = "location_1"."location_id")

Bind

FunSQL.BindMethod
Bind(; over = nothing; args)
Bind(args...; over = nothing)

The Bind node evaluates a query with parameters. Specifically, Bind provides the values for Var parameters contained in the over node.

In a scalar context, the Bind node is translated to a correlated subquery. When Bind is applied to the joinee branch of a Join node, it is translated to a JOIN LATERAL query.

Examples

Show patients with at least one visit to a heathcare provider.

julia> person = SQLTable(:person, columns = [:person_id]);

julia> visit_occurrence = SQLTable(:visit_occurrence, columns = [:visit_occurrence_id, :person_id]);

julia> q = From(:person) |>
           Where(Fun.exists(From(:visit_occurrence) |>
                            Where(Get.person_id .== Var.PERSON_ID) |>
                            Bind(:PERSON_ID => Get.person_id)));

julia> print(render(q, tables = [person, visit_occurrence]))
SELECT "person_1"."person_id"
FROM "person" AS "person_1"
WHERE (EXISTS (
  SELECT NULL AS "_"
  FROM "visit_occurrence" AS "visit_occurrence_1"
  WHERE ("visit_occurrence_1"."person_id" = "person_1"."person_id")
))

Show all patients together with the date of their latest visit to a heathcare provider.

julia> person = SQLTable(:person, columns = [:person_id]);

julia> visit_occurrence =
           SQLTable(:visit_occurrence, columns = [:visit_occurrence_id, :person_id, :visit_start_date]);

julia> q = From(:person) |>
           LeftJoin(From(:visit_occurrence) |>
                    Where(Get.person_id .== Var.PERSON_ID) |>
                    Order(Get.visit_start_date |> Desc()) |>
                    Limit(1) |>
                    Bind(:PERSON_ID => Get.person_id) |>
                    As(:visit),
                    on = true) |>
            Select(Get.person_id, Get.visit.visit_start_date);

julia> print(render(q, tables = [person, visit_occurrence]))
SELECT
  "person_1"."person_id",
  "visit_1"."visit_start_date"
FROM "person" AS "person_1"
LEFT JOIN LATERAL (
  SELECT "visit_occurrence_1"."visit_start_date"
  FROM "visit_occurrence" AS "visit_occurrence_1"
  WHERE ("visit_occurrence_1"."person_id" = "person_1"."person_id")
  ORDER BY "visit_occurrence_1"."visit_start_date" DESC
  FETCH FIRST 1 ROW ONLY
) AS "visit_1" ON TRUE

Define

FunSQL.DefineMethod
Define(; over; args = [])
Define(args...; over)

The Define node adds or replaces output columns.

Examples

Show patients who are at least 16 years old.

julia> person = SQLTable(:person, columns = [:person_id, :birth_datetime]);

julia> q = From(:person) |>
           Define(:age => Fun.now() .- Get.birth_datetime) |>
           Where(Get.age .>= "16 years");

julia> print(render(q, tables = [person]))
SELECT
  "person_2"."person_id",
  "person_2"."birth_datetime",
  "person_2"."age"
FROM (
  SELECT
    "person_1"."person_id",
    "person_1"."birth_datetime",
    (now() - "person_1"."birth_datetime") AS "age"
  FROM "person" AS "person_1"
) AS "person_2"
WHERE ("person_2"."age" >= '16 years')

Conceal the year of birth of patients born before 1930.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(:person) |>
           Define(:year_of_birth => Fun.case(Get.year_of_birth .>= 1930,
                                             Get.year_of_birth,
                                             missing));

julia> print(render(q, tables = [person]))
SELECT
  "person_1"."person_id",
  (CASE WHEN ("person_1"."year_of_birth" >= 1930) THEN "person_1"."year_of_birth" ELSE NULL END) AS "year_of_birth"
FROM "person" AS "person_1"

From

FunSQL.FromMethod
From(; source)
From(tbl::SQLTable)
From(name::Symbol)
From(^)
From(df)
From(f::SQLNode; columns::Vector{Symbol})
From(::Nothing)

From outputs the content of a database table.

The parameter source could be one of:

  • a SQLTable object;
  • a Symbol value;
  • a ^ object;
  • a DataFrame or any Tables.jl-compatible dataset;
  • A SQLNode representing a table-valued function. In this case, From also requires a keyword parameter columns with a list of output columns produced by the function.
  • nothing.

When source is a symbol, it can refer to either a table in SQLCatalog or an intermediate dataset defined with the With node.

The From node is translated to a SQL query with a FROM clause:

SELECT ...
FROM $source

From(^) must be a component of Iterate. In the context of Iterate, it refers to the output of the previous iteration.

From(::DataFrame) is translated to a VALUES clause.

From(nothing) emits a dataset with one row and no columns and can usually be omitted.

Examples

List all patients.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(person);

julia> print(render(q))
SELECT
  "person_1"."person_id",
  "person_1"."year_of_birth"
FROM "person" AS "person_1"

List all patients.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(:person);

julia> print(render(q, tables = [person]))
SELECT
  "person_1"."person_id",
  "person_1"."year_of_birth"
FROM "person" AS "person_1"

Show all patients diagnosed with essential hypertension.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> condition_occurrence =
           SQLTable(:condition_occurrence,
                    columns = [:condition_occurrence_id, :person_id, :condition_concept_id]);

julia> q = From(:person) |>
           Where(Fun.in(Get.person_id, From(:essential_hypertension) |>
                                       Select(Get.person_id))) |>
           With(:essential_hypertension =>
                    From(:condition_occurrence) |>
                    Where(Get.condition_concept_id .== 320128));

julia> print(render(q, tables = [person, condition_occurrence]))
WITH "essential_hypertension_1" ("person_id") AS (
  SELECT "condition_occurrence_1"."person_id"
  FROM "condition_occurrence" AS "condition_occurrence_1"
  WHERE ("condition_occurrence_1"."condition_concept_id" = 320128)
)
SELECT
  "person_1"."person_id",
  "person_1"."year_of_birth"
FROM "person" AS "person_1"
WHERE ("person_1"."person_id" IN (
  SELECT "essential_hypertension_2"."person_id"
  FROM "essential_hypertension_1" AS "essential_hypertension_2"
))

Show the current date.

julia> q = From(nothing) |>
           Select(Fun.current_date());

julia> print(render(q))
SELECT CURRENT_DATE AS "current_date"

julia> q = Select(Fun.current_date());

julia> print(render(q))
SELECT CURRENT_DATE AS "current_date"

Query a DataFrame.

julia> df = DataFrame(name = ["SQL", "Julia", "FunSQL"],
                      year = [1974, 2012, 2021]);

julia> q = From(df) |>
           Group() |>
           Select(Agg.min(Get.year), Agg.max(Get.year));

julia> print(render(q))
SELECT
  min("values_1"."year") AS "min",
  max("values_1"."year") AS "max"
FROM (
  VALUES
    (1974),
    (2012),
    (2021)
) AS "values_1" ("year")

Parse comma-separated numbers.

julia> q = From(Fun.regexp_matches("2,3,5,7,11", "(\\d+)", "g"),
                columns = [:captures]) |>
           Select(Fun."CAST(?[1] AS INTEGER)"(Get.captures));

julia> print(render(q, dialect = :postgresql))
SELECT CAST("regexp_matches_1"."captures"[1] AS INTEGER) AS "_"
FROM regexp_matches('2,3,5,7,11', '(\d+)', 'g') AS "regexp_matches_1" ("captures")

Fun

FunSQL.FunMethod
Fun(; name, args = [])
Fun(name; args = [])
Fun(name, args...)
Fun.name(args...)

Application of a SQL function or a SQL operator.

A Fun node is also generated by broadcasting on SQLNode objects. Names of Julia operators (==, !=, &&, ||, !) are replaced with their SQL equivalents (=, <>, and, or, not).

If name contains only symbols, or if name starts or ends with a space, the Fun node is translated to a SQL operator.

If name contains one or more ? characters, it serves as a template of a SQL expression where ? symbols are replaced with the given arguments. Use ?? to represent a literal ? mark. Wrap the template in parentheses if this is necessary to make the SQL expression unambiguous.

Certain names have a customized translation in order to generate common SQL functions and operators with irregular syntax:

Fun nodeSQL syntax
Fun.and(p₁, p₂, …)p₁ AND p₂ AND …
Fun.between(x, y, z)x BETWEEN y AND z
Fun.case(p, x, …)CASE WHEN p THEN x … END
Fun.cast(x, "TYPE")CAST(x AS TYPE)
Fun.concat(s₁, s₂, …)dialect-specific, e.g., (s₁ || s₂ || …)
Fun.current_date()CURRENT_DATE
Fun.current_timestamp()CURRENT_TIMESTAMP
Fun.exists(q)EXISTS q
Fun.extract("FIELD", x)EXTRACT(FIELD FROM x)
Fun.in(x, q)x IN q
Fun.in(x, y₁, y₂, …)x IN (y₁, y₂, …)
Fun.is_not_null(x)x IS NOT NULL
Fun.is_null(x)x IS NULL
Fun.like(x, y)x LIKE y
Fun.not(p)NOT p
Fun.not_between(x, y, z)x NOT BETWEEN y AND z
Fun.not_exists(q)NOT EXISTS q
Fun.not_in(x, q)x NOT IN q
Fun.not_in(x, y₁, y₂, …)x NOT IN (y₁, y₂, …)
Fun.not_like(x, y)x NOT LIKE y
Fun.or(p₁, p₂, …)p₁ OR p₂ OR …

Examples

Replace missing values with N/A.

julia> location = SQLTable(:location, columns = [:location_id, :city]);

julia> q = From(:location) |>
           Select(Fun.coalesce(Get.city, "N/A"));

julia> print(render(q, tables = [location]))
SELECT coalesce("location_1"."city", 'N/A') AS "coalesce"
FROM "location" AS "location_1"

Find patients not born in 1980.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(:person) |>
           Where(Get.year_of_birth .!= 1980);

julia> print(render(q, tables = [person]))
SELECT
  "person_1"."person_id",
  "person_1"."year_of_birth"
FROM "person" AS "person_1"
WHERE ("person_1"."year_of_birth" <> 1980)

For each patient, show their age in 2000.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(:person) |>
           Select(Fun."-"(2000, Get.year_of_birth));

julia> print(render(q, tables = [person]))
SELECT (2000 - "person_1"."year_of_birth") AS "_"
FROM "person" AS "person_1"

Find invalid zip codes.

julia> location = SQLTable(:location, columns = [:location_id, :zip]);

julia> q = From(:location) |>
           Select(Fun." NOT SIMILAR TO '[0-9]{5}'"(Get.zip));

julia> print(render(q, tables = [location]))
SELECT ("location_1"."zip" NOT SIMILAR TO '[0-9]{5}') AS "_"
FROM "location" AS "location_1"

Extract the first 3 digits of the zip code.

julia> location = SQLTable(:location, columns = [:location_id, :zip]);

julia> q = From(:location) |>
           Select(Fun."SUBSTRING(? FROM ? FOR ?)"(Get.zip, 1, 3));

julia> print(render(q, tables = [location]))
SELECT SUBSTRING("location_1"."zip" FROM 1 FOR 3) AS "_"
FROM "location" AS "location_1"

Get

FunSQL.GetMethod
Get(; over, name)
Get(name; over)
Get.name        Get."name"      Get[name]       Get["name"]
over.name       over."name"     over[name]      over["name"]
name

A reference to a column of the input dataset.

When a column reference is ambiguous (e.g., with Join), use As to disambiguate the columns, and a chained Get node (Get.a.b.….z) to refer to a column wrapped with … |> As(:b) |> As(:a).

Examples

List patient IDs.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(:person) |>
           Select(Get(:person_id));

julia> print(render(q, tables = [person]))
SELECT "person_1"."person_id"
FROM "person" AS "person_1"

Show patients with their state of residence.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth, :location_id]);

julia> location = SQLTable(:location, columns = [:location_id, :state]);

julia> q = From(:person) |>
           Join(From(:location) |> As(:location),
                on = Get.location_id .== Get.location.location_id) |>
           Select(Get.person_id, Get.location.state);

julia> print(render(q, tables = [person, location]))
SELECT
  "person_1"."person_id",
  "location_1"."state"
FROM "person" AS "person_1"
JOIN "location" AS "location_1" ON ("person_1"."location_id" = "location_1"."location_id")

Group

FunSQL.GroupMethod
Group(; over; by = [], name = nothing)
Group(by...; over, name = nothing)

The Group node summarizes the input dataset.

Specifically, Group outputs all unique values of the given grouping key. This key partitions the input rows into disjoint groups that are summarized by aggregate functions Agg applied to the output of Group. An optional parameter name specifies the field to hold the group.

The Group node is translated to a SQL query with a GROUP BY clause:

SELECT ...
FROM $over
GROUP BY $by...

Examples

Total number of patients.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(:person) |>
           Group() |>
           Select(Agg.count());

julia> print(render(q, tables = [person]))
SELECT count(*) AS "count"
FROM "person" AS "person_1"

Number of patients per year of birth.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(:person) |>
           Group(Get.year_of_birth) |>
           Select(Get.year_of_birth, Agg.count());

julia> print(render(q, tables = [person]))
SELECT
  "person_1"."year_of_birth",
  count(*) AS "count"
FROM "person" AS "person_1"
GROUP BY "person_1"."year_of_birth"

The same example using an explicit group name.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(:person) |>
           Group(Get.year_of_birth, name = :person) |>
           Select(Get.year_of_birth, Get.person |> Agg.count());

julia> print(render(q, tables = [person]))
SELECT
  "person_1"."year_of_birth",
  count(*) AS "count"
FROM "person" AS "person_1"
GROUP BY "person_1"."year_of_birth"

Distinct states across all available locations.

julia> location = SQLTable(:location, columns = [:location_id, :state]);

julia> q = From(:location) |>
           Group(Get.state);

julia> print(render(q, tables = [location]))
SELECT DISTINCT "location_1"."state"
FROM "location" AS "location_1"

Highlight

FunSQL.HighlightMethod
Highlight(; over = nothing; color)
Highlight(color; over = nothing)

Highlight over with the given color.

The highlighted node is printed with the selected color when the query containing it is displayed.

Available colors can be found in Base.text_colors.

Examples

julia> q = From(:person) |>
           Select(Get.person_id |> Highlight(:bold))
let q1 = From(:person),
    q2 = q1 |> Select(Get.person_id)
    q2
end

Iterate

FunSQL.IterateMethod
Iterate(; over = nothing, iterator)
Iterate(iterator; over = nothing)

Iterate generates the concatenated output of an iterated query.

The over query is evaluated first. Then the iterator query is repeatedly applied: to the output of over, then to the output of its previous run, and so on, until the iterator produces no data. All these outputs are concatenated to generate the output of Iterate.

The iterator query may explicitly refer to the output of the previous run using From(^) notation.

The Iterate node is translated to a recursive common table expression:

WITH RECURSIVE iterator AS (
  SELECT ...
  FROM $over
  UNION ALL
  SELECT ...
  FROM $iterator
)
SELECT ...
FROM iterator

Examples

Calculate the factorial.

julia> q = Define(:n => 1, :f => 1) |>
           Iterate(From(^) |>
                   Where(Get.n .< 10) |>
                   Define(:n => Get.n .+ 1, :f => Get.f .* (Get.n .+ 1)));

julia> print(render(q))
WITH RECURSIVE "__1" ("n", "f") AS (
  SELECT
    1 AS "n",
    1 AS "f"
  UNION ALL
  SELECT
    ("__2"."n" + 1) AS "n",
    ("__2"."f" * ("__2"."n" + 1)) AS "f"
  FROM "__1" AS "__2"
  WHERE ("__2"."n" < 10)
)
SELECT
  "__3"."n",
  "__3"."f"
FROM "__1" AS "__3"

*Calculate the factorial, with implicit From(^).

julia> q = Define(:n => 1, :f => 1) |>
           Iterate(Where(Get.n .< 10) |>
                   Define(:n => Get.n .+ 1, :f => Get.f .* (Get.n .+ 1)));

julia> print(render(q))
WITH RECURSIVE "__1" ("n", "f") AS (
  SELECT
    1 AS "n",
    1 AS "f"
  UNION ALL
  SELECT
    ("__2"."n" + 1) AS "n",
    ("__2"."f" * ("__2"."n" + 1)) AS "f"
  FROM "__1" AS "__2"
  WHERE ("__2"."n" < 10)
)
SELECT
  "__3"."n",
  "__3"."f"
FROM "__1" AS "__3"

Join

FunSQL.JoinMethod
Join(; over = nothing, joinee, on, left = false, right = false, optional = false)
Join(joinee; over = nothing, on, left = false, right = false, optional = false)
Join(joinee, on; over = nothing, left = false, right = false, optional = false)

Join correlates two input datasets.

The Join node is translated to a query with a JOIN clause:

SELECT ...
FROM $over
JOIN $joinee ON $on

You can specify the join type:

  • INNER JOIN (the default);
  • LEFT JOIN (left = true or LeftJoin);
  • RIGHT JOIN (right = true);
  • FULL JOIN (both left = true and right = true);
  • CROSS JOIN (on = true).

When optional is set, the JOIN clause is omitted if the query does not depend on any columns from the joinee branch.

To make a lateral join, apply Bind to the joinee branch.

Use As to disambiguate output columns.

Examples

Show patients with their state of residence.

julia> person = SQLTable(:person, columns = [:person_id, :location_id]);

julia> location = SQLTable(:location, columns = [:location_id, :state]);

julia> q = From(:person) |>
           Join(:location => From(:location),
                Get.location_id .== Get.location.location_id) |>
           Select(Get.person_id, Get.location.state);

julia> print(render(q, tables = [person, location]))
SELECT
  "person_1"."person_id",
  "location_1"."state"
FROM "person" AS "person_1"
JOIN "location" AS "location_1" ON ("person_1"."location_id" = "location_1"."location_id")

Limit

FunSQL.LimitMethod
Limit(; over = nothing, offset = nothing, limit = nothing)
Limit(limit; over = nothing, offset = nothing)
Limit(offset, limit; over = nothing)
Limit(start:stop; over = nothing)

The Limit node skips the first offset rows and then emits the next limit rows.

To make the output deterministic, Limit must be applied directly after an Order node.

The Limit node is translated to a query with a LIMIT or a FETCH clause:

SELECT ...
FROM $over
OFFSET $offset ROWS
FETCH NEXT $limit ROWS ONLY

Examples

Show the oldest patient.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(:person) |>
           Order(Get.year_of_birth) |>
           Limit(1);

julia> print(render(q, tables = [person]))
SELECT
  "person_1"."person_id",
  "person_1"."year_of_birth"
FROM "person" AS "person_1"
ORDER BY "person_1"."year_of_birth"
FETCH FIRST 1 ROW ONLY

Lit

FunSQL.LitMethod
Lit(; val)
Lit(val)

A SQL literal.

In a context where a SQL node is expected, missing, numbers, strings, and datetime values are automatically converted to SQL literals.

Examples

julia> q = Select(:null => missing,
                  :boolean => true,
                  :integer => 42,
                  :text => "SQL is fun!",
                  :date => Date(2000));

julia> print(render(q))
SELECT
  NULL AS "null",
  TRUE AS "boolean",
  42 AS "integer",
  'SQL is fun!' AS "text",
  '2000-01-01' AS "date"

Order

FunSQL.OrderMethod
Order(; over = nothing, by)
Order(by...; over = nothing)

Order sorts the input rows by the given key.

The Ordernode is translated to a query with an ORDER BY clause:

SELECT ...
FROM $over
ORDER BY $by...

Specify the sort order with Asc, Desc, or Sort.

Examples

List patients ordered by their age.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(:person) |>
           Order(Get.year_of_birth);

julia> print(render(q, tables = [person]))
SELECT
  "person_1"."person_id",
  "person_1"."year_of_birth"
FROM "person" AS "person_1"
ORDER BY "person_1"."year_of_birth"

Over

FunSQL.OverMethod
Over(; over = nothing, arg, materialized = nothing)
Over(arg; over = nothing, materialized = nothing)

base |> Over(arg) is an alias for With(base, over = arg).

Examples

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> condition_occurrence =
           SQLTable(:condition_occurrence, columns = [:condition_occurrence_id,
                                                      :person_id,
                                                      :condition_concept_id]);

julia> q = From(:condition_occurrence) |>
           Where(Get.condition_concept_id .== 320128) |>
           As(:essential_hypertension) |>
           Over(From(:person) |>
                Where(Fun.in(Get.person_id, From(:essential_hypertension) |>
                                            Select(Get.person_id))));

julia> print(render(q, tables = [person, condition_occurrence]))
WITH "essential_hypertension_1" ("person_id") AS (
  SELECT "condition_occurrence_1"."person_id"
  FROM "condition_occurrence" AS "condition_occurrence_1"
  WHERE ("condition_occurrence_1"."condition_concept_id" = 320128)
)
SELECT
  "person_1"."person_id",
  "person_1"."year_of_birth"
FROM "person" AS "person_1"
WHERE ("person_1"."person_id" IN (
  SELECT "essential_hypertension_2"."person_id"
  FROM "essential_hypertension_1" AS "essential_hypertension_2"
))

Partition

FunSQL.PartitionMethod
Partition(; over, by = [], order_by = [], frame = nothing, name = nothing)
Partition(by...; over, order_by = [], frame = nothing, name = nothing)

The Partition node relates adjacent rows.

Specifically, Partition specifies how to relate each row to the adjacent rows in the same dataset. The rows are partitioned by the given key and ordered within each partition using order_by key. The parameter frame customizes the extent of related rows. These related rows are summarized by aggregate functions Agg applied to the output of Partition. An optional parameter name specifies the field to hold the partition.

The Partition node is translated to a query with a WINDOW clause:

SELECT ...
FROM $over
WINDOW w AS (PARTITION BY $by... ORDER BY $order_by...)

Examples

Enumerate patients' visits.

julia> visit_occurrence =
           SQLTable(:visit_occurrence, columns = [:visit_occurrence_id, :person_id, :visit_start_date]);

julia> q = From(:visit_occurrence) |>
           Partition(Get.person_id, order_by = [Get.visit_start_date]) |>
           Select(Agg.row_number(), Get.visit_occurrence_id);

julia> print(render(q, tables = [visit_occurrence]))
SELECT
  (row_number() OVER (PARTITION BY "visit_occurrence_1"."person_id" ORDER BY "visit_occurrence_1"."visit_start_date")) AS "row_number",
  "visit_occurrence_1"."visit_occurrence_id"
FROM "visit_occurrence" AS "visit_occurrence_1"

The same example using an explicit partition name.

julia> visit_occurrence =
           SQLTable(:visit_occurrence, columns = [:visit_occurrence_id, :person_id, :visit_start_date]);

julia> q = From(:visit_occurrence) |>
           Partition(Get.person_id, order_by = [Get.visit_start_date], name = :visit_by_person) |>
           Select(Get.visit_by_person |> Agg.row_number(), Get.visit_occurrence_id);

julia> print(render(q, tables = [visit_occurrence]))
SELECT
  (row_number() OVER (PARTITION BY "visit_occurrence_1"."person_id" ORDER BY "visit_occurrence_1"."visit_start_date")) AS "row_number",
  "visit_occurrence_1"."visit_occurrence_id"
FROM "visit_occurrence" AS "visit_occurrence_1"

Calculate the moving average of the number of patients by the year of birth.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(:person) |>
           Group(Get.year_of_birth) |>
           Partition(order_by = [Get.year_of_birth],
                     frame = (mode = :range, start = -1, finish = 1)) |>
           Select(Get.year_of_birth, Agg.avg(Agg.count()));

julia> print(render(q, tables = [person]))
SELECT
  "person_1"."year_of_birth",
  (avg(count(*)) OVER (ORDER BY "person_1"."year_of_birth" RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING)) AS "avg"
FROM "person" AS "person_1"
GROUP BY "person_1"."year_of_birth"

Select

FunSQL.SelectMethod
Select(; over; args)
Select(args...; over)

The Select node specifies the output columns.

SELECT $args...
FROM $over

Set the column labels with As.

Examples

List patient IDs and their age.

julia> person = SQLTable(:person, columns = [:person_id, :birth_datetime]);

julia> q = From(:person) |>
           Select(Get.person_id,
                  :age => Fun.now() .- Get.birth_datetime);

julia> print(render(q, tables = [person]))
SELECT
  "person_1"."person_id",
  (now() - "person_1"."birth_datetime") AS "age"
FROM "person" AS "person_1"

Sort, Asc, and Desc

FunSQL.AscMethod
Asc(; over = nothing, nulls = nothing)

Ascending order indicator.

FunSQL.DescMethod
Desc(; over = nothing, nulls = nothing)

Descending order indicator.

FunSQL.SortMethod
Sort(; over = nothing, value, nulls = nothing)
Sort(value; over = nothing, nulls = nothing)
Asc(; over = nothing, nulls = nothing)
Desc(; over = nothing, nulls = nothing)

Sort order indicator.

Use with Order or Partition nodes.

Examples

List patients ordered by their age.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(:person) |>
           Order(Get.year_of_birth |> Desc(nulls = :first));

julia> print(render(q, tables = [person]))
SELECT
  "person_1"."person_id",
  "person_1"."year_of_birth"
FROM "person" AS "person_1"
ORDER BY "person_1"."year_of_birth" DESC NULLS FIRST

Var

FunSQL.VarMethod
Var(; name)
Var(name)
Var.name        Var."name"      Var[name]       Var["name"]

A reference to a query parameter.

Specify the value for the parameter with Bind to create a correlated subquery or a lateral join.

Examples

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(:person) |>
           Where(Get.year_of_birth .> Var.YEAR);

julia> print(render(q, tables = [person]))
SELECT
  "person_1"."person_id",
  "person_1"."year_of_birth"
FROM "person" AS "person_1"
WHERE ("person_1"."year_of_birth" > :YEAR)

Where

FunSQL.WhereMethod
Where(; over = nothing, condition)
Where(condition; over = nothing)

The Where node filters the input rows by the given condition.

Where is translated to a SQL query with a WHERE clause:

SELECT ...
FROM $over
WHERE $condition

Examples

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(:person) |>
           Where(Fun(">", Get.year_of_birth, 2000));

julia> print(render(q, tables = [person]))
SELECT
  "person_1"."person_id",
  "person_1"."year_of_birth"
FROM "person" AS "person_1"
WHERE ("person_1"."year_of_birth" > 2000)

With

FunSQL.WithMethod
With(; over = nothing, args, materialized = nothing)
With(args...; over = nothing, materialized = nothing)

With assigns a name to a temporary dataset. The dataset content can be retrieved within the over query using the From node.

With is translated to a common table expression:

WITH $args...
SELECT ...
FROM $over

Examples

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> condition_occurrence =
           SQLTable(:condition_occurrence, columns = [:condition_occurrence_id,
                                                      :person_id,
                                                      :condition_concept_id]);

julia> q = From(:person) |>
           Where(Fun.in(Get.person_id, From(:essential_hypertension) |>
                                       Select(Get.person_id))) |>
           With(:essential_hypertension =>
                    From(:condition_occurrence) |>
                    Where(Get.condition_concept_id .== 320128));

julia> print(render(q, tables = [person, condition_occurrence]))
WITH "essential_hypertension_1" ("person_id") AS (
  SELECT "condition_occurrence_1"."person_id"
  FROM "condition_occurrence" AS "condition_occurrence_1"
  WHERE ("condition_occurrence_1"."condition_concept_id" = 320128)
)
SELECT
  "person_1"."person_id",
  "person_1"."year_of_birth"
FROM "person" AS "person_1"
WHERE ("person_1"."person_id" IN (
  SELECT "essential_hypertension_2"."person_id"
  FROM "essential_hypertension_1" AS "essential_hypertension_2"
))

WithExternal

FunSQL.WithExternalMethod
WithExternal(; over = nothing, args, qualifiers = [], handler = nothing)
WithExternal(args...; over = nothing, qualifiers = [], handler = nothing)

WithExternal assigns a name to a temporary dataset. The dataset content can be retrieved within the over query using the From node.

The definition of the dataset is converted to a Pair{SQLTable, SQLClause} object and sent to handler, which can use it, for instance, to construct a SELECT INTO statement.

Examples

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> condition_occurrence =
           SQLTable(:condition_occurrence, columns = [:condition_occurrence_id,
                                                      :person_id,
                                                      :condition_concept_id]);

julia> handler((tbl, def)) =
           println("CREATE TEMP TABLE ", render(ID(tbl.name)), " AS\n",
                   render(def), ";\n");

julia> q = From(:person) |>
           Where(Fun.in(Get.person_id, From(:essential_hypertension) |>
                                       Select(Get.person_id))) |>
           WithExternal(:essential_hypertension =>
                            From(:condition_occurrence) |>
                            Where(Get.condition_concept_id .== 320128),
                        handler = handler);

julia> print(render(q, tables = [person, condition_occurrence]))
CREATE TEMP TABLE "essential_hypertension" AS
SELECT "condition_occurrence_1"."person_id"
FROM "condition_occurrence" AS "condition_occurrence_1"
WHERE ("condition_occurrence_1"."condition_concept_id" = 320128);

SELECT
  "person_1"."person_id",
  "person_1"."year_of_birth"
FROM "person" AS "person_1"
WHERE ("person_1"."person_id" IN (
  SELECT "essential_hypertension_1"."person_id"
  FROM "essential_hypertension" AS "essential_hypertension_1"
))

SQLClause

AGG

FunSQL.AGGMethod
AGG(; name, args = [], filter = nothing, over = nothing)
AGG(name; args = [], filter = nothing, over = nothing)
AGG(name, args...; filter = nothing, over = nothing)

An application of an aggregate function.

Examples

julia> c = AGG(:max, :year_of_birth);

julia> print(render(c))
max("year_of_birth")
julia> c = AGG(:count, filter = FUN(">", :year_of_birth, 1970));

julia> print(render(c))
(count(*) FILTER (WHERE ("year_of_birth" > 1970)))
julia> c = AGG(:row_number, over = PARTITION(:year_of_birth));

julia> print(render(c))
(row_number() OVER (PARTITION BY "year_of_birth"))

AS

FunSQL.ASMethod
AS(; over = nothing, name, columns = nothing)
AS(name; over = nothing, columns = nothing)

An AS clause.

Examples

julia> c = ID(:person) |> AS(:p);

julia> print(render(c))
"person" AS "p"
julia> c = ID(:person) |> AS(:p, columns = [:person_id, :year_of_birth]);

julia> print(render(c))
"person" AS "p" ("person_id", "year_of_birth")

FROM

FunSQL.FROMMethod
FROM(; over = nothing)
FROM(over)

A FROM clause.

Examples

julia> c = ID(:person) |> AS(:p) |> FROM() |> SELECT((:p, :person_id));

julia> print(render(c))
SELECT "p"."person_id"
FROM "person" AS "p"

FUN

FunSQL.FUNMethod
FUN(; name, args = [])
FUN(name; args = [])
FUN(name, args...)

An invocation of a SQL function or a SQL operator.

Examples

julia> c = FUN(:concat, :city, ", ", :state);

julia> print(render(c))
concat("city", ', ', "state")
julia> c = FUN("||", :city, ", ", :state);

julia> print(render(c))
("city" || ', ' || "state")
julia> c = FUN("SUBSTRING(? FROM ? FOR ?)", :zip, 1, 3);

julia> print(render(c))
SUBSTRING("zip" FROM 1 FOR 3)

GROUP

FunSQL.GROUPMethod
GROUP(; over = nothing, by = [])
GROUP(by...; over = nothing)

A GROUP BY clause.

Examples

julia> c = FROM(:person) |>
           GROUP(:year_of_birth) |>
           SELECT(:year_of_birth, AGG(:count));

julia> print(render(c))
SELECT
  "year_of_birth",
  count(*)
FROM "person"
GROUP BY "year_of_birth"

HAVING

FunSQL.HAVINGMethod
HAVING(; over = nothing, condition)
HAVING(condition; over = nothing)

A HAVING clause.

Examples

julia> c = FROM(:person) |>
           GROUP(:year_of_birth) |>
           HAVING(FUN(">", AGG(:count), 10)) |>
           SELECT(:person_id);

julia> print(render(c))
SELECT "person_id"
FROM "person"
GROUP BY "year_of_birth"
HAVING (count(*) > 10)

ID

FunSQL.IDMethod
ID(; over = nothing, name)
ID(name; over = nothing)
ID(qualifiers, name)

A SQL identifier. Specify over or use the |> operator to make a qualified identifier.

Examples

julia> c = ID(:person);

julia> print(render(c))
"person"
julia> c = ID(:p) |> ID(:birth_datetime);

julia> print(render(c))
"p"."birth_datetime"
julia> c = ID([:pg_catalog], :pg_database);

julia> print(render(c))
"pg_catalog"."pg_database"

JOIN

FunSQL.JOINMethod
JOIN(; over = nothing, joinee, on, left = false, right = false, lateral = false)
JOIN(joinee; over = nothing, on, left = false, right = false, lateral = false)
JOIN(joinee, on; over = nothing, left = false, right = false, lateral = false)

A JOIN clause.

Examples

julia> c = FROM(:p => :person) |>
           JOIN(:l => :location,
                on = FUN("=", (:p, :location_id), (:l, :location_id)),
                left = true) |>
           SELECT((:p, :person_id), (:l, :state));

julia> print(render(c))
SELECT
  "p"."person_id",
  "l"."state"
FROM "person" AS "p"
LEFT JOIN "location" AS "l" ON ("p"."location_id" = "l"."location_id")

LIMIT

FunSQL.LIMITMethod
LIMIT(; over = nothing, offset = nothing, limit = nothing, with_ties = false)
LIMIT(limit; over = nothing, offset = nothing, with_ties = false)
LIMIT(offset, limit; over = nothing, with_ties = false)
LIMIT(start:stop; over = nothing, with_ties = false)

A LIMIT clause.

Examples

julia> c = FROM(:person) |>
           LIMIT(1) |>
           SELECT(:person_id);

julia> print(render(c))
SELECT "person_id"
FROM "person"
FETCH FIRST 1 ROW ONLY

LIT

FunSQL.LITMethod
LIT(; val)
LIT(val)

A SQL literal.

In a context of a SQL clause, missing, numbers, strings and datetime values are automatically converted to SQL literals.

Examples

julia> c = LIT(missing);

julia> print(render(c))
NULL
julia> c = LIT("SQL is fun!");

julia> print(render(c))
'SQL is fun!'

NOTE

FunSQL.NOTEMethod
NOTE(; over = nothing, text, postfix = false)
NOTE(text; over = nothing, postfix = false)

A free-form prefix of postfix annotation.

Examples

julia> c = FROM(:p => :person) |>
           NOTE("TABLESAMPLE SYSTEM (50)", postfix = true) |>
           SELECT((:p, :person_id));

julia> print(render(c))
SELECT "p"."person_id"
FROM "person" AS "p" TABLESAMPLE SYSTEM (50)

ORDER

FunSQL.ORDERMethod
ORDER(; over = nothing, by = [])
ORDER(by...; over = nothing)

An ORDER BY clause.

Examples

julia> c = FROM(:person) |>
           ORDER(:year_of_birth) |>
           SELECT(:person_id);

julia> print(render(c))
SELECT "person_id"
FROM "person"
ORDER BY "year_of_birth"

PARTITION

FunSQL.PARTITIONMethod
PARTITION(; over = nothing, by = [], order_by = [], frame = nothing)
PARTITION(by...; over = nothing, order_by = [], frame = nothing)

A window definition clause.

Examples

julia> c = FROM(:person) |>
           SELECT(:person_id,
                  AGG(:row_number, over = PARTITION(:year_of_birth)));

julia> print(render(c))
SELECT
  "person_id",
  (row_number() OVER (PARTITION BY "year_of_birth"))
FROM "person"
julia> c = FROM(:person) |>
           WINDOW(:w1 => PARTITION(:year_of_birth),
                  :w2 => :w1 |> PARTITION(order_by = [:month_of_birth, :day_of_birth])) |>
           SELECT(:person_id, AGG(:row_number, over = :w2));

julia> print(render(c))
SELECT
  "person_id",
  (row_number() OVER ("w2"))
FROM "person"
WINDOW
  "w1" AS (PARTITION BY "year_of_birth"),
  "w2" AS ("w1" ORDER BY "month_of_birth", "day_of_birth")
julia> c = FROM(:person) |>
           GROUP(:year_of_birth) |>
           SELECT(:year_of_birth,
                  AGG(:avg,
                      AGG(:count),
                      over = PARTITION(order_by = [:year_of_birth],
                                       frame = (mode = :range, start = -1, finish = 1))));

julia> print(render(c))
SELECT
  "year_of_birth",
  (avg(count(*)) OVER (ORDER BY "year_of_birth" RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING))
FROM "person"
GROUP BY "year_of_birth"

SELECT

FunSQL.SELECTMethod
SELECT(; over = nothing, top = nothing, distinct = false, args)
SELECT(args...; over = nothing, top = nothing, distinct = false)

A SELECT clause. Unlike raw SQL, SELECT() should be placed at the end of a clause chain.

Set distinct to true to add a DISTINCT modifier.

Examples

julia> c = SELECT(true, false);

julia> print(render(c))
SELECT
  TRUE,
  FALSE
julia> c = FROM(:location) |>
           SELECT(distinct = true, :zip);

julia> print(render(c))
SELECT DISTINCT "zip"
FROM "location"

SORT, ASC, and DESC

FunSQL.ASCMethod
ASC(; over = nothing, nulls = nothing)

Ascending order indicator.

FunSQL.DESCMethod
DESC(; over = nothing, nulls = nothing)

Descending order indicator.

FunSQL.SORTMethod
SORT(; over = nothing, value, nulls = nothing)
SORT(value; over = nothing, nulls = nothing)
ASC(; over = nothing, nulls = nothing)
DESC(; over = nothing, nulls = nothing)

Sort order options.

Examples

julia> c = FROM(:person) |>
           ORDER(:year_of_birth |> DESC()) |>
           SELECT(:person_id);

julia> print(render(c))
SELECT "person_id"
FROM "person"
ORDER BY "year_of_birth" DESC

UNION

FunSQL.UNIONMethod
UNION(; over = nothing, all = false, args)
UNION(args...; over = nothing, all = false)

A UNION clause.

Examples

julia> c = FROM(:measurement) |>
           SELECT(:person_id, :date => :measurement_date) |>
           UNION(all = true,
                 FROM(:observation) |>
                 SELECT(:person_id, :date => :observation_date));

julia> print(render(c))
SELECT
  "person_id",
  "measurement_date" AS "date"
FROM "measurement"
UNION ALL
SELECT
  "person_id",
  "observation_date" AS "date"
FROM "observation"

VALUES

FunSQL.VALUESMethod
VALUES(; rows)
VALUES(rows)

A VALUES clause.

Examples

julia> c = VALUES([("SQL", 1974), ("Julia", 2012), ("FunSQL", 2021)]);

julia> print(render(c))
VALUES
  ('SQL', 1974),
  ('Julia', 2012),
  ('FunSQL', 2021)

VAR

FunSQL.VARMethod
VAR(; name)
VAR(name)

A placeholder in a parameterized query.

Examples

julia> c = VAR(:year);

julia> print(render(c))
:year

WHERE

FunSQL.WHEREMethod
WHERE(; over = nothing, condition)
WHERE(condition; over = nothing)

A WHERE clause.

Examples

julia> c = FROM(:location) |>
           WHERE(FUN("=", :zip, "60614")) |>
           SELECT(:location_id);

julia> print(render(c))
SELECT "location_id"
FROM "location"
WHERE ("zip" = '60614')

WINDOW

FunSQL.WINDOWMethod
WINDOW(; over = nothing, args)
WINDOW(args...; over = nothing)

A WINDOW clause.

Examples

julia> c = FROM(:person) |>
           WINDOW(:w1 => PARTITION(:year_of_birth),
                  :w2 => :w1 |> PARTITION(order_by = [:month_of_birth, :day_of_birth])) |>
           SELECT(:person_id, AGG("row_number", over = :w2));

julia> print(render(c))
SELECT
  "person_id",
  (row_number() OVER ("w2"))
FROM "person"
WINDOW
  "w1" AS (PARTITION BY "year_of_birth"),
  "w2" AS ("w1" ORDER BY "month_of_birth", "day_of_birth")

WITH

FunSQL.WITHMethod
WITH(; over = nothing, recursive = false, args)
WITH(args...; over = nothing, recursive = false)

A WITH clause.

Examples

julia> c = FROM(:person) |>
           WHERE(FUN(:in, :person_id,
                          FROM(:essential_hypertension) |>
                          SELECT(:person_id))) |>
           SELECT(:person_id, :year_of_birth) |>
           WITH(FROM(:condition_occurrence) |>
                WHERE(FUN("=", :condition_concept_id, 320128)) |>
                SELECT(:person_id) |>
                AS(:essential_hypertension));

julia> print(render(c))
WITH "essential_hypertension" AS (
  SELECT "person_id"
  FROM "condition_occurrence"
  WHERE ("condition_concept_id" = 320128)
)
SELECT
  "person_id",
  "year_of_birth"
FROM "person"
WHERE ("person_id" IN (
  SELECT "person_id"
  FROM "essential_hypertension"
))
julia> c = FROM(:essential_hypertension) |>
           SELECT(*) |>
           WITH(recursive = true,
                FROM(:concept) |>
                WHERE(FUN("=", :concept_id, 320128)) |>
                SELECT(:concept_id, :concept_name) |>
                UNION(all = true,
                      FROM(:eh => :essential_hypertension) |>
                      JOIN(:cr => :concept_relationship,
                           FUN("=", (:eh, :concept_id), (:cr, :concept_id_1))) |>
                      JOIN(:c => :concept,
                           FUN("=", (:cr, :concept_id_2), (:c, :concept_id))) |>
                      WHERE(FUN("=", (:cr, :relationship_id), "Subsumes")) |>
                      SELECT((:c, :concept_id), (:c, :concept_name))) |>
                AS(:essential_hypertension, columns = [:concept_id, :concept_name]));

julia> print(render(c))
WITH RECURSIVE "essential_hypertension" ("concept_id", "concept_name") AS (
  SELECT
    "concept_id",
    "concept_name"
  FROM "concept"
  WHERE ("concept_id" = 320128)
  UNION ALL
  SELECT
    "c"."concept_id",
    "c"."concept_name"
  FROM "essential_hypertension" AS "eh"
  JOIN "concept_relationship" AS "cr" ON ("eh"."concept_id" = "cr"."concept_id_1")
  JOIN "concept" AS "c" ON ("cr"."concept_id_2" = "c"."concept_id")
  WHERE ("cr"."relationship_id" = 'Subsumes')
)
SELECT *
FROM "essential_hypertension"