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 ]