Base error class for all errors raised by FunSQL.

render(node::Union{SQLNode, SQLClause}; dialect = :default)::SQLStatement

Convert the given SQL node or clause object to a SQL string.

SQLDialect(; name = :default,             variable_style = :named,             variable_prefix = ':',             identifier_quotes = ('"', '"'),             has_boolean_literals = true,             limit_style = :ansi)SQLDialect(template::SQLDialect; kws...)SQLDialect(name::Symbol, kws...)

Properties and capabilities of a particular SQL dialect.

Use the constructorSQLDialect(name::Symbol) to create one of the known dialects::postgresql,:sqlite,:mysql,:redshift,:sqlserver.

SQLTable(; schema = nothing, name, columns)SQLTable(name; schema = nothing, columns)SQLTable(name, columns...; schema = nothing)

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

TheSQLTable constructor expects the tablename, a vectorcolumns of column names, and, optionally, the name of the tableschema. A name can be provided as aSymbol orString value.

Examples

julia> t = SQLTable(:location,                    :location_id, :address_1, :address_2, :city, :state, :zip);julia> show(t.name):locationjulia> show(t.columns)[:location_id, :address_1, :address_2, :city, :state, :zip]

julia> t = SQLTable(schema = "public",                    name = "person",                    columns = ["person_id", "birth_datetime", "location_id"]);julia> show(t.schema):publicjulia> show(t.name):personjulia> show(t.columns)[:person_id, :birth_datetime, :location_id]

Prepared SQL statement.

pack(stmt::SQLStatement, vars::Union{Dict, NamedTuple}) :: Vector{Any}

Convert named parameters to positional form.

A SQL expression.

An opaque wrapper over an arbitrary SQL node.

Agg(; over = nothing, name, distinct = false, args = [], filter = nothing)Agg(name; over = nothing, distinct = false, args = [], filter = nothing)Agg(name, args...; over = nothing, distinct = false, filter = nothing)

An application of an aggregate function.

Example

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))SELECT  "person_1"."year_of_birth",  COUNT(*) AS "count"FROM "person" AS "person_1"GROUP BY "person_1"."year_of_birth"

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);julia> q = From(person) |>           Group() |>           Select(Agg.count(distinct = true, Get.year_of_birth));julia> print(render(q))SELECT COUNT(DISTINCT "person_1"."year_of_birth") AS "count"FROM "person" AS "person_1"

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))SELECT  "person_1"."person_id",  "visit_group_1"."max" AS "max_visit_start_date"FROM "person" AS "person_1"LEFT JOIN (  SELECT    "visit_occurrence_1"."person_id",    MAX("visit_occurrence_1"."visit_start_date") AS "max"  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")

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

Append concatenates input datasets.

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

Examples

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)) |>           Select(Get.person_id, Get.date);julia> print(render(q))SELECT  "union_1"."person_id",  "union_1"."date"FROM (  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 "union_1"

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 a tabular node,As wraps the output of the node in a nested record.

Examples

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);julia> q = From(person) |>           As(:p) |>           Select(:birth_year => Get.p.year_of_birth);

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

TheBind node binds the query parameters in an inner query to make it a correlated subquery.

Examples

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(Get.person_id)));julia> print(render(q))SELECT "person_1"."person_id"FROM "person" AS "person_1"WHERE (EXISTS (  SELECT NULL  FROM "visit_occurrence" AS "visit_occurrence_1"  WHERE ("visit_occurrence_1"."person_id" = "person_1"."person_id")))

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

Define adds a column to the output.

Examples

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))SELECT  "person_1"."person_id",  "person_1"."birth_datetime",  (NOW() - "person_1"."birth_datetime") AS "age"FROM "person" AS "person_1"WHERE ((NOW() - "person_1"."birth_datetime") > '16 years')

From(; source)From(source)

From outputs the content of a database table. The parametersource could be aSQLTable object, a symbol (for use withWith), ornothing.

SELECT ...FROM $source

