Queries and DML#

Business Language includes SQL-like query declarations for reads and data-changing operations.

Select#

select id, name as customer_name
from Customers c
where c.active == true
order by c.name asc nulls last
limit 10
offset 20;

Select items can be *, alias.*, aggregate functions, window functions, or general expressions.

Select sources and locking#

Select can bind a typed variable, read from tables, subqueries, values clauses, and table-like functions, and request update locking.

select var selected_customer: CustomerId
from Customers;

select *
from (select * from Customers where active == true) c
for update;

select *
from customer_search(company_code_param, "active") s;

select *
from values (1, "open"), (2, "closed") v;

Use for update only when the caller owns the transaction boundary and intends to update the selected rows.

Limiters and controllers#

The select grammar accepts at most one with limiter or with controller clause after any limit and offset clauses and before for update.

select *
from Vendor
where active == true
order by vendor_name
with limiter page_limiter;

select *
from PostingWorkQueue
where ready == true
with controller posting_controller;

Query expressions#

LINQ-style query expressions are expression syntax, not SQL statement syntax. They are useful inside let, return, arguments, and object construction.

let overdue_invoice_ids =
  from invoice in invoices
  where invoice.due_date < today()
  orderby invoice.due_date ascending
  select invoice.invoice_id;

Joins#

select *
from Orders o
inner join Customers c on o.customer_id == c.customer_id
left outer join Regions r on c.region_id == r.region_id;

Supported join families are inner, left, right, full, and cross.

Where predicates#

select * from Invoice where amount between 10 and 100;
select * from Invoice where amount not between 10 and 100;
select * from Invoice where name like "A%";
select * from Invoice where name matches "^INV-[0-9]+$";
select * from Invoice where id in (1, 2, 3);
select * from Invoice where code not in ["A", "B"];
select * from Invoice where deleted_at is null;
select * from Invoice where approved_at is not null;
select * from Invoice where description contains "urgent";
select * from Invoice where result contains errors;
select *
from Invoice
where exists (
  select *
  from PostingPolicy p
  where p.company_code == company_code_param
);

Grouping and aggregates#

select region, count(*) as invoice_count, sum(amount) as total
from Invoice
group by region
having count(*) > 1;

Grouping supports rollup, cube, and grouping sets.

select count(*) as invoice_count from Invoice group by rollup(region, company_code);
select count(*) as invoice_count from Invoice group by cube(region, company_code);
select count(*) as invoice_count from Invoice group by grouping sets ((region), (company_code));

Aggregates include count, sum, avg, min, max, stddev, variance, median, percentile_cont, percentile_disc, string_agg, and array_agg.

Aggregate arguments can use distinct or all, and aggregates can include a filter clause.

select count(distinct vendor_id) as vendor_count
from Invoice;

select sum(amount) filter (where status == "posted") as posted_total
from Invoice;

Window functions#

select
  invoice_id,
  row_number() over (partition by vendor_id order by due_date) as row_no
from Invoice;

Window-capable functions include row_number, rank, dense_rank, lag, lead, first_value, last_value, nth_value, and aggregate functions.

CTEs and unions#

with open_invoices as (
  select *
  from Invoice
  where status == "open"
)
select *
from open_invoices;

with recursive account_tree(account_id, parent_id) as (
  select account_id, parent_id
  from Account
)
select *
from account_tree;

select id from Customers
union all
select id from Vendors;

select id from Customers
union distinct
select id from Vendors;

select id from Customers
intersect all
select id from ActiveParties;

select id from Customers
except all
select id from BlockedParties;

Set operators include union, intersect, and except.

Insert#

insert into Customers (customer_id, name)
values (customer_id_param, name_param)
on conflict (customer_id) do update set name = name_param
returning customer_id;

insert into CustomerArchive
select *
from Customers
where inactive == true;

insert into Customers (customer_id, name)
values (customer_id_param, name_param)
on conflict do nothing
returning customer_id as id;

Update#

update Customers
set name = name_param, updated_at = now()
where customer_id == customer_id_param
returning customer_id;

update Customers
set region_id = r.region_id
from Regions r
where Customers.country == r.country
returning Customers.customer_id as id;

Delete#

delete from Customers
where customer_id == customer_id_param
returning customer_id;

Merge#

merge into Customers as c
using IncomingCustomers on c.customer_id == IncomingCustomers.customer_id
when matched and IncomingCustomers.name != c.name then update set name = IncomingCustomers.name
when matched and IncomingCustomers.deleted == true then delete
when not matched and IncomingCustomers.deleted == false then insert (customer_id, name) values (IncomingCustomers.customer_id, IncomingCustomers.name);

Save#

save checkpoint_name;
save Customer from customer_expr;
save (payload_expr);