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 ]