Examples

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"

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))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_1"."person_id"  FROM "essential_hypertension_1"))

julia> q = From(nothing);julia> print(render(q))SELECT NULL

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

A function or an operator invocation.

Example

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);julia> q = From(person) |>           Where(Fun.not(Get.person_id .> 2000));julia> print(render(q))SELECT  "person_1"."person_id",  "person_1"."year_of_birth"FROM "person" AS "person_1"WHERE (NOT ("person_1"."person_id" > 2000))

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

A reference to a table column.

Examples

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);julia> q = From(person) |>           As(:p) |>           Select(Get.p.person_id);

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);julia> q = From(person);julia> q = q |> Select(q.person_id);

Group(; over; by = [])Group(by...; over)

TheGroup node partitions the input rows into disjoint groupsby the given grouping key and outputs all unique values of the key. Aggregate functions applied to the output ofGroup summarize the values from the rows of each group.

SELECT ...FROM $overGROUP BY $by...

Examples

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))SELECT  "person_1"."year_of_birth",  COUNT(*) AS "count"FROM "person" AS "person_1"GROUP BY "person_1"."year_of_birth"

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);julia> q = From(person) |>           Group() |>           Select(Agg.count(distinct = true, Get.year_of_birth));julia> print(render(q))SELECT COUNT(DISTINCT "person_1"."year_of_birth") AS "count"FROM "person" AS "person_1"

Highlight(; over = nothing; color)Highlight(color; over = nothing)

Highlightover with the givencolor.

Available colors can be found inBase.text_colors.

Examples

julia> q = Get.person_id |> Highlight(:bold);

Iterate(; over = nothing, iterator)Iterate(iterator; over = nothing)

Iterate generates the concatenated output of an iterated query.

Theover query is evaluated first. Then theiterator query is repeatedly applied: to the output ofover, 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 ofIterate.

Theiterator query should have an alias specified withAs; it can refer to the output of the previous iteration usingFrom with the same alias.

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

Examples

julia> q = Define(:n => 1, :f => 1) |>           Iterate(               From(:factorial) |>               Define(:n => Get.n .+ 1) |>               Define(:f => Get.f .* Get.n) |>               Where(Get.n .<= 10) |>               As(:factorial));julia> print(render(q))WITH RECURSIVE "factorial_1" ("n", "f") AS (  SELECT    1 AS "n",    1 AS "f"  UNION ALL  SELECT    ("factorial_1"."n" + 1) AS "n",    ("factorial_1"."f" * ("factorial_1"."n" + 1)) AS "f"  FROM "factorial_1"  WHERE (("factorial_1"."n" + 1) <= 10))SELECT  "factorial_1"."n",  "factorial_1"."f"FROM "factorial_1"

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

Join correlates two input datasets.

Whenoptional is set, theJOIN clause is omitted if the output contains no data from thejoinee branch.

SELECT ...FROM $overJOIN $joinee ON $on

Examples

julia> person = SQLTable(:person, columns = [:person_id, :location_id]);julia> location = SQLTable(:location, columns = [:location_id, :state]);julia> q = person |>           Join(:location => location,                Get.location_id .== Get.location.location_id) |>           Select(Get.person_id, Get.location.state);julia> print(render(q))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")

An alias forJoin(...; ..., left = true).

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

TheLimit node skips the firstoffset rows and then emits the nextlimit rows.

SELECT ...FROM $overOFFSET $offset ROWSFETCH NEXT $limit ROWS ONLY

Examples

julia> person = SQLTable(:person, columns = [:person_id]);julia> q = From(person) |>           Limit(1);julia> print(render(q))SELECT "person_1"."person_id"FROM "person" AS "person_1"FETCH FIRST 1 ROW ONLY

Lit(; val)Lit(val)

A SQL literal.

In a scalar context,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(; over = nothing, by)Order(by...; over = nothing)

Order sorts the input rowsby the given key.

SELECT ...FROM $overORDER BY $by...

