.\" Automatically generated by Pod::Man 4.14 (Pod::Simple 3.40)
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" Set up some character translations and predefined strings.  \*(-- will
.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
.\" double quote, and \*(R" will give a right double quote.  \*(C+ will
.\" give a nicer C++.  Capital omega is used to do unbreakable dashes and
.\" therefore won't be available.  \*(C` and \*(C' expand to `' in nroff,
.\" nothing in troff, for use with C<>.
.tr \(*W-
.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
.ie n \{\
.    ds -- \(*W-
.    ds PI pi
.    if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
.    if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\"  diablo 12 pitch
.    ds L" ""
.    ds R" ""
.    ds C` ""
.    ds C' ""
'br\}
.el\{\
.    ds -- \|\(em\|
.    ds PI \(*p
.    ds L" ``
.    ds R" ''
.    ds C`
.    ds C'
'br\}
.\"
.\" Escape single quotes in literal strings from groff's Unicode transform.
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\"
.\" If the F register is >0, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index
.\" entries marked with X<> in POD.  Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.\"
.\" Avoid warning from groff about undefined register 'F'.
.de IX
..
.nr rF 0
.if \n(.g .if rF .nr rF 1
.if (\n(rF:(\n(.g==0)) \{\
.    if \nF \{\
.        de IX
.        tm Index:\\$1\t\\n%\t"\\$2"
..
.        if !\nF==2 \{\
.            nr % 0
.            nr F 2
.        \}
.    \}
.\}
.rr rF
.\" ========================================================================
.\"
.IX Title "SQL::Abstract::Reference 3pm"
.TH SQL::Abstract::Reference 3pm "2021-09-30" "perl v5.32.1" "User Contributed Perl Documentation"
.\" For nroff, turn off justification.  Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.if n .ad l
.nh
.SH "NAME"
SQL::Abstract::Reference \- Reference documentation for SQL::Abstract
.SH "TERMS"
.IX Header "TERMS"
.SS "Expression (expr)"
.IX Subsection "Expression (expr)"
The \s-1DWIM\s0 structure that's passed to most methods by default is referred to
as expression syntax. If you see a variable with \f(CW\*(C`expr\*(C'\fR in the name, or a
comment before a code block saying \f(CW\*(C`# expr\*(C'\fR, this is what's being described.
.SS "Abstract Query Tree (aqt)"
.IX Subsection "Abstract Query Tree (aqt)"
The explicit structure that an expression is converted into before it's
rendered into \s-1SQL\s0 is referred to as an abstract query tree. If you see a
variable with \f(CW\*(C`aqt\*(C'\fR in the name, or a comment before a code block saying
\&\f(CW\*(C`# aqt\*(C'\fR, this is what's being described.
.SS "\s-1SQL\s0 and Bind Values (query)"
.IX Subsection "SQL and Bind Values (query)"
The final result of SQL::Abstract rendering is generally an \s-1SQL\s0 statement
plus bind values for passing to \s-1DBI,\s0 ala:
.PP
.Vb 2
\&  my ($sql, @bind) = $sqla\->some_method(@args);
\&  my @hashes = @{$dbh\->do($sql, { Slice => {} }, @bind)};
.Ve
.PP
If you see a comment before a code block saying \f(CW\*(C`# query\*(C'\fR, the \s-1SQL +\s0 bind
array is what's being described.
.SS "Expander"
.IX Subsection "Expander"
An expander subroutine is written as:
.PP
.Vb 5
\&  sub {
\&    my ($sqla, $name, $value, $k) = @_;
\&    ...
\&    return $aqt;
\&  }
.Ve
.PP
\&\f(CW$name\fR is the expr node type for node expanders, the op name for op
expanders, and the clause name for clause expanders.
.PP
\&\f(CW$value\fR is the body of the thing being expanded
.PP
If an op expander is being called as the binary operator in a \*(L"hashtriple\*(R"
expression, \f(CW$k\fR will be the hash key to be used as the left hand side
identifier.
.PP
This can trivially be converted to an \f(CW\*(C`ident\*(C'\fR type \s-1AQT\s0 node with:
.PP
.Vb 1
\&  my $ident = $sqla\->expand_expr({ \-ident => $k });
.Ve
.SS "Renderer"
.IX Subsection "Renderer"
A renderer subroutine looks like:
.PP
.Vb 5
\&  sub {
\&    my ($sqla, $type, $value) = @_;
\&    ...
\&    $sqla\->join_query_parts($join, @parts);
\&  }
.Ve
.PP
and can be registered on a per-type, per-op or per-clause basis.
.SH "AQT node types"
.IX Header "AQT node types"
An \s-1AQT\s0 node consists of a hashref with a single key, whose name is \f(CW\*(C`\-type\*(C'\fR
where 'type' is the node type, and whose value is the data for the node.
.PP
The following is an explanation of the built-in \s-1AQT\s0 type renderers;
additional renderers can be registered as part of the extension system.
.SS "literal"
.IX Subsection "literal"
.Vb 2
\&  # expr
\&  { \-literal => [ \*(AqSPANG(?, ?)\*(Aq, 1, 27 ] }
\&
\&  # query
\&  SPANG(?, ?)
\&  [ 1, 27 ]
.Ve
.SS "ident"
.IX Subsection "ident"
.Vb 2
\&  # expr
\&  { \-ident => \*(Aqfoo\*(Aq }
\&
\&  # query
\&  foo
\&  []
\&
\&  # expr
\&  { \-ident => [ \*(Aqfoo\*(Aq, \*(Aqbar\*(Aq ] }
\&
\&  # query
\&  foo.bar
\&  []
.Ve
.SS "bind"
.IX Subsection "bind"
.Vb 2
\&  # expr
\&  { \-bind => [ \*(Aqcolname\*(Aq, \*(Aqvalue\*(Aq ] }
\&
\&  # query
\&  ?
\&  [ \*(Aqvalue\*(Aq ]
.Ve
.SS "row"
.IX Subsection "row"
.Vb 4
\&  # expr
\&  {
\&    \-row => [ { \-bind => [ \*(Aqr\*(Aq, 1 ] }, { \-ident => [ \*(Aqclown\*(Aq, \*(Aqcar\*(Aq ] } ]
\&  }
\&
\&  # query
\&  (?, clown.car)
\&  [ 1 ]
.Ve
.SS "func"
.IX Subsection "func"
.Vb 4
\&  # expr
\&  {
\&    \-func => [ \*(Aqfoo\*(Aq, { \-ident => [ \*(Aqbar\*(Aq ] }, { \-bind => [ undef, 7 ] } ]
\&  }
\&
\&  # query
\&  FOO(bar, ?)
\&  [ 7 ]
.Ve
.SS "op"
.IX Subsection "op"
Standard binop:
.PP
.Vb 5
\&  # expr
\&  { \-op => [
\&      \*(Aq=\*(Aq, { \-ident => [ \*(Aqbomb\*(Aq, \*(Aqstatus\*(Aq ] },
\&      { \-value => \*(Aqunexploded\*(Aq },
\&  ] }
\&
\&  # query
\&  bomb.status = ?
\&  [ \*(Aqunexploded\*(Aq ]
.Ve
.PP
Prefix unop:
.PP
.Vb 2
\&  # expr
\&  { \-op => [ \*(Aq\-\*(Aq, { \-ident => \*(Aqfoo\*(Aq } ] }
\&
\&  # query
\&  \- foo
\&  []
.Ve
.PP
Not as special case parenthesised unop:
.PP
.Vb 2
\&  # expr
\&  { \-op => [ \*(Aqnot\*(Aq, { \-ident => \*(Aqexplosive\*(Aq } ] }
\&
\&  # query
\&  (NOT explosive)
\&  []
.Ve
.PP
Postfix unop: (is_null, is_not_null, asc, desc)
.PP
.Vb 2
\&  # expr
\&  { \-op => [ \*(Aqis_null\*(Aq, { \-ident => [ \*(Aqbobby\*(Aq ] } ] }
\&
\&  # query
\&  bobby IS NULL
\&  []
.Ve
.PP
\&\s-1AND\s0 and \s-1OR:\s0
.PP
.Vb 4
\&  # expr
\&  { \-op =>
\&      [ \*(Aqand\*(Aq, { \-ident => \*(Aqx\*(Aq }, { \-ident => \*(Aqy\*(Aq }, { \-ident => \*(Aqz\*(Aq } ]
\&  }
\&
\&  # query
\&  ( x AND y AND z )
\&  []
.Ve
.PP
\&\s-1IN\s0 (and \s-1NOT IN\s0):
.PP
.Vb 5
\&  # expr
\&  { \-op => [
\&      \*(Aqin\*(Aq, { \-ident => \*(Aqcard\*(Aq }, { \-bind => [ \*(Aqcard\*(Aq, 3 ] },
\&      { \-bind => [ \*(Aqcard\*(Aq, \*(AqJ\*(Aq ] },
\&  ] }
\&
\&  # query
\&  card IN ( ?, ? )
\&  [ 3, \*(AqJ\*(Aq ]
.Ve
.PP
\&\s-1BETWEEN\s0 (and \s-1NOT BETWEEN\s0):
.PP
.Vb 5
\&  # expr
\&  { \-op => [
\&      \*(Aqbetween\*(Aq, { \-ident => \*(Aqpints\*(Aq }, { \-bind => [ \*(Aqpints\*(Aq, 2 ] },
\&      { \-bind => [ \*(Aqpints\*(Aq, 4 ] },
\&  ] }
\&
\&  # query
\&  ( pints BETWEEN ? AND ? )
\&  [ 2, 4 ]
.Ve
.PP
Comma (use \-row for parens):
.PP
.Vb 2
\&  # expr
\&  { \-op => [ \*(Aq,\*(Aq, { \-literal => [ 1 ] }, { \-literal => [ 2 ] } ] }
\&
\&  # query
\&  1, 2
\&  []
.Ve
.SS "values"
.IX Subsection "values"
.Vb 4
\&  # 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)
\&  []
.Ve
.SS "keyword"
.IX Subsection "keyword"
.Vb 2
\&  # expr
\&  { \-keyword => \*(Aqinsert_into\*(Aq }
\&
\&  # query
\&  INSERT INTO
\&  []
.Ve
.SS "statement types"
.IX Subsection "statement types"
\&\s-1AQT\s0 node types are also provided for \f(CW\*(C`select\*(C'\fR, \f(CW\*(C`insert\*(C'\fR, \f(CW\*(C`update\*(C'\fR and
\&\f(CW\*(C`delete\*(C'\fR. These types are handled by the clauses system as discussed later.
.SH "Expressions"
.IX Header "Expressions"
.SS "node expr"
.IX Subsection "node expr"
The simplest expression is just an \s-1AQT\s0 node:
.PP
.Vb 2
\&  # expr
\&  { \-ident => [ \*(Aqfoo\*(Aq, \*(Aqbar\*(Aq ] }
\&
\&  # aqt
\&  { \-ident => [ \*(Aqfoo\*(Aq, \*(Aqbar\*(Aq ] }
\&
\&  # query
\&  foo.bar
\&  []
.Ve
.PP
However, even in the case of an \s-1AQT\s0 node, the node value will be expanded if
an expander has been registered for that node type:
.PP
.Vb 2
\&  # expr
\&  { \-ident => \*(Aqfoo.bar\*(Aq }
\&
\&  # aqt
\&  { \-ident => [ \*(Aqfoo\*(Aq, \*(Aqbar\*(Aq ] }
\&
\&  # query
\&  foo.bar
\&  []
.Ve
.SS "identifier hashpair types"
.IX Subsection "identifier hashpair types"
\fIhashtriple\fR
.IX Subsection "hashtriple"
.PP
.Vb 2
\&  # expr
\&  { id => { op => \*(Aqvalue\*(Aq } }
\&
\&  # aqt
\&  { \-op =>
\&      [ \*(Aqop\*(Aq, { \-ident => [ \*(Aqid\*(Aq ] }, { \-bind => [ \*(Aqid\*(Aq, \*(Aqvalue\*(Aq ] } ]
\&  }
\&
\&  # query
\&  id OP ?
\&  [ \*(Aqvalue\*(Aq ]
.Ve
.PP
If the value is undef, attempts to convert equality and like ops to \s-1IS NULL,\s0
and inequality and not like to \s-1IS NOT NULL:\s0
.PP
.Vb 2
\&  # expr
\&  { id => { \*(Aq!=\*(Aq => undef } }
\&
\&  # aqt
\&  { \-op => [ \*(Aqis_not_null\*(Aq, { \-ident => [ \*(Aqid\*(Aq ] } ] }
\&
\&  # query
\&  id IS NOT NULL
\&  []
.Ve
.PP
\fIidentifier hashpair w/simple value\fR
.IX Subsection "identifier hashpair w/simple value"
.PP
Equivalent to a hashtriple with an op of '='.
.PP
.Vb 2
\&  # expr
\&  { id => \*(Aqvalue\*(Aq }
\&
\&  # aqt
\&  {
\&    \-op => [ \*(Aq=\*(Aq, { \-ident => [ \*(Aqid\*(Aq ] }, { \-bind => [ \*(Aqid\*(Aq, \*(Aqvalue\*(Aq ] } ]
\&  }
\&
\&  # query
\&  id = ?
\&  [ \*(Aqvalue\*(Aq ]
.Ve
.PP
(an object value will also follow this code path)
.PP
\fIidentifier hashpair w/undef \s-1RHS\s0\fR
.IX Subsection "identifier hashpair w/undef RHS"
.PP
Converted to \s-1IS NULL :\s0
.PP
.Vb 2
\&  # expr
\&  { id => undef }
\&
\&  # aqt
\&  { \-op => [ \*(Aqis_null\*(Aq, { \-ident => [ \*(Aqid\*(Aq ] } ] }
\&
\&  # query
\&  id IS NULL
\&  []
.Ve
.PP
(equivalent to the \-is operator) :
.PP
.Vb 2
\&  # expr
\&  { id => { \-is => undef } }
\&
\&  # aqt
\&  { \-op => [ \*(Aqis_null\*(Aq, { \-ident => [ \*(Aqid\*(Aq ] } ] }
\&
\&  # query
\&  id IS NULL
\&  []
.Ve
.PP
\fIidentifier hashpair w/literal \s-1RHS\s0\fR
.IX Subsection "identifier hashpair w/literal RHS"
.PP
Directly appended to the key, remember you need to provide an operator:
.PP
.Vb 2
\&  # expr
\&  { id => \e"= dont_try_this_at_home" }
\&
\&  # aqt
\&  { \-literal => [ \*(Aqid = dont_try_this_at_home\*(Aq ] }
\&
\&  # query
\&  id = dont_try_this_at_home
\&  []
\&
\&  # expr
\&  { id => \e[
\&        "= seriously(?, ?, ?, ?)",
\&        "use",
\&        "\-ident",
\&        "and",
\&        "\-func",
\&      ]
\&  }
\&
\&  # aqt
\&  { \-literal =>
\&      [ \*(Aqid = seriously(?, ?, ?, ?)\*(Aq, \*(Aquse\*(Aq, \-ident => \*(Aqand\*(Aq, \*(Aq\-func\*(Aq ]
\&  }
\&
\&  # query
\&  id = seriously(?, ?, ?, ?)
\&  [ \*(Aquse\*(Aq, \-ident => \*(Aqand\*(Aq, \*(Aq\-func\*(Aq ]
.Ve
.PP
(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)
.PP
\fIidentifier hashpair w/arrayref value\fR
.IX Subsection "identifier hashpair w/arrayref value"
.PP
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:
.PP
.Vb 2
\&  # expr
\&  { id => [ 3, 4, { \*(Aq>\*(Aq => 12 } ] }
\&
\&  # aqt
\&  { \-op => [
\&      \*(Aqor\*(Aq,
\&      { \-op => [ \*(Aq=\*(Aq, { \-ident => [ \*(Aqid\*(Aq ] }, { \-bind => [ \*(Aqid\*(Aq, 3 ] } ] },
\&      { \-op => [ \*(Aq=\*(Aq, { \-ident => [ \*(Aqid\*(Aq ] }, { \-bind => [ \*(Aqid\*(Aq, 4 ] } ] },
\&      {
\&        \-op => [ \*(Aq>\*(Aq, { \-ident => [ \*(Aqid\*(Aq ] }, { \-bind => [ \*(Aqid\*(Aq, 12 ] } ]
\&      },
\&  ] }
\&
\&  # query
\&  ( id = ? OR id = ? OR id > ? )
\&  [ 3, 4, 12 ]
\&
\&  # expr
\&  { \-or => [ { id => 3 }, { id => 4 }, { id => { \*(Aq>\*(Aq => 12 } } ] }
\&
\&  # aqt
\&  { \-op => [
\&      \*(Aqor\*(Aq,
\&      { \-op => [ \*(Aq=\*(Aq, { \-ident => [ \*(Aqid\*(Aq ] }, { \-bind => [ \*(Aqid\*(Aq, 3 ] } ] },
\&      { \-op => [ \*(Aq=\*(Aq, { \-ident => [ \*(Aqid\*(Aq ] }, { \-bind => [ \*(Aqid\*(Aq, 4 ] } ] },
\&      {
\&        \-op => [ \*(Aq>\*(Aq, { \-ident => [ \*(Aqid\*(Aq ] }, { \-bind => [ \*(Aqid\*(Aq, 12 ] } ]
\&      },
\&  ] }
\&
\&  # query
\&  ( id = ? OR id = ? OR id > ? )
\&  [ 3, 4, 12 ]
.Ve
.PP
Special Case: If the first element of the arrayref is \-or or \-and, that's
used as the top level logic op:
.PP
.Vb 2
\&  # expr
\&  { id => [ \-and => { \*(Aq>\*(Aq => 3 }, { \*(Aq<\*(Aq => 6 } ] }
\&
\&  # aqt
\&  { \-op => [
\&      \*(Aqand\*(Aq,
\&      { \-op => [ \*(Aq>\*(Aq, { \-ident => [ \*(Aqid\*(Aq ] }, { \-bind => [ \*(Aqid\*(Aq, 3 ] } ] },
\&      { \-op => [ \*(Aq<\*(Aq, { \-ident => [ \*(Aqid\*(Aq ] }, { \-bind => [ \*(Aqid\*(Aq, 6 ] } ] },
\&  ] }
\&
\&  # query
\&  ( id > ? AND id < ? )
\&  [ 3, 6 ]
.Ve
.PP
\fIidentifier hashpair w/hashref value\fR
.IX Subsection "identifier hashpair w/hashref value"
.PP
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:
.PP
.Vb 2
\&  # expr
\&  { id => { \*(Aq<\*(Aq => 4, \*(Aq>\*(Aq => 3 } }
\&
\&  # aqt
\&  { \-op => [
\&      \*(Aqand\*(Aq,
\&      { \-op => [ \*(Aq<\*(Aq, { \-ident => [ \*(Aqid\*(Aq ] }, { \-bind => [ \*(Aqid\*(Aq, 4 ] } ] },
\&      { \-op => [ \*(Aq>\*(Aq, { \-ident => [ \*(Aqid\*(Aq ] }, { \-bind => [ \*(Aqid\*(Aq, 3 ] } ] },
\&  ] }
\&
\&  # query
\&  ( id < ? AND id > ? )
\&  [ 4, 3 ]
.Ve
.PP
is sugar for:
.PP
.Vb 2
\&  # expr
\&  { \-and => [ { id => { \*(Aq<\*(Aq => 4 } }, { id => { \*(Aq>\*(Aq => 3 } } ] }
\&
\&  # aqt
\&  { \-op => [
\&      \*(Aqand\*(Aq,
\&      { \-op => [ \*(Aq<\*(Aq, { \-ident => [ \*(Aqid\*(Aq ] }, { \-bind => [ \*(Aqid\*(Aq, 4 ] } ] },
\&      { \-op => [ \*(Aq>\*(Aq, { \-ident => [ \*(Aqid\*(Aq ] }, { \-bind => [ \*(Aqid\*(Aq, 3 ] } ] },
\&  ] }
\&
\&  # query
\&  ( id < ? AND id > ? )
\&  [ 4, 3 ]
.Ve
.SS "operator hashpair types"
.IX Subsection "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.
.PP
\fIoperator hashpair w/node type\fR
.IX Subsection "operator hashpair w/node type"
.PP
If a node type expander is registered for the key, the hashpair is
treated as a \*(L"node expr\*(R".
.PP
\fIoperator hashpair w/registered op\fR
.IX Subsection "operator hashpair w/registered op"
.PP
If an expander is registered for the op name, that's run and the
result returned:
.PP
.Vb 2
\&  # expr
\&  { \-in => [ \*(Aqfoo\*(Aq, 1, 2, 3 ] }
\&
\&  # aqt
\&  { \-op => [
\&      \*(Aqin\*(Aq, { \-ident => [ \*(Aqfoo\*(Aq ] }, { \-bind => [ undef, 1 ] },
\&      { \-bind => [ undef, 2 ] }, { \-bind => [ undef, 3 ] },
\&  ] }
\&
\&  # query
\&  foo IN ( ?, ?, ? )
\&  [ 1, 2, 3 ]
.Ve
.PP
\fIoperator hashpair w/not prefix\fR
.IX Subsection "operator hashpair w/not prefix"
.PP
If the op name starts \-not_ this is stripped and turned into a \-not
wrapper around the result:
.PP
.Vb 2
\&  # expr
\&  { \-not_ident => \*(Aqfoo\*(Aq }
\&
\&  # aqt
\&  { \-op => [ \*(Aqnot\*(Aq, { \-ident => [ \*(Aqfoo\*(Aq ] } ] }
\&
\&  # query
\&  (NOT foo)
\&  []
.Ve
.PP
is equivalent to:
.PP
.Vb 2
\&  # expr
\&  { \-not => { \-ident => \*(Aqfoo\*(Aq } }
\&
\&  # aqt
\&  { \-op => [ \*(Aqnot\*(Aq, { \-ident => [ \*(Aqfoo\*(Aq ] } ] }
\&
\&  # query
\&  (NOT foo)
\&  []
.Ve
.PP
\fIoperator hashpair with unknown op\fR
.IX Subsection "operator hashpair with unknown op"
.PP
If the \f(CW\*(C`unknown_unop_always_func\*(C'\fR option is set (which is recommended but
defaults to off for backwards compatibility reasons), an unknown op
expands into a \f(CW\*(C`\-func\*(C'\fR node:
.PP
.Vb 2
\&  # expr
\&  { \-count => { \-ident => \*(Aq*\*(Aq } }
\&
\&  # aqt
\&  { \-func => [ \*(Aqcount\*(Aq, { \-ident => [ \*(Aq*\*(Aq ] } ] }
\&
\&  # query
\&  COUNT(*)
\&  []
.Ve
.PP
If not, an unknown op will expand into a \f(CW\*(C`\-op\*(C'\fR node.
.SS "hashref expr"
.IX Subsection "hashref expr"
A hashref with more than one pair becomes a \f(CW\*(C`\-and\*(C'\fR over its hashpairs, i.e.
.PP
.Vb 2
\&  # expr
\&  { x => 1, y => 2 }
\&
\&  # aqt
\&  { \-op => [
\&      \*(Aqand\*(Aq,
\&      { \-op => [ \*(Aq=\*(Aq, { \-ident => [ \*(Aqx\*(Aq ] }, { \-bind => [ \*(Aqx\*(Aq, 1 ] } ] },
\&      { \-op => [ \*(Aq=\*(Aq, { \-ident => [ \*(Aqy\*(Aq ] }, { \-bind => [ \*(Aqy\*(Aq, 2 ] } ] },
\&  ] }
\&
\&  # query
\&  ( x = ? AND y = ? )
\&  [ 1, 2 ]
.Ve
.PP
is short hand for:
.PP
.Vb 2
\&  # expr
\&  { \-and => [ { x => 1 }, { y => 2 } ] }
\&
\&  # aqt
\&  { \-op => [
\&      \*(Aqand\*(Aq,
\&      { \-op => [ \*(Aq=\*(Aq, { \-ident => [ \*(Aqx\*(Aq ] }, { \-bind => [ \*(Aqx\*(Aq, 1 ] } ] },
\&      { \-op => [ \*(Aq=\*(Aq, { \-ident => [ \*(Aqy\*(Aq ] }, { \-bind => [ \*(Aqy\*(Aq, 2 ] } ] },
\&  ] }
\&
\&  # query
\&  ( x = ? AND y = ? )
\&  [ 1, 2 ]
.Ve
.SS "arrayref expr"
.IX Subsection "arrayref expr"
An arrayref becomes a \f(CW\*(C`\-or\*(C'\fR over its contents. Arrayrefs, hashrefs and
literals are all expanded and added to the clauses of the \f(CW\*(C`\-or\*(C'\fR. If the
arrayref contains a scalar it's treated as the key of a hashpair and the
next element as the value.
.PP
.Vb 2
\&  # expr
\&  [ { x => 1 }, [ { y => 2 }, { z => 3 } ], \*(Aqkey\*(Aq, \*(Aqvalue\*(Aq, \e"lit()" ]
\&
\&  # aqt
\&  { \-op => [
\&      \*(Aqor\*(Aq,
\&      { \-op => [ \*(Aq=\*(Aq, { \-ident => [ \*(Aqx\*(Aq ] }, { \-bind => [ \*(Aqx\*(Aq, 1 ] } ] },
\&      { \-op => [
\&          \*(Aqor\*(Aq, {
\&            \-op => [ \*(Aq=\*(Aq, { \-ident => [ \*(Aqy\*(Aq ] }, { \-bind => [ \*(Aqy\*(Aq, 2 ] } ]
\&          }, {
\&            \-op => [ \*(Aq=\*(Aq, { \-ident => [ \*(Aqz\*(Aq ] }, { \-bind => [ \*(Aqz\*(Aq, 3 ] } ]
\&          },
\&      ] }, { \-op =>
\&          [
\&            \*(Aq=\*(Aq, { \-ident => [ \*(Aqkey\*(Aq ] },
\&            { \-bind => [ \*(Aqkey\*(Aq, \*(Aqvalue\*(Aq ] },
\&          ]
\&      },
\&      { \-literal => [ \*(Aqlit()\*(Aq ] },
\&  ] }
\&
\&  # query
\&  ( x = ? OR ( y = ? OR z = ? ) OR key = ? OR lit() )
\&  [ 1, 2, 3, \*(Aqvalue\*(Aq ]
.Ve
.SH "Default Expanders"
.IX Header "Default Expanders"
.SS "bool"
.IX Subsection "bool"
Turns the old \-bool syntax into the value expression, i.e.
.PP
.Vb 2
\&  # expr
\&  { \-bool => { \-ident => \*(Aqfoo\*(Aq } }
\&
\&  # aqt
\&  { \-ident => [ \*(Aqfoo\*(Aq ] }
\&
\&  # query
\&  foo
\&  []
.Ve
.PP
behaves the same way as the now-directly-supported
.PP
.Vb 2
\&  # expr
\&  { \-ident => \*(Aqfoo\*(Aq }
\&
\&  # aqt
\&  { \-ident => [ \*(Aqfoo\*(Aq ] }
\&
\&  # query
\&  foo
\&  []
.Ve
.SS "row"
.IX Subsection "row"
Expands the elements of the value arrayref:
.PP
.Vb 2
\&  # expr
\&  { \-row => [ 1, { \-ident => \*(Aqfoo\*(Aq }, 2, 3 ] }
\&
\&  # aqt
\&  { \-row => [
\&      { \-bind => [ undef, 1 ] }, { \-ident => [ \*(Aqfoo\*(Aq ] },
\&      { \-bind => [ undef, 2 ] }, { \-bind => [ undef, 3 ] },
\&  ] }
\&
\&  # query
\&  (?, foo, ?, ?)
\&  [ 1, 2, 3 ]
.Ve
.SS "op"
.IX Subsection "op"
If an expander is registered for the op name, delegates to the expander; if
not, expands the argument values:
.PP
.Vb 2
\&  # expr
\&  { \-op => [ \*(Aqident\*(Aq, \*(Aqfoo.bar\*(Aq ] }
\&
\&  # aqt
\&  { \-ident => [ \*(Aqfoo\*(Aq, \*(Aqbar\*(Aq ] }
\&
\&  # query
\&  foo.bar
\&  []
\&
\&  # expr
\&  { \-op => [ \*(Aq=\*(Aq, { \-ident => \*(Aqfoo\*(Aq }, 3 ] }
\&
\&  # aqt
\&  { \-op => [ \*(Aq=\*(Aq, { \-ident => [ \*(Aqfoo\*(Aq ] }, { \-bind => [ undef, 3 ] } ] }
\&
\&  # query
\&  foo = ?
\&  [ 3 ]
.Ve
.SS "func"
.IX Subsection "func"
Expands the argument values:
.PP
.Vb 2
\&  # expr
\&  { \-func => [ \*(Aqcoalesce\*(Aq, { \-ident => \*(Aqthing\*(Aq }, \*(Aqfallback\*(Aq ] }
\&
\&  # aqt
\&  { \-func => [
\&      \*(Aqcoalesce\*(Aq, { \-ident => [ \*(Aqthing\*(Aq ] },
\&      { \-bind => [ undef, \*(Aqfallback\*(Aq ] },
\&  ] }
\&
\&  # query
\&  COALESCE(thing, ?)
\&  [ \*(Aqfallback\*(Aq ]
.Ve
.SS "values"
.IX Subsection "values"
A hashref value is expanded as an expression:
.PP
.Vb 2
\&  # expr
\&  { \-values => { \-row => [ 1, 2 ] } }
\&
\&  # aqt
\&  { \-values => [
\&      { \-row => [ { \-bind => [ undef, 1 ] }, { \-bind => [ undef, 2 ] } ] }
\&  ] }
\&
\&  # query
\&  VALUES (?, ?)
\&  [ 1, 2 ]
.Ve
.PP
An arrayref value's elements are either expressions or arrayrefs to be
treated as rows:
.PP
.Vb 2
\&  # 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 ]
.Ve
.SS "list"
.IX Subsection "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:
.PP
.Vb 2
\&  # expr
\&  { \-list => [ { \-ident => \*(Aqfoo\*(Aq } ] }
\&
\&  # aqt
\&  { \-op => [ \*(Aq,\*(Aq, { \-ident => [ \*(Aqfoo\*(Aq ] } ] }
\&
\&  # query
\&  foo
\&  []
\&
\&  # expr
\&  { \-list => [ { \-ident => \*(Aqfoo\*(Aq }, { \-ident => \*(Aqbar\*(Aq } ] }
\&
\&  # aqt
\&  { \-op => [ \*(Aq,\*(Aq, { \-ident => [ \*(Aqfoo\*(Aq ] }, { \-ident => [ \*(Aqbar\*(Aq ] } ] }
\&
\&  # query
\&  foo, bar
\&  []
.Ve
.SS "between op"
.IX Subsection "between op"
The \s-1RHS\s0 of between must either be a pair of exprs/plain values, or a single
literal expr:
.PP
.Vb 2
\&  # expr
\&  { \-between => [ \*(Aqsize\*(Aq, 3, { \-ident => \*(Aqmax_size\*(Aq } ] }
\&
\&  # aqt
\&  { \-op => [
\&      \*(Aqbetween\*(Aq, { \-ident => [ \*(Aqsize\*(Aq ] }, { \-bind => [ undef, 3 ] },
\&      { \-ident => [ \*(Aqmax_size\*(Aq ] },
\&  ] }
\&
\&  # query
\&  ( size BETWEEN ? AND max_size )
\&  [ 3 ]
\&
\&  # expr
\&  { size => { \-between => [ 3, { \-ident => \*(Aqmax_size\*(Aq } ] } }
\&
\&  # aqt
\&  { \-op => [
\&      \*(Aqbetween\*(Aq, { \-ident => [ \*(Aqsize\*(Aq ] }, { \-bind => [ \*(Aqsize\*(Aq, 3 ] },
\&      { \-ident => [ \*(Aqmax_size\*(Aq ] },
\&  ] }
\&
\&  # query
\&  ( size BETWEEN ? AND max_size )
\&  [ 3 ]
\&
\&  # expr
\&  { size => { \-between => \e"3 AND 7" } }
\&
\&  # aqt
\&  { \-op =>
\&      [
\&        \*(Aqbetween\*(Aq, { \-ident => [ \*(Aqsize\*(Aq ] },
\&        { \-literal => [ \*(Aq3 AND 7\*(Aq ] },
\&      ]
\&  }
\&
\&  # query
\&  ( size BETWEEN 3 AND 7 )
\&  []
.Ve
.PP
not_between is also expanded:
.PP
.Vb 2
\&  # expr
\&  { size => { \-not_between => [ 3, 7 ] } }
\&
\&  # aqt
\&  { \-op => [
\&      \*(Aqnot_between\*(Aq, { \-ident => [ \*(Aqsize\*(Aq ] },
\&      { \-bind => [ \*(Aqsize\*(Aq, 3 ] }, { \-bind => [ \*(Aqsize\*(Aq, 7 ] },
\&  ] }
\&
\&  # query
\&  ( size NOT BETWEEN ? AND ? )
\&  [ 3, 7 ]
.Ve
.SS "in op"
.IX Subsection "in op"
The \s-1RHS\s0 of in/not_in is either an expr/value or an arrayref of
exprs/values:
.PP
.Vb 2
\&  # expr
\&  { foo => { \-in => [ 1, 2 ] } }
\&
\&  # aqt
\&  { \-op => [
\&      \*(Aqin\*(Aq, { \-ident => [ \*(Aqfoo\*(Aq ] }, { \-bind => [ \*(Aqfoo\*(Aq, 1 ] },
\&      { \-bind => [ \*(Aqfoo\*(Aq, 2 ] },
\&  ] }
\&
\&  # query
\&  foo IN ( ?, ? )
\&  [ 1, 2 ]
\&
\&  # expr
\&  { bar => { \-not_in => \e"(1, 2)" } }
\&
\&  # aqt
\&  { \-op =>
\&      [ \*(Aqnot_in\*(Aq, { \-ident => [ \*(Aqbar\*(Aq ] }, { \-literal => [ \*(Aq1, 2\*(Aq ] } ]
\&  }
\&
\&  # query
\&  bar NOT IN ( 1, 2 )
\&  []
.Ve
.PP
A non-trivial \s-1LHS\s0 is expanded with ident as the default rather than value:
.PP
.Vb 5
\&  # expr
\&  { \-in => [
\&      { \-row => [ \*(Aqx\*(Aq, \*(Aqy\*(Aq ] }, { \-row => [ 1, 2 ] },
\&      { \-row => [ 3, 4 ] },
\&  ] }
\&
\&  # aqt
\&  { \-op => [
\&      \*(Aqin\*(Aq, { \-row => [ { \-ident => [ \*(Aqx\*(Aq ] }, { \-ident => [ \*(Aqy\*(Aq ] } ] },
\&      { \-row => [ { \-bind => [ undef, 1 ] }, { \-bind => [ undef, 2 ] } ] },
\&      { \-row => [ { \-bind => [ undef, 3 ] }, { \-bind => [ undef, 4 ] } ] },
\&  ] }
\&
\&  # query
\&  (x, y) IN ( (?, ?), (?, ?) )
\&  [ 1, 2, 3, 4 ]
.Ve
.SS "and/or ops"
.IX Subsection "and/or ops"
expands the same way as a plain arrayref/hashref expression but with the
logic type set to the op name.
.SS "is op"
.IX Subsection "is op"
Expands is and is_not to null checks, \s-1RHS\s0 value must be undef:
.PP
.Vb 2
\&  # expr
\&  { \-is => [ \*(Aqfoo\*(Aq, undef ] }
\&
\&  # aqt
\&  { \-op => [ \*(Aqis_null\*(Aq, { \-ident => [ \*(Aqfoo\*(Aq ] } ] }
\&
\&  # query
\&  foo IS NULL
\&  []
\&
\&  # expr
\&  { bar => { \-is_not => undef } }
\&
\&  # aqt
\&  { \-op => [ \*(Aqis_not_null\*(Aq, { \-ident => [ \*(Aqbar\*(Aq ] } ] }
\&
\&  # query
\&  bar IS NOT NULL
\&  []
.Ve
.SS "ident op"
.IX Subsection "ident op"
Expands a string ident to an arrayref by splitting on the configured
separator, almost always '.':
.PP
.Vb 2
\&  # expr
\&  { \-ident => \*(Aqfoo.bar\*(Aq }
\&
\&  # aqt
\&  { \-ident => [ \*(Aqfoo\*(Aq, \*(Aqbar\*(Aq ] }
\&
\&  # query
\&  foo.bar
\&  []
.Ve
.SS "value op"
.IX Subsection "value op"
Expands to a bind node with the currently applicable column name if known:
.PP
.Vb 2
\&  # expr
\&  { foo => { \*(Aq=\*(Aq => { \-value => 3 } } }
\&
\&  # aqt
\&  { \-op => [ \*(Aq=\*(Aq, { \-ident => [ \*(Aqfoo\*(Aq ] }, { \-bind => [ \*(Aqfoo\*(Aq, 3 ] } ] }
\&
\&  # query
\&  foo = ?
\&  [ 3 ]
.Ve
.SH "Query Types"
.IX Header "Query Types"
.SS "select"
.IX Subsection "select"
A select node accepts select, from, where and order_by clauses.
.PP
The select clause is expanded as a list expression with a \-ident default:
.PP
.Vb 2
\&  # expr
\&  { \-select => { _ => [ \*(Aqfoo\*(Aq, \*(Aqbar\*(Aq, { \-count => \*(Aqbaz\*(Aq } ] } }
\&
\&  # aqt
\&  { \-select => { select => { \-op => [
\&          \*(Aq,\*(Aq, { \-ident => [ \*(Aqfoo\*(Aq ] }, { \-ident => [ \*(Aqbar\*(Aq ] },
\&          { \-func => [ \*(Aqcount\*(Aq, { \-ident => [ \*(Aqbaz\*(Aq ] } ] },
\&  ] } } }
\&
\&  # query
\&  SELECT foo, bar, COUNT(baz)
\&  []
.Ve
.PP
The from clause is expanded as a list expression with a \-ident default:
.PP
.Vb 4
\&  # expr
\&  { \-select => {
\&      from => [ \*(Aqschema1.table1\*(Aq, { \-ident => [ \*(Aqschema2\*(Aq, \*(Aqtable2\*(Aq ] } ]
\&  } }
\&
\&  # aqt
\&  { \-select => { from => { \-from_list => [
\&          { \-ident => [ \*(Aqschema1\*(Aq, \*(Aqtable1\*(Aq ] },
\&          { \-ident => [ \*(Aqschema2\*(Aq, \*(Aqtable2\*(Aq ] },
\&  ] } } }
\&
\&  # query
\&  FROM schema1.table1, schema2.table2
\&  []
.Ve
.PP
The where clause is expanded as a plain expression:
.PP
.Vb 2
\&  # expr
\&  { \-select => { where => { foo => 3 } } }
\&
\&  # aqt
\&  { \-select => { where => {
\&        \-op => [ \*(Aq=\*(Aq, { \-ident => [ \*(Aqfoo\*(Aq ] }, { \-bind => [ \*(Aqfoo\*(Aq, 3 ] } ]
\&  } } }
\&
\&  # query
\&  WHERE foo = ?
\&  [ 3 ]
.Ve
.PP
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:
.PP
.Vb 4
\&  # expr
\&  { \-select =>
\&      { order_by => [ \*(Aqfoo\*(Aq, { \-desc => \*(Aqbar\*(Aq }, { \-max => \*(Aqbaz\*(Aq } ] }
\&  }
\&
\&  # aqt
\&  { \-select => { order_by => { \-op => [
\&          \*(Aq,\*(Aq, { \-ident => [ \*(Aqfoo\*(Aq ] }, {
\&            \-op => [ \*(Aq,\*(Aq, { \-op => [ \*(Aqdesc\*(Aq, { \-ident => [ \*(Aqbar\*(Aq ] } ] } ]
\&          }, { \-func => [ \*(Aqmax\*(Aq, { \-ident => [ \*(Aqbaz\*(Aq ] } ] },
\&  ] } } }
\&
\&  # query
\&  ORDER BY foo, bar DESC, MAX(baz)
\&  []
.Ve
.SS ""
.IX Subsection ""
An insert node accepts an into/target clause, a fields clause, a values/from
clause, and a returning clause.
.PP
The target clause is expanded with an ident default.
.PP
The fields clause is expanded as a list expression if an arrayref, and
otherwise passed through.
.PP
The from clause may either be an expr, a literal, an arrayref of column
values, or a hashref mapping colum names to values.
.PP
The returning clause is expanded as a list expr with an ident default.
.PP
.Vb 6
\&  # expr
\&  { \-insert => {
\&      into => \*(Aqfoo\*(Aq,
\&      returning => \*(Aqid\*(Aq,
\&      values => { bar => \*(Aqyay\*(Aq, baz => \*(Aqargh\*(Aq },
\&  } }
\&
\&  # aqt
\&  { \-insert => {
\&      fields =>
\&        { \-row => [ { \-ident => [ \*(Aqbar\*(Aq ] }, { \-ident => [ \*(Aqbaz\*(Aq ] } ] },
\&      from => { \-values => [ { \-row => [
\&              { \-bind => [ \*(Aqbar\*(Aq, \*(Aqyay\*(Aq ] },
\&              { \-bind => [ \*(Aqbaz\*(Aq, \*(Aqargh\*(Aq ] },
\&      ] } ] },
\&      returning => { \-op => [ \*(Aq,\*(Aq, { \-ident => [ \*(Aqid\*(Aq ] } ] },
\&      target => { \-ident => [ \*(Aqfoo\*(Aq ] },
\&  } }
\&
\&  # query
\&  INSERT INTO foo (bar, baz) VALUES (?, ?) RETURNING id
\&  [ \*(Aqyay\*(Aq, \*(Aqargh\*(Aq ]
\&
\&  # expr
\&  { \-insert => {
\&      fields => [ \*(Aqbar\*(Aq, \*(Aqbaz\*(Aq ],
\&      from => { \-select => { _ => [ \*(Aqbar\*(Aq, \*(Aqbaz\*(Aq ], from => \*(Aqother\*(Aq } },
\&      into => \*(Aqfoo\*(Aq,
\&  } }
\&
\&  # aqt
\&  { \-insert => {
\&      fields => { \-row => [ { \-op =>
\&              [ \*(Aq,\*(Aq, { \-ident => [ \*(Aqbar\*(Aq ] }, { \-ident => [ \*(Aqbaz\*(Aq ] } ]
\&      } ] },
\&      from => { \-select => {
\&          from => { \-ident => [ \*(Aqother\*(Aq ] },
\&          select => { \-op =>
\&              [ \*(Aq,\*(Aq, { \-ident => [ \*(Aqbar\*(Aq ] }, { \-ident => [ \*(Aqbaz\*(Aq ] } ]
\&          },
\&      } },
\&      target => { \-ident => [ \*(Aqfoo\*(Aq ] },
\&  } }
\&
\&  # query
\&  INSERT INTO foo (bar, baz) SELECT bar, baz FROM other
\&  []
.Ve
.SS "update"
.IX Subsection "update"
An update node accepts update/target (either may be used at expansion time),
set, where, and returning clauses.
.PP
The target clause is expanded with an ident default.
.PP
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.
.PP
The where clauses is expanded as a normal expr.
.PP
The returning clause is expanded as a list expr with an ident default.
.PP
.Vb 7
\&  # expr
\&  { \-update => {
\&      _ => \*(Aqfoo\*(Aq,
\&      returning => [ \*(Aqid\*(Aq, \*(Aqbaz\*(Aq ],
\&      set => { bar => 3, baz => { baz => { \*(Aq+\*(Aq => 1 } } },
\&      where => { \-not => { \-ident => \*(Aqquux\*(Aq } },
\&  } }
\&
\&  # aqt
\&  { \-update => {
\&      returning =>
\&        {
\&          \-op => [ \*(Aq,\*(Aq, { \-ident => [ \*(Aqid\*(Aq ] }, { \-ident => [ \*(Aqbaz\*(Aq ] } ]
\&        },
\&      set => { \-op => [
\&          \*(Aq,\*(Aq, { \-op =>
\&              [ \*(Aq=\*(Aq, { \-ident => [ \*(Aqbar\*(Aq ] }, { \-bind => [ \*(Aqbar\*(Aq, 3 ] } ]
\&          }, { \-op => [
\&              \*(Aq=\*(Aq, { \-ident => [ \*(Aqbaz\*(Aq ] }, { \-op => [
\&                  \*(Aq+\*(Aq, { \-ident => [ \*(Aqbaz\*(Aq ] },
\&                  { \-bind => [ \*(Aqbaz\*(Aq, 1 ] },
\&              ] },
\&          ] },
\&      ] },
\&      target => { \-ident => [ \*(Aqfoo\*(Aq ] },
\&      where => { \-op => [ \*(Aqnot\*(Aq, { \-ident => [ \*(Aqquux\*(Aq ] } ] },
\&  } }
\&
\&  # query
\&  UPDATE foo SET bar = ?, baz = baz + ? WHERE (NOT quux) RETURNING id, baz
\&  [ 3, 1 ]
.Ve
.SS "delete"
.IX Subsection "delete"
delete accepts from/target, where, and returning clauses.
.PP
The target clause is expanded with an ident default.
.PP
The where clauses is expanded as a normal expr.
.PP
The returning clause is expanded as a list expr with an ident default.
.PP
.Vb 6
\&  # expr
\&  { \-delete => {
\&      from => \*(Aqfoo\*(Aq,
\&      returning => \*(Aqid\*(Aq,
\&      where => { bar => { \*(Aq<\*(Aq => 10 } },
\&  } }
\&
\&  # aqt
\&  { \-delete => {
\&      returning => { \-op => [ \*(Aq,\*(Aq, { \-ident => [ \*(Aqid\*(Aq ] } ] },
\&      target => { \-op => [ \*(Aq,\*(Aq, { \-ident => [ \*(Aqfoo\*(Aq ] } ] },
\&      where => { \-op =>
\&          [ \*(Aq<\*(Aq, { \-ident => [ \*(Aqbar\*(Aq ] }, { \-bind => [ \*(Aqbar\*(Aq, 10 ] } ]
\&      },
\&  } }
\&
\&  # query
\&  DELETE FROM foo WHERE bar < ? RETURNING id
\&  [ 10 ]
.Ve