CONTENTS

• NAME
• TERMS
  • Expression (expr)
  • Abstract Query Tree (aqt)
  • SQL and Bind Values (query)
  • Expander
  • Renderer
• AQT node types
  • literal
  • ident
  • bind
  • row
  • func
  • op
  • values
  • keyword
  • statement types
• Expressions
  • node expr
  • identifier hashpair types
    • hashtriple
    • identifier hashpair w/simple value
    • identifier hashpair w/undef RHS
    • identifier hashpair w/literal RHS
    • identifier hashpair w/arrayref value
    • identifier hashpair w/hashref value
  • operator hashpair types
    • operator hashpair w/node type
    • operator hashpair w/registered op
    • operator hashpair w/not prefix
    • operator hashpair with unknown op
  • hashref expr
  • arrayref expr
• Default Expanders
  • bool
  • row
  • op
  • func
  • values
  • list
  • between op
  • in op
  • and/or ops
  • is op
  • ident op
  • value op
• Query Types
  • select
  • update
  • delete

#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 ]

CPANdoc Browser is maintained by Dan Book (DBOOK). Please contact him via the GitHub issue tracker or email regarding any issues with the site itself, search, or rendering of documentation.

CPAN module documentation is maintained by each author and maintainer individually. Please contact them via the mechanisms listed in their documentation or on the module's MetaCPAN page to report any issues with the contents or format of the documentation.