Examples

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);julia> q = From(person) |>           Order(Get.year_of_birth);julia> print(render(q))SELECT  "person_1"."person_id",  "person_1"."year_of_birth"FROM "person" AS "person_1"ORDER BY "person_1"."year_of_birth"

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

ThePartition node specifies how to relate each row to the adjacent rows in the same dataset. Aggregate functions applied to the output ofPartition summarize the values of the related rows.

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

Examples

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);julia> q = From(person) |>           Partition(Get.year_of_birth) |>           Select(Get.year_of_birth, Agg.row_number());julia> print(render(q))SELECT  "person_1"."year_of_birth",  (ROW_NUMBER() OVER (PARTITION BY "person_1"."year_of_birth")) AS "row_number"FROM "person" AS "person_1"

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))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(; over; args)Select(args...; over)

TheSelect node specifies the output columns.

SELECT $args...FROM $over

Examples

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);julia> q = From(person) |>           Select(Get.person_id);julia> print(render(q))SELECT "person_1"."person_id"FROM "person" AS "person_1"

Asc(; over = nothing, nulls = nothing)

Ascending order indicator.

Desc(; over = nothing, nulls = nothing)

Descending order indicator.

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.

Examples

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);julia> q = From(person) |>           Order(Get.year_of_birth |> Desc());julia> print(render(q))SELECT  "person_1"."person_id",  "person_1"."year_of_birth"FROM "person" AS "person_1"ORDER BY "person_1"."year_of_birth" DESC

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

A reference to a query parameter.

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))SELECT  "person_1"."person_id",  "person_1"."year_of_birth"FROM "person" AS "person_1"WHERE ("person_1"."year_of_birth" > :year)

Where(; over = nothing, condition)Where(condition; over = nothing)

TheWhere node filters the input rows by the givencondition.

SELECT ...FROM $overWHERE $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))SELECT  "person_1"."person_id",  "person_1"."year_of_birth"FROM "person" AS "person_1"WHERE ("person_1"."year_of_birth" > 2000)

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

With assigns a name to a temporary dataset. This dataset could be referred to by name in theover query.

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))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_1"."person_id"  FROM "essential_hypertension_1"))

WithExternal(; over = nothing, args, schema = nothing, handler = nothing)WithExternal(args...; over = nothing, schema = nothing, handler = nothing)

WithExternal assigns a name to a temporary dataset. The dataset could be referred to by name in theover query.

The definition of the dataset is converted to aPair{SQLTable, SQLClause} object and sent tohandler, which can use it, for instance, to construct aSELECT 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), ";");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))CREATE TEMP TABLE "essential_hypertension" ASSELECT "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"."person_id"  FROM "essential_hypertension"))

A part of a SQL query.

An opaque wrapper over an arbitrary SQL clause.

AGG(; name, distinct = false, args = [], filter = nothing, over = nothing)AGG(name; distinct = false, args = [], filter = nothing, over = nothing)AGG(name, args...; distinct = false, filter = nothing, over = nothing)

An application of an aggregate function.

Examples

julia> c = AGG(:COUNT, OP("*"));julia> print(render(c))COUNT(*)

julia> c = AGG(:COUNT, distinct = true, :year_of_birth);julia> print(render(c))COUNT(DISTINCT "year_of_birth")

