---
name: "SQL::Abstract::Reference"
section: "3pm"
platform: "linux"
package: "libsql-abstract-perl"
version: "2.000001-2"
release: "Debian 13 (Trixie)"
platforms: ["linux"]
url: "https://new.carta.tech/SQL::Abstract::Reference"
---

# SQL::Abstract::Reference(3pm)

Reference documentation for SQL::Abstract

## NAME

SQL::Abstract::Reference - Reference documentation for SQL::Abstract

## TERMS

### Expression (expr)

The DWIM structure that's passed to most methods by default is referred to as expression syntax. If you see a variable with `"expr"` in the name, or a comment before a code block saying `"# expr"`, this is what's being described.

### Abstract Query Tree (aqt)

The explicit structure that an expression is converted into before it's rendered into SQL is referred to as an abstract query tree. If you see a variable with `"aqt"` in the name, or a comment before a code block saying `"# aqt"`, this is what's being described.

### SQL and Bind Values (query)

The final result of SQL::Abstract rendering is generally an SQL statement plus bind values for passing to DBI, ala:

```
  my ($sql, @bind) = $sqla->some_method(@args);
  my @hashes = @{$dbh->do($sql, { Slice => {} }, @bind)};
```

If you see a comment before a code block saying `"# query"`, the SQL + bind array is what's being described.

### Expander

An expander subroutine is written as:

```
  sub {
    my ($sqla, $name, $value, $k) = @_;
    ...
    return $aqt;
  }
```

`$name` is the expr node type for node expanders, the op name for op expanders, and the clause name for clause expanders.

`$value` is the body of the thing being expanded

If an op expander is being called as the binary operator in a "hashtriple" expression, `$k` will be the hash key to be used as the left hand side identifier.

This can trivially be converted to an `"ident"` type AQT node with:

```
  my $ident = $sqla->expand_expr({ -ident => $k });
```

### Renderer

A renderer subroutine looks like:

```
  sub {
    my ($sqla, $type, $value) = @_;
    ...
    $sqla->join_query_parts($join, @parts);
  }
```

and can be registered on a per-type, per-op or per-clause basis.

## AQT node types

An AQT node consists of a hashref with a single key, whose name is `"-type"` where 'type' is the node type, and whose value is the data for the node.

The following is an explanation of the built-in AQT type renderers; additional renderers can be registered as part of the extension system.

### literal

```
  # expr
  { -literal => [ 'SPANG(?, ?)', 1, 27 ] }
  # query
  SPANG(?, ?)
  [ 1, 27 ]
```

### ident

```
  # expr
  { -ident => 'foo' }
  # query
  foo
  []
  # expr
  { -ident => [ 'foo', 'bar' ] }
  # query
  foo.bar
  []
```

### bind

```
  # expr
  { -bind => [ 'colname', 'value' ] }
  # query
  ?
  [ 'value' ]
```

### row

```
  # expr
  {
    -row => [ { -bind => [ 'r', 1 ] }, { -ident => [ 'clown', 'car' ] } ]
  }
  # query
  (?, clown.car)
  [ 1 ]
```

### func

```
  # expr
  {
    -func => [ 'foo', { -ident => [ 'bar' ] }, { -bind => [ undef, 7 ] } ]
  }
  # query
  FOO(bar, ?)
  [ 7 ]
```

### op

Standard binop:

```
  # expr
  { -op => [
      '=', { -ident => [ 'bomb', 'status' ] },
      { -value => 'unexploded' },
  ] }
  # query
  bomb.status = ?
  [ 'unexploded' ]
```

Prefix unop:

```
  # expr
  { -op => [ '-', { -ident => 'foo' } ] }
  # query
  - foo
  []
```

Not as special case parenthesised unop:

```
  # expr
  { -op => [ 'not', { -ident => 'explosive' } ] }
  # query
  (NOT explosive)
  []
```

Postfix unop: (is\_null, is\_not\_null, asc, desc)

