API Reference

FunSQL.FunSQLError — Type

Base error class for all errors raised by FunSQL.

FunSQL.render — Function

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

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

SQL Dialects

FunSQL.SQLDialect — Type

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 constructor SQLDialect(name::Symbol) to create one of the known dialects: :postgresql, :sqlite, :mysql, :redshift, :sqlserver.

SQL Entities

FunSQL.SQLTable — Type

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.

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

Examples

julia> t = SQLTable(:location,
                    :location_id, :address_1, :address_2, :city, :state, :zip);


julia> show(t.name)
:location

julia> 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)
:public

julia> show(t.name)
:person

julia> show(t.columns)
[:person_id, :birth_datetime, :location_id]

SQL Statements

FunSQL.SQLStatement — Type

Prepared SQL statement.

FunSQL.pack — Function

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

Convert named parameters to positional form.

Semantic Structure

FunSQL.AbstractSQLNode — Type

A SQL expression.

FunSQL.SQLNode — Type

An opaque wrapper over an arbitrary SQL node.

FunSQL.Agg — Method

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

FunSQL.Append — Method

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

Append concatenates input datasets.

SELECT ...
FROM $over
UNION ALL
SELECT ...
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"

FunSQL.As — Method

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);

FunSQL.Bind — Method

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

The Bind 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")
))

FunSQL.Define — Method

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')

FunSQL.From — Method

From(; source)
From(source)

From outputs the content of a database table. The parameter source could be a SQLTable object, a symbol (for use with With), or nothing.

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

FunSQL.Fun — Method

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

FunSQL.Get — Method

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);

FunSQL.Group — Method

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

The Group node partitions the input rows into disjoint groups by the given grouping key and outputs all unique values of the key. Aggregate functions applied to the output of Group summarize the values from the rows of each group.

SELECT ...
FROM $over
GROUP 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"

FunSQL.Highlight — Method

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

Highlight over with the given color.

Available colors can be found in Base.text_colors.

Examples

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

FunSQL.Iterate — Method

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 should have an alias specified with As; it can refer to the output of the previous iteration using From 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"

FunSQL.Join — Method

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.

When optional is set, the JOIN clause is omitted if the output contains no data from the joinee branch.

SELECT ...
FROM $over
JOIN $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")

FunSQL.LeftJoin — Method

An alias for Join(...; ..., left = true).

FunSQL.Limit — Method

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.

SELECT ...
FROM $over
OFFSET $offset ROWS
FETCH 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

FunSQL.Lit — Method

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"

FunSQL.Order — Method

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

Order sorts the input rows by the given key.

SELECT ...
FROM $over
ORDER 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"

FunSQL.Partition — Method

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

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

SELECT ...
FROM $over
WINDOW 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"

FunSQL.Select — Method

Select(; over; args)
Select(args...; over)

The Select 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"

FunSQL.Asc — Method

Asc(; over = nothing, nulls = nothing)

Ascending order indicator.

FunSQL.Desc — Method

Desc(; over = nothing, nulls = nothing)

Descending order indicator.

FunSQL.Sort — Method

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

FunSQL.Var — Method

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)

FunSQL.Where — Method

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

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

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

FunSQL.With — Method

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 the over 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"
))

FunSQL.WithExternal — Method

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 the over query.

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), ";");

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

Syntactic Structure

FunSQL.AbstractSQLClause — Type

A part of a SQL query.

FunSQL.SQLClause — Type

An opaque wrapper over an arbitrary SQL clause.

FunSQL.AGG — Method

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

FunSQL.AS — Method

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

FunSQL.CASE — Method

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

A CASE 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)

FunSQL.FROM — Method

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"

FunSQL.FUN — Method

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())

FunSQL.GROUP — Method

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", OP("*")));

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

FunSQL.HAVING — Method

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

A HAVING 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)

FunSQL.ID — Method

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

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"

FunSQL.JOIN — Method

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

FunSQL.KW — Method

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)

FunSQL.LIMIT — Method

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

FunSQL.LIT — Method

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!'

FunSQL.NOTE — Method

NOTE(; over = nothing, text, postfix = false)
NOTE(text; over = nothing, postfix = false)

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

FunSQL.OP — Method

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'))

FunSQL.ORDER — Method

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

A 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"

FunSQL.PARTITION — Method

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"

FunSQL.SELECT — Method

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"

FunSQL.ASC — Method

ASC(; over = nothing, nulls = nothing)

Ascending order indicator.

FunSQL.DESC — Method

DESC(; over = nothing, nulls = nothing)

Descending order indicator.

FunSQL.SORT — Method

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

FunSQL.UNION — Method

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"

FunSQL.VAR — Method

VAR(; name)
VAR(name)

A placeholder in a parameterized query.

Examples

julia> c = VAR(:year);

julia> print(render(c))
:year

FunSQL.WHERE — Method

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

A WHERE 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')

FunSQL.WINDOW — Method

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

FunSQL.WITH — Method

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

A WITH 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"