julia> c = AGG(:COUNT, OP("*"), filter = OP(">", :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(; over = nothing, name, columns = nothing)AS(name; over = nothing, columns = nothing)

AnAS 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")

CASE(; args)CASE(args...)

ACASE expression.

Examples

julia> c = CASE(OP("<", :year_of_birth, 1970), "boomer", "millenial");julia> print(render(c))(CASE WHEN ("year_of_birth" < 1970) THEN 'boomer' ELSE 'millenial' END)

FROM(; over = nothing)FROM(over)

AFROM 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(; name, args = [])FUN(name; args = [])FUN(name, args...)

An invocation of a SQL function.

Examples

julia> c = FUN(:EXTRACT, OP(:YEAR), KW(:FROM, FUN(:NOW)));julia> print(render(c))EXTRACT(YEAR FROM NOW())

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

AGROUP BY clause.

Examples

julia> c = FROM(:person) |>           GROUP(:year_of_birth) |>           SELECT(:year_of_birth, AGG("COUNT", OP("*")));julia> print(render(c))SELECT  "year_of_birth",  COUNT(*)FROM "person"GROUP BY "year_of_birth"

HAVING(; over = nothing, condition)HAVING(condition; over = nothing)

AHAVING clause.

Examples

julia> c = FROM(:person) |>           GROUP(:year_of_birth) |>           HAVING(OP(">", AGG("COUNT", OP("*")), 10)) |>           SELECT(:person_id);julia> print(render(c))SELECT "person_id"FROM "person"GROUP BY "year_of_birth"HAVING (COUNT(*) > 10)

ID(; over = nothing, name)ID(name; over = nothing)

A SQL identifier. Specifyover 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"

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)

AJOIN clause.

Examples

julia> c = FROM(:p => :person) |>           JOIN(:l => :location,                on = OP("=", (: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")

KW(; over = nothing, name)KW(name; over = nothing)KW(over, name)

A keyword argument of a function or an operator.

Examples

julia> c = FUN(:SUBSTRING, :zip, KW(:FROM, 1), KW(:FOR, 3));julia> print(render(c))SUBSTRING("zip" FROM 1 FOR 3)

julia> c = OP(:BETWEEN, :year_of_birth, 2000, KW(:AND, 2010));julia> print(render(c))("year_of_birth" BETWEEN 2000 AND 2010)

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)

ALIMIT 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(; 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(; over = nothing, text, postfix = false)NOTE(text; over = nothing, postfix = false)

A free-form prefix of postfix annotation. ```

OP(; name, args = [])OP(name; args = [])OP(name, args...)

An application of a SQL operator.

Examples

julia> c = OP("NOT", OP("=", :zip, "60614"));julia> print(render(c))(NOT ("zip" = '60614'))

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

AORDER 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(; 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", OP("*")),                      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(; over = nothing, top = nothing, distinct = false, args)SELECT(args...; over = nothing, top = nothing, distinct = false)

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

Setdistinct totrue to add aDISTINCT 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"

ASC(; over = nothing, nulls = nothing)

Ascending order indicator.

DESC(; over = nothing, nulls = nothing)

Descending order indicator.

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(; over = nothing, all = false, args)UNION(args...; over = nothing, all = false)

AUNION 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 ALLSELECT  "person_id",  "observation_date" AS "date"FROM "observation"

VAR(; name)VAR(name)

A placeholder in a parameterized query.

Examples

julia> c = VAR(:year);julia> print(render(c)):year

WHERE(; over = nothing, condition)WHERE(condition; over = nothing)

AWHERE clause.

Examples

julia> c = FROM(:location) |>           WHERE(OP("=", :zip, "60614")) |>           SELECT(:location_id);julia> print(render(c))SELECT "location_id"FROM "location"WHERE ("zip" = '60614')

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

AWINDOW 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(; over = nothing, recursive = false, args)WITH(args...; over = nothing, recursive = false)

AWITH clause.

Examples

julia> c = FROM(:person) |>           WHERE(OP("IN", :person_id,                          FROM(:essential_hypertension) |>                          SELECT(:person_id))) |>           SELECT(:person_id, :year_of_birth) |>           WITH(FROM(:condition_occurrence) |>                WHERE(OP("=", :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(OP("*")) |>           WITH(recursive = true,                FROM(:concept) |>                WHERE(OP("=", :concept_id, 320128)) |>                SELECT(:concept_id, :concept_name) |>                UNION(all = true,                      FROM(:eh => :essential_hypertension) |>                      JOIN(:cr => :concept_relationship,                           OP("=", (:eh, :concept_id), (:cr, :concept_id_1))) |>                      JOIN(:c => :concept,                           OP("=", (:cr, :concept_id_2), (:c, :concept_id))) |>                      WHERE(OP("=", (: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"