```
  # expr
  { -op => [ 'is_null', { -ident => [ 'bobby' ] } ] }
  # query
  bobby IS NULL
  []
```

AND and OR:

```
  # expr
  { -op =>
      [ 'and', { -ident => 'x' }, { -ident => 'y' }, { -ident => 'z' } ]
  }
  # query
  ( x AND y AND z )
  []
```

IN (and NOT IN):

```
  # expr
  { -op => [
      'in', { -ident => 'card' }, { -bind => [ 'card', 3 ] },
      { -bind => [ 'card', 'J' ] },
  ] }
  # query
  card IN ( ?, ? )
  [ 3, 'J' ]
```

BETWEEN (and NOT BETWEEN):

```
  # expr
  { -op => [
      'between', { -ident => 'pints' }, { -bind => [ 'pints', 2 ] },
      { -bind => [ 'pints', 4 ] },
  ] }
  # query
  ( pints BETWEEN ? AND ? )
  [ 2, 4 ]
```

Comma (use -row for parens):

```
  # expr
  { -op => [ ',', { -literal => [ 1 ] }, { -literal => [ 2 ] } ] }
  # query
  1, 2
  []
```

### values

```
  # expr
  { -values =>
      { -row => [ { -bind => [ undef, 1 ] }, { -bind => [ undef, 2 ] } ] }
  }
  # query
  VALUES (?, ?)
  [ 1, 2 ]
  # expr
  { -values => [
      { -row => [ { -literal => [ 1 ] }, { -literal => [ 2 ] } ] },
      { -row => [ { -literal => [ 3 ] }, { -literal => [ 4 ] } ] },
  ] }
  # query
  VALUES (1, 2), (3, 4)
  []
```

### keyword

```
  # expr
  { -keyword => 'insert_into' }
  # query
  INSERT INTO
  []
```

### statement types

AQT node types are also provided for `"select"`, `"insert"`, `"update"` and `"delete"`. These types are handled by the clauses system as discussed later.

## Expressions

### node expr

The simplest expression is just an AQT node:

```
  # expr
  { -ident => [ 'foo', 'bar' ] }
  # aqt
  { -ident => [ 'foo', 'bar' ] }
  # query
  foo.bar
  []
```

However, even in the case of an AQT node, the node value will be expanded if an expander has been registered for that node type:

```
  # expr
  { -ident => 'foo.bar' }
  # aqt
  { -ident => [ 'foo', 'bar' ] }
  # query
  foo.bar
  []
```

### identifier hashpair types

*hashtriple*

```
  # expr
  { id => { op => 'value' } }
  # aqt
  { -op =>
      [ 'op', { -ident => [ 'id' ] }, { -bind => [ 'id', 'value' ] } ]
  }
  # query
  id OP ?
  [ 'value' ]
```

If the value is undef, attempts to convert equality and like ops to IS NULL, and inequality and not like to IS NOT NULL:

```
  # expr
  { id => { '!=' => undef } }
  # aqt
  { -op => [ 'is_not_null', { -ident => [ 'id' ] } ] }
  # query
  id IS NOT NULL
  []
```

*identifier hashpair w/simple value*

Equivalent to a hashtriple with an op of '='.

```
  # expr
  { id => 'value' }
  # aqt
  {
    -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 'value' ] } ]
  }
  # query
  id = ?
  [ 'value' ]
```

(an object value will also follow this code path)

*identifier hashpair w/undef RHS*

Converted to IS NULL :

```
  # expr
  { id => undef }
  # aqt
  { -op => [ 'is_null', { -ident => [ 'id' ] } ] }
  # query
  id IS NULL
  []
```

(equivalent to the -is operator) :

```
  # expr
  { id => { -is => undef } }
  # aqt
  { -op => [ 'is_null', { -ident => [ 'id' ] } ] }
  # query
  id IS NULL
  []
```

*identifier hashpair w/literal RHS*

Directly appended to the key, remember you need to provide an operator:

```
  # expr
  { id => \"= dont_try_this_at_home" }
  # aqt
  { -literal => [ 'id = dont_try_this_at_home' ] }
  # query
  id = dont_try_this_at_home
  []
  # expr
  { id => \[
        "= seriously(?, ?, ?, ?)",
        "use",
        "-ident",
        "and",
        "-func",
      ]
  }
  # aqt
  { -literal =>
      [ 'id = seriously(?, ?, ?, ?)', 'use', -ident => 'and', '-func' ]
  }
  # query
  id = seriously(?, ?, ?, ?)
  [ 'use', -ident => 'and', '-func' ]
```

(you may absolutely use this when there's no built-in expression type for what you need and registering a custom one would be more hassle than it's worth, but, y'know, do try and avoid it)

*identifier hashpair w/arrayref value*

Becomes equivalent to a -or over an arrayref of hashrefs with the identifier as key and the member of the original arrayref as the value:

```
  # expr
  { id => [ 3, 4, { '>' => 12 } ] }
  # aqt
  { -op => [
      'or',
      { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
      { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
      {
        -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 12 ] } ]
      },
  ] }
  # query
  ( id = ? OR id = ? OR id > ? )
  [ 3, 4, 12 ]
  # expr
  { -or => [ { id => 3 }, { id => 4 }, { id => { '>' => 12 } } ] }
  # aqt
  { -op => [
      'or',
      { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
      { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
      {
        -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 12 ] } ]
      },
  ] }
  # query
  ( id = ? OR id = ? OR id > ? )
  [ 3, 4, 12 ]
```

Special Case: If the first element of the arrayref is -or or -and, that's used as the top level logic op:

```
  # expr
  { id => [ -and => { '>' => 3 }, { '<' => 6 } ] }
  # aqt
  { -op => [
      'and',
      { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
      { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 6 ] } ] },
  ] }
  # query
  ( id > ? AND id < ? )
  [ 3, 6 ]
```

*identifier hashpair w/hashref value*

Becomes equivalent to a -and over an arrayref of hashtriples constructed with the identifier as the key and each key/value pair of the original hashref as the value:

```
  # expr
  { id => { '<' => 4, '>' => 3 } }
  # aqt
  { -op => [
      'and',
      { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
      { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
  ] }
  # query
  ( id < ? AND id > ? )
  [ 4, 3 ]
```

is sugar for:

```
  # expr
  { -and => [ { id => { '<' => 4 } }, { id => { '>' => 3 } } ] }
  # aqt
  { -op => [
      'and',
      { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
      { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
  ] }
  # query
  ( id < ? AND id > ? )
  [ 4, 3 ]
```

### operator hashpair types

A hashpair whose key begins with a -, or whose key consists entirely of nonword characters (thereby covering '=', '\>', pg json ops, etc.) is processed as an operator hashpair.

*operator hashpair w/node type*

If a node type expander is registered for the key, the hashpair is treated as a "node expr".

*operator hashpair w/registered op*

If an expander is registered for the op name, that's run and the result returned:

```
  # expr
  { -in => [ 'foo', 1, 2, 3 ] }
  # aqt
  { -op => [
      'in', { -ident => [ 'foo' ] }, { -bind => [ undef, 1 ] },
      { -bind => [ undef, 2 ] }, { -bind => [ undef, 3 ] },
  ] }
  # query
  foo IN ( ?, ?, ? )
  [ 1, 2, 3 ]
```

*operator hashpair w/not prefix*

If the op name starts -not\_ this is stripped and turned into a -not wrapper around the result:

```
  # expr
  { -not_ident => 'foo' }
  # aqt
  { -op => [ 'not', { -ident => [ 'foo' ] } ] }
  # query
  (NOT foo)
  []
```

is equivalent to:

```
  # expr
  { -not => { -ident => 'foo' } }
  # aqt
  { -op => [ 'not', { -ident => [ 'foo' ] } ] }
  # query
  (NOT foo)
  []
```

*operator hashpair with unknown op*

If the `"unknown_unop_always_func"` option is set (which is recommended but defaults to off for backwards compatibility reasons), an unknown op expands into a `"-func"` node:

```
  # expr
  { -count => { -ident => '*' } }
  # aqt
  { -func => [ 'count', { -ident => [ '*' ] } ] }
  # query
  COUNT(*)
  []
```

If not, an unknown op will expand into a `"-op"` node.

### hashref expr

A hashref with more than one pair becomes a `"-and"` over its hashpairs, i.e.

```
  # expr
  { x => 1, y => 2 }
  # aqt
  { -op => [
      'and',
      { -op => [ '=', { -ident => [ 'x' ] }, { -bind => [ 'x', 1 ] } ] },
      { -op => [ '=', { -ident => [ 'y' ] }, { -bind => [ 'y', 2 ] } ] },
  ] }
  # query
  ( x = ? AND y = ? )
  [ 1, 2 ]
```

is short hand for:

```
  # expr
  { -and => [ { x => 1 }, { y => 2 } ] }
  # aqt
  { -op => [
      'and',
      { -op => [ '=', { -ident => [ 'x' ] }, { -bind => [ 'x', 1 ] } ] },
      { -op => [ '=', { -ident => [ 'y' ] }, { -bind => [ 'y', 2 ] } ] },
  ] }
  # query
  ( x = ? AND y = ? )
  [ 1, 2 ]
```

### arrayref expr

An arrayref becomes a `"-or"` over its contents. Arrayrefs, hashrefs and literals are all expanded and added to the clauses of the `"-or"`. If the arrayref contains a scalar it's treated as the key of a hashpair and the next element as the value.

```
  # expr
  [ { x => 1 }, [ { y => 2 }, { z => 3 } ], 'key', 'value', \"lit()" ]
  # aqt
  { -op => [
      'or',
      { -op => [ '=', { -ident => [ 'x' ] }, { -bind => [ 'x', 1 ] } ] },
      { -op => [
          'or', {
            -op => [ '=', { -ident => [ 'y' ] }, { -bind => [ 'y', 2 ] } ]
          }, {
            -op => [ '=', { -ident => [ 'z' ] }, { -bind => [ 'z', 3 ] } ]
          },
      ] }, { -op =>
          [
            '=', { -ident => [ 'key' ] },
            { -bind => [ 'key', 'value' ] },
          ]
      },
      { -literal => [ 'lit()' ] },
  ] }
  # query
  ( x = ? OR ( y = ? OR z = ? ) OR key = ? OR lit() )
  [ 1, 2, 3, 'value' ]
```

## Default Expanders

### bool

Turns the old -bool syntax into the value expression, i.e.

```
  # expr
  { -bool => { -ident => 'foo' } }
  # aqt
  { -ident => [ 'foo' ] }
  # query
  foo
  []
```

behaves the same way as the now-directly-supported

```
  # expr
  { -ident => 'foo' }
  # aqt
  { -ident => [ 'foo' ] }
  # query
  foo
  []
```

### row

Expands the elements of the value arrayref:

```
  # expr
  { -row => [ 1, { -ident => 'foo' }, 2, 3 ] }
  # aqt
  { -row => [
      { -bind => [ undef, 1 ] }, { -ident => [ 'foo' ] },
      { -bind => [ undef, 2 ] }, { -bind => [ undef, 3 ] },
  ] }
  # query
  (?, foo, ?, ?)
  [ 1, 2, 3 ]
```

### op

If an expander is registered for the op name, delegates to the expander; if not, expands the argument values:

```
  # expr
  { -op => [ 'ident', 'foo.bar' ] }
  # aqt
  { -ident => [ 'foo', 'bar' ] }
  # query
  foo.bar
  []
  # expr
  { -op => [ '=', { -ident => 'foo' }, 3 ] }
  # aqt
  { -op => [ '=', { -ident => [ 'foo' ] }, { -bind => [ undef, 3 ] } ] }
  # query
  foo = ?
  [ 3 ]
```

### func

Expands the argument values:

```
  # expr
  { -func => [ 'coalesce', { -ident => 'thing' }, 'fallback' ] }
  # aqt
  { -func => [
      'coalesce', { -ident => [ 'thing' ] },
      { -bind => [ undef, 'fallback' ] },
  ] }
  # query
  COALESCE(thing, ?)
  [ 'fallback' ]
```

### values

A hashref value is expanded as an expression:

```
  # expr
  { -values => { -row => [ 1, 2 ] } }
  # aqt
  { -values => [
      { -row => [ { -bind => [ undef, 1 ] }, { -bind => [ undef, 2 ] } ] }
  ] }
  # query
  VALUES (?, ?)
  [ 1, 2 ]
```

An arrayref value's elements are either expressions or arrayrefs to be treated as rows:

```
  # expr
  { -values => [ { -row => [ 1, 2 ] }, [ 3, 4 ] ] }
  # aqt
  { -values => [
      { -row => [ { -bind => [ undef, 1 ] }, { -bind => [ undef, 2 ] } ] },
      { -row => [ { -bind => [ undef, 3 ] }, { -bind => [ undef, 4 ] } ] },
  ] }
  # query
  VALUES (?, ?), (?, ?)
  [ 1, 2, 3, 4 ]
```

### list

Expects a value or an arrayref of values, expands them, and returns just the expanded aqt for a single entry or a comma operator for multiple:

```
  # expr
  { -list => [ { -ident => 'foo' } ] }
  # aqt
  { -op => [ ',', { -ident => [ 'foo' ] } ] }
  # query
  foo
  []
  # expr
  { -list => [ { -ident => 'foo' }, { -ident => 'bar' } ] }
  # aqt
  { -op => [ ',', { -ident => [ 'foo' ] }, { -ident => [ 'bar' ] } ] }
  # query
  foo, bar
  []
```

### between op

The RHS of between must either be a pair of exprs/plain values, or a single literal expr:

```
  # expr
  { -between => [ 'size', 3, { -ident => 'max_size' } ] }
  # aqt
  { -op => [
      'between', { -ident => [ 'size' ] }, { -bind => [ undef, 3 ] },
      { -ident => [ 'max_size' ] },
  ] }
  # query
  ( size BETWEEN ? AND max_size )
  [ 3 ]
  # expr
  { size => { -between => [ 3, { -ident => 'max_size' } ] } }
  # aqt
  { -op => [
      'between', { -ident => [ 'size' ] }, { -bind => [ 'size', 3 ] },
      { -ident => [ 'max_size' ] },
  ] }
  # query
  ( size BETWEEN ? AND max_size )
  [ 3 ]
  # expr
  { size => { -between => \"3 AND 7" } }
  # aqt
  { -op =>
      [
        'between', { -ident => [ 'size' ] },
        { -literal => [ '3 AND 7' ] },
      ]
  }
  # query
  ( size BETWEEN 3 AND 7 )
  []
```

not\_between is also expanded:

```
  # expr
  { size => { -not_between => [ 3, 7 ] } }
  # aqt
  { -op => [
      'not_between', { -ident => [ 'size' ] },
      { -bind => [ 'size', 3 ] }, { -bind => [ 'size', 7 ] },
  ] }
  # query
  ( size NOT BETWEEN ? AND ? )
  [ 3, 7 ]
```

### in op

The RHS of in/not\_in is either an expr/value or an arrayref of exprs/values:

```
  # expr
  { foo => { -in => [ 1, 2 ] } }
  # aqt
  { -op => [
      'in', { -ident => [ 'foo' ] }, { -bind => [ 'foo', 1 ] },
      { -bind => [ 'foo', 2 ] },
  ] }
  # query
  foo IN ( ?, ? )
  [ 1, 2 ]
  # expr
  { bar => { -not_in => \"(1, 2)" } }
  # aqt
  { -op =>
      [ 'not_in', { -ident => [ 'bar' ] }, { -literal => [ '1, 2' ] } ]
  }
  # query
  bar NOT IN ( 1, 2 )
  []
```

A non-trivial LHS is expanded with ident as the default rather than value:

```
  # expr
  { -in => [
      { -row => [ 'x', 'y' ] }, { -row => [ 1, 2 ] },
      { -row => [ 3, 4 ] },
  ] }
  # aqt
  { -op => [
      'in', { -row => [ { -ident => [ 'x' ] }, { -ident => [ 'y' ] } ] },
      { -row => [ { -bind => [ undef, 1 ] }, { -bind => [ undef, 2 ] } ] },
      { -row => [ { -bind => [ undef, 3 ] }, { -bind => [ undef, 4 ] } ] },
  ] }
  # query
  (x, y) IN ( (?, ?), (?, ?) )
  [ 1, 2, 3, 4 ]
```

### and/or ops

expands the same way as a plain arrayref/hashref expression but with the logic type set to the op name.

### is op

Expands is and is\_not to null checks, RHS value must be undef:

```
  # expr
  { -is => [ 'foo', undef ] }
  # aqt
  { -op => [ 'is_null', { -ident => [ 'foo' ] } ] }
  # query
  foo IS NULL
  []
  # expr
  { bar => { -is_not => undef } }
  # aqt
  { -op => [ 'is_not_null', { -ident => [ 'bar' ] } ] }
  # query
  bar IS NOT NULL
  []
```

### ident op

Expands a string ident to an arrayref by splitting on the configured separator, almost always '.':

```
  # expr
  { -ident => 'foo.bar' }
  # aqt
  { -ident => [ 'foo', 'bar' ] }
  # query
  foo.bar
  []
```

### value op

Expands to a bind node with the currently applicable column name if known:

```
  # expr
  { foo => { '=' => { -value => 3 } } }
  # aqt
  { -op => [ '=', { -ident => [ 'foo' ] }, { -bind => [ 'foo', 3 ] } ] }
  # query
  foo = ?
  [ 3 ]
```

## Query Types

### select

A select node accepts select, from, where and order\_by clauses.

The select clause is expanded as a list expression with a -ident default:

```
  # expr
  { -select => { _ => [ 'foo', 'bar', { -count => 'baz' } ] } }
  # aqt
  { -select => { select => { -op => [
          ',', { -ident => [ 'foo' ] }, { -ident => [ 'bar' ] },
          { -func => [ 'count', { -ident => [ 'baz' ] } ] },
  ] } } }
  # query
  SELECT foo, bar, COUNT(baz)
  []
```

The from clause is expanded as a list expression with a -ident default:

```
  # expr
  { -select => {
      from => [ 'schema1.table1', { -ident => [ 'schema2', 'table2' ] } ]
  } }
  # aqt
  { -select => { from => { -from_list => [
          { -ident => [ 'schema1', 'table1' ] },
          { -ident => [ 'schema2', 'table2' ] },
  ] } } }
  # query
  FROM schema1.table1, schema2.table2
  []
```

The where clause is expanded as a plain expression:

```
  # expr
  { -select => { where => { foo => 3 } } }
  # aqt
  { -select => { where => {
        -op => [ '=', { -ident => [ 'foo' ] }, { -bind => [ 'foo', 3 ] } ]
  } } }
  # query
  WHERE foo = ?
  [ 3 ]
```

The order\_by clause expands as a list expression at top level, but a hashref element may be either an expr or a hashpair with key -asc or -desc to indicate an order by direction:

```
  # expr
  { -select =>
      { order_by => [ 'foo', { -desc => 'bar' }, { -max => 'baz' } ] }
  }
  # aqt
  { -select => { order_by => { -op => [
          ',', { -ident => [ 'foo' ] }, {
            -op => [ ',', { -op => [ 'desc', { -ident => [ 'bar' ] } ] } ]
          }, { -func => [ 'max', { -ident => [ 'baz' ] } ] },
  ] } } }
  # query
  ORDER BY foo, bar DESC, MAX(baz)
  []
```

###

An insert node accepts an into/target clause, a fields clause, a values/from clause, and a returning clause.

The target clause is expanded with an ident default.

The fields clause is expanded as a list expression if an arrayref, and otherwise passed through.

The from clause may either be an expr, a literal, an arrayref of column values, or a hashref mapping colum names to values.

The returning clause is expanded as a list expr with an ident default.

```
  # expr
  { -insert => {
      into => 'foo',
      returning => 'id',
      values => { bar => 'yay', baz => 'argh' },
  } }
  # aqt
  { -insert => {
      fields =>
        { -row => [ { -ident => [ 'bar' ] }, { -ident => [ 'baz' ] } ] },
      from => { -values => [ { -row => [
              { -bind => [ 'bar', 'yay' ] },
              { -bind => [ 'baz', 'argh' ] },
      ] } ] },
      returning => { -op => [ ',', { -ident => [ 'id' ] } ] },
      target => { -ident => [ 'foo' ] },
  } }
  # query
  INSERT INTO foo (bar, baz) VALUES (?, ?) RETURNING id
  [ 'yay', 'argh' ]
  # expr
  { -insert => {
      fields => [ 'bar', 'baz' ],
      from => { -select => { _ => [ 'bar', 'baz' ], from => 'other' } },
      into => 'foo',
  } }
  # aqt
  { -insert => {
      fields => { -row => [ { -op =>
              [ ',', { -ident => [ 'bar' ] }, { -ident => [ 'baz' ] } ]
      } ] },
      from => { -select => {
          from => { -ident => [ 'other' ] },
          select => { -op =>
              [ ',', { -ident => [ 'bar' ] }, { -ident => [ 'baz' ] } ]
          },
      } },
      target => { -ident => [ 'foo' ] },
  } }
  # query
  INSERT INTO foo (bar, baz) SELECT bar, baz FROM other
  []
```

### update

An update node accepts update/target (either may be used at expansion time), set, where, and returning clauses.

The target clause is expanded with an ident default.

The set clause (if not already a list expr) is expanded as a hashref where the keys are identifiers to be set and the values are exprs/values.

The where clauses is expanded as a normal expr.

The returning clause is expanded as a list expr with an ident default.

```
  # expr
  { -update => {
      _ => 'foo',
      returning => [ 'id', 'baz' ],
      set => { bar => 3, baz => { baz => { '+' => 1 } } },
      where => { -not => { -ident => 'quux' } },
  } }
  # aqt
  { -update => {
      returning =>
        {
          -op => [ ',', { -ident => [ 'id' ] }, { -ident => [ 'baz' ] } ]
        },
      set => { -op => [
          ',', { -op =>
              [ '=', { -ident => [ 'bar' ] }, { -bind => [ 'bar', 3 ] } ]
          }, { -op => [
              '=', { -ident => [ 'baz' ] }, { -op => [
                  '+', { -ident => [ 'baz' ] },
                  { -bind => [ 'baz', 1 ] },
              ] },
          ] },
      ] },
      target => { -ident => [ 'foo' ] },
      where => { -op => [ 'not', { -ident => [ 'quux' ] } ] },
  } }
  # query
  UPDATE foo SET bar = ?, baz = baz + ? WHERE (NOT quux) RETURNING id, baz
  [ 3, 1 ]
```

### delete

delete accepts from/target, where, and returning clauses.

The target clause is expanded with an ident default.

The where clauses is expanded as a normal expr.

The returning clause is expanded as a list expr with an ident default.

```
  # expr
  { -delete => {
      from => 'foo',
      returning => 'id',
      where => { bar => { '<' => 10 } },
  } }
  # aqt
  { -delete => {
      returning => { -op => [ ',', { -ident => [ 'id' ] } ] },
      target => { -op => [ ',', { -ident => [ 'foo' ] } ] },
      where => { -op =>
          [ '<', { -ident => [ 'bar' ] }, { -bind => [ 'bar', 10 ] } ]
      },
  } }
  # query
  DELETE FROM foo WHERE bar < ? RETURNING id
  [ 10 ]
```