1SQL::Abstract::ReferencUes(e3r)Contributed Perl DocumentSaQtLi:o:nAbstract::Reference(3)
2
6 SQL::Abstract::Reference - Reference documentation for SQL::Abstract
7
9 Expression (expr)
10 The DWIM structure that's passed to most methods by default is referred
11 to as expression syntax. If you see a variable with "expr" in the name,
12 or a comment before a code block saying "# expr", this is what's being
13 described.
14 Abstract Query Tree (aqt)
16 The explicit structure that an expression is converted into before it's
17 rendered into SQL is referred to as an abstract query tree. If you see
18 a variable with "aqt" in the name, or a comment before a code block
19 saying "# aqt", this is what's being described.
20 SQL and Bind Values (query)
22 The final result of SQL::Abstract rendering is generally an SQL
23 statement plus bind values for passing to DBI, ala:
24 my ($sql, @bind) = $sqla->some_method(@args);
26 my @hashes = @{$dbh->do($sql, { Slice => {} }, @bind)};
27 If you see a comment before a code block saying "# query", the SQL +
29 bind array is what's being described.
30 Expander
32 An expander subroutine is written as:
33 sub {
35 my ($sqla, $name, $value, $k) = @_;
36 ...
37 return $aqt;
38 }
39 $name is the expr node type for node expanders, the op name for op
41 expanders, and the clause name for clause expanders.
42 $value is the body of the thing being expanded
44 If an op expander is being called as the binary operator in a
46 "hashtriple" expression, $k will be the hash key to be used as the left
47 hand side identifier.
48 This can trivially be converted to an "ident" type AQT node with:
50 my $ident = $sqla->expand_expr({ -ident => $k });
52 Renderer
54 A renderer subroutine looks like:
55 sub {
57 my ($sqla, $type, $value) = @_;
58 ...
59 $sqla->join_query_parts($join, @parts);
60 }
61 and can be registered on a per-type, per-op or per-clause basis.
63
65 An AQT node consists of a hashref with a single key, whose name is
66 "-type" where 'type' is the node type, and whose value is the data for
67 the node.
68 The following is an explanation of the built-in AQT type renderers;
70 additional renderers can be registered as part of the extension system.
71 literal
73 # expr
74 { -literal => [ 'SPANG(?, ?)', 1, 27 ] }
75 # query
77 SPANG(?, ?)
78 [ 1, 27 ]
79 ident
81 # expr
82 { -ident => 'foo' }
83 # query
85 foo
86 []
87 # expr
89 { -ident => [ 'foo', 'bar' ] }
90 # query
92 foo.bar
93 []
94 bind
96 # expr
97 { -bind => [ 'colname', 'value' ] }
98 # query
100 ?
101 [ 'value' ]
102 row
104 # expr
105 {
106 -row => [ { -bind => [ 'r', 1 ] }, { -ident => [ 'clown', 'car' ] } ]
107 }
108 # query
110 (?, clown.car)
111 [ 1 ]
112 func
114 # expr
115 {
116 -func => [ 'foo', { -ident => [ 'bar' ] }, { -bind => [ undef, 7 ] } ]
117 }
118 # query
120 FOO(bar, ?)
121 [ 7 ]
122 op
124 Standard binop:
125 # expr
127 { -op => [
128 '=', { -ident => [ 'bomb', 'status' ] },
129 { -value => 'unexploded' },
130 ] }
131 # query
133 bomb.status = ?
134 [ 'unexploded' ]
135 Prefix unop:
137 # expr
139 { -op => [ '-', { -ident => 'foo' } ] }
140 # query
142 - foo
143 []
144 Not as special case parenthesised unop:
146 # expr
148 { -op => [ 'not', { -ident => 'explosive' } ] }
149 # query
151 (NOT explosive)
152 []
153 Postfix unop: (is_null, is_not_null, asc, desc)
155 # expr
157 { -op => [ 'is_null', { -ident => [ 'bobby' ] } ] }
158 # query
160 bobby IS NULL
161 []
162 AND and OR:
164 # expr
166 { -op =>
167 [ 'and', { -ident => 'x' }, { -ident => 'y' }, { -ident => 'z' } ]
168 }
169 # query
171 ( x AND y AND z )
172 []
173 IN (and NOT IN):
175 # expr
177 { -op => [
178 'in', { -ident => 'card' }, { -bind => [ 'card', 3 ] },
179 { -bind => [ 'card', 'J' ] },
180 ] }
181 # query
183 card IN ( ?, ? )
184 [ 3, 'J' ]
185 BETWEEN (and NOT BETWEEN):
187 # expr
189 { -op => [
190 'between', { -ident => 'pints' }, { -bind => [ 'pints', 2 ] },
191 { -bind => [ 'pints', 4 ] },
192 ] }
193 # query
195 ( pints BETWEEN ? AND ? )
196 [ 2, 4 ]
197 Comma (use -row for parens):
199 # expr
201 { -op => [ ',', { -literal => [ 1 ] }, { -literal => [ 2 ] } ] }
202 # query
204 1, 2
205 []
206 values
208 # expr
209 { -values =>
210 { -row => [ { -bind => [ undef, 1 ] }, { -bind => [ undef, 2 ] } ] }
211 }
212 # query
214 VALUES (?, ?)
215 [ 1, 2 ]
216 # expr
218 { -values => [
219 { -row => [ { -literal => [ 1 ] }, { -literal => [ 2 ] } ] },
220 { -row => [ { -literal => [ 3 ] }, { -literal => [ 4 ] } ] },
221 ] }
222 # query
224 VALUES (1, 2), (3, 4)
225 []
226 keyword
228 # expr
229 { -keyword => 'insert_into' }
230 # query
232 INSERT INTO
233 []
234 statement types
236 AQT node types are also provided for "select", "insert", "update" and
237 "delete". These types are handled by the clauses system as discussed
238 later.
239
241 node expr
242 The simplest expression is just an AQT node:
243 # expr
245 { -ident => [ 'foo', 'bar' ] }
246 # aqt
248 { -ident => [ 'foo', 'bar' ] }
249 # query
251 foo.bar
252 []
253 However, even in the case of an AQT node, the node value will be
255 expanded if an expander has been registered for that node type:
256 # expr
258 { -ident => 'foo.bar' }
259 # aqt
261 { -ident => [ 'foo', 'bar' ] }
262 # query
264 foo.bar
265 []
266 identifier hashpair types
268 hashtriple
269 # expr
271 { id => { op => 'value' } }
272 # aqt
274 { -op =>
275 [ 'op', { -ident => [ 'id' ] }, { -bind => [ 'id', 'value' ] } ]
276 }
277 # query
279 id OP ?
280 [ 'value' ]
281 If the value is undef, attempts to convert equality and like ops to IS
283 NULL, and inequality and not like to IS NOT NULL:
284 # expr
286 { id => { '!=' => undef } }
287 # aqt
289 { -op => [ 'is_not_null', { -ident => [ 'id' ] } ] }
290 # query
292 id IS NOT NULL
293 []
294 identifier hashpair w/simple value
296 Equivalent to a hashtriple with an op of '='.
298 # expr
300 { id => 'value' }
301 # aqt
303 {
304 -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 'value' ] } ]
305 }
306 # query
308 id = ?
309 [ 'value' ]
310 (an object value will also follow this code path)
312 identifier hashpair w/undef RHS
314 Converted to IS NULL :
316 # expr
318 { id => undef }
319 # aqt
321 { -op => [ 'is_null', { -ident => [ 'id' ] } ] }
322 # query
324 id IS NULL
325 []
326 (equivalent to the -is operator) :
328 # expr
330 { id => { -is => undef } }
331 # aqt
333 { -op => [ 'is_null', { -ident => [ 'id' ] } ] }
334 # query
336 id IS NULL
337 []
338 identifier hashpair w/literal RHS
340 Directly appended to the key, remember you need to provide an operator:
342 # expr
344 { id => \"= dont_try_this_at_home" }
345 # aqt
347 { -literal => [ 'id = dont_try_this_at_home' ] }
348 # query
350 id = dont_try_this_at_home
351 []
352 # expr
354 { id => \[
355 "= seriously(?, ?, ?, ?)",
356 "use",
357 "-ident",
358 "and",
359 "-func",
360 ]
361 }
362 # aqt
364 { -literal =>
365 [ 'id = seriously(?, ?, ?, ?)', 'use', -ident => 'and', '-func' ]
366 }
367 # query
369 id = seriously(?, ?, ?, ?)
370 [ 'use', -ident => 'and', '-func' ]
371 (you may absolutely use this when there's no built-in expression type
373 for what you need and registering a custom one would be more hassle
374 than it's worth, but, y'know, do try and avoid it)
375 identifier hashpair w/arrayref value
377 Becomes equivalent to a -or over an arrayref of hashrefs with the
379 identifier as key and the member of the original arrayref as the value:
380 # expr
382 { id => [ 3, 4, { '>' => 12 } ] }
383 # aqt
385 { -op => [
386 'or',
387 { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
388 { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
389 {
390 -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 12 ] } ]
391 },
392 ] }
393 # query
395 ( id = ? OR id = ? OR id > ? )
396 [ 3, 4, 12 ]
397 # expr
399 { -or => [ { id => 3 }, { id => 4 }, { id => { '>' => 12 } } ] }
400 # aqt
402 { -op => [
403 'or',
404 { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
405 { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
406 {
407 -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 12 ] } ]
408 },
409 ] }
410 # query
412 ( id = ? OR id = ? OR id > ? )
413 [ 3, 4, 12 ]
414 Special Case: If the first element of the arrayref is -or or -and,
416 that's used as the top level logic op:
417 # expr
419 { id => [ -and => { '>' => 3 }, { '<' => 6 } ] }
420 # aqt
422 { -op => [
423 'and',
424 { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
425 { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 6 ] } ] },
426 ] }
427 # query
429 ( id > ? AND id < ? )
430 [ 3, 6 ]
431 identifier hashpair w/hashref value
433 Becomes equivalent to a -and over an arrayref of hashtriples
435 constructed with the identifier as the key and each key/value pair of
436 the original hashref as the value:
437 # expr
439 { id => { '<' => 4, '>' => 3 } }
440 # aqt
442 { -op => [
443 'and',
444 { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
445 { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
446 ] }
447 # query
449 ( id < ? AND id > ? )
450 [ 4, 3 ]
451 is sugar for:
453 # expr
455 { -and => [ { id => { '<' => 4 } }, { id => { '>' => 3 } } ] }
456 # aqt
458 { -op => [
459 'and',
460 { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
461 { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
462 ] }
463 # query
465 ( id < ? AND id > ? )
466 [ 4, 3 ]
467 operator hashpair types
469 A hashpair whose key begins with a -, or whose key consists entirely of
470 nonword characters (thereby covering '=', '>', pg json ops, etc.) is
471 processed as an operator hashpair.
472 operator hashpair w/node type
474 If a node type expander is registered for the key, the hashpair is
476 treated as a "node expr".
477 operator hashpair w/registered op
479 If an expander is registered for the op name, that's run and the result
481 returned:
482 # expr
484 { -in => [ 'foo', 1, 2, 3 ] }
485 # aqt
487 { -op => [
488 'in', { -ident => [ 'foo' ] }, { -bind => [ undef, 1 ] },
489 { -bind => [ undef, 2 ] }, { -bind => [ undef, 3 ] },
490 ] }
491 # query
493 foo IN ( ?, ?, ? )
494 [ 1, 2, 3 ]
495 operator hashpair w/not prefix
497 If the op name starts -not_ this is stripped and turned into a -not
499 wrapper around the result:
500 # expr
502 { -not_ident => 'foo' }
503 # aqt
505 { -op => [ 'not', { -ident => [ 'foo' ] } ] }
506 # query
508 (NOT foo)
509 []
510 is equivalent to:
512 # expr
514 { -not => { -ident => 'foo' } }
515 # aqt
517 { -op => [ 'not', { -ident => [ 'foo' ] } ] }
518 # query
520 (NOT foo)
521 []
522 operator hashpair with unknown op
524 If the "unknown_unop_always_func" option is set (which is recommended
526 but defaults to off for backwards compatibility reasons), an unknown op
527 expands into a "-func" node:
528 # expr
530 { -count => { -ident => '*' } }
531 # aqt
533 { -func => [ 'count', { -ident => [ '*' ] } ] }
534 # query
536 COUNT(*)
537 []
538 If not, an unknown op will expand into a "-op" node.
540 hashref expr
542 A hashref with more than one pair becomes a "-and" over its hashpairs,
543 i.e.
544 # expr
546 { x => 1, y => 2 }
547 # aqt
549 { -op => [
550 'and',
551 { -op => [ '=', { -ident => [ 'x' ] }, { -bind => [ 'x', 1 ] } ] },
552 { -op => [ '=', { -ident => [ 'y' ] }, { -bind => [ 'y', 2 ] } ] },
553 ] }
554 # query
556 ( x = ? AND y = ? )
557 [ 1, 2 ]
558 is short hand for:
560 # expr
562 { -and => [ { x => 1 }, { y => 2 } ] }
563 # aqt
565 { -op => [
566 'and',
567 { -op => [ '=', { -ident => [ 'x' ] }, { -bind => [ 'x', 1 ] } ] },
568 { -op => [ '=', { -ident => [ 'y' ] }, { -bind => [ 'y', 2 ] } ] },
569 ] }
570 # query
572 ( x = ? AND y = ? )
573 [ 1, 2 ]
574 arrayref expr
576 An arrayref becomes a "-or" over its contents. Arrayrefs, hashrefs and
577 literals are all expanded and added to the clauses of the "-or". If the
578 arrayref contains a scalar it's treated as the key of a hashpair and
579 the next element as the value.
580 # expr
582 [ { x => 1 }, [ { y => 2 }, { z => 3 } ], 'key', 'value', \"lit()" ]
583 # aqt
585 { -op => [
586 'or',
587 { -op => [ '=', { -ident => [ 'x' ] }, { -bind => [ 'x', 1 ] } ] },
588 { -op => [
589 'or', {
590 -op => [ '=', { -ident => [ 'y' ] }, { -bind => [ 'y', 2 ] } ]
591 }, {
592 -op => [ '=', { -ident => [ 'z' ] }, { -bind => [ 'z', 3 ] } ]
593 },
594 ] }, { -op =>
595 [
596 '=', { -ident => [ 'key' ] },
597 { -bind => [ 'key', 'value' ] },
598 ]
599 },
600 { -literal => [ 'lit()' ] },
601 ] }
602 # query
604 ( x = ? OR ( y = ? OR z = ? ) OR key = ? OR lit() )
605 [ 1, 2, 3, 'value' ]
606
608 bool
609 Turns the old -bool syntax into the value expression, i.e.
610 # expr
612 { -bool => { -ident => 'foo' } }
613 # aqt
615 { -ident => [ 'foo' ] }
616 # query
618 foo
619 []
620 behaves the same way as the now-directly-supported
622 # expr
624 { -ident => 'foo' }
625 # aqt
627 { -ident => [ 'foo' ] }
628 # query
630 foo
631 []
632 row
634 Expands the elements of the value arrayref:
635 # expr
637 { -row => [ 1, { -ident => 'foo' }, 2, 3 ] }
638 # aqt
640 { -row => [
641 { -bind => [ undef, 1 ] }, { -ident => [ 'foo' ] },
642 { -bind => [ undef, 2 ] }, { -bind => [ undef, 3 ] },
643 ] }
644 # query
646 (?, foo, ?, ?)
647 [ 1, 2, 3 ]
648 op
650 If an expander is registered for the op name, delegates to the
651 expander; if not, expands the argument values:
652 # expr
654 { -op => [ 'ident', 'foo.bar' ] }
655 # aqt
657 { -ident => [ 'foo', 'bar' ] }
658 # query
660 foo.bar
661 []
662 # expr
664 { -op => [ '=', { -ident => 'foo' }, 3 ] }
665 # aqt
667 { -op => [ '=', { -ident => [ 'foo' ] }, { -bind => [ undef, 3 ] } ] }
668 # query
670 foo = ?
671 [ 3 ]
672 func
674 Expands the argument values:
675 # expr
677 { -func => [ 'coalesce', { -ident => 'thing' }, 'fallback' ] }
678 # aqt
680 { -func => [
681 'coalesce', { -ident => [ 'thing' ] },
682 { -bind => [ undef, 'fallback' ] },
683 ] }
684 # query
686 COALESCE(thing, ?)
687 [ 'fallback' ]
688 values
690 A hashref value is expanded as an expression:
691 # expr
693 { -values => { -row => [ 1, 2 ] } }
694 # aqt
696 { -values => [
697 { -row => [ { -bind => [ undef, 1 ] }, { -bind => [ undef, 2 ] } ] }
698 ] }
699 # query
701 VALUES (?, ?)
702 [ 1, 2 ]
703 An arrayref value's elements are either expressions or arrayrefs to be
705 treated as rows:
706 # expr
708 { -values => [ { -row => [ 1, 2 ] }, [ 3, 4 ] ] }
709 # aqt
711 { -values => [
712 { -row => [ { -bind => [ undef, 1 ] }, { -bind => [ undef, 2 ] } ] },
713 { -row => [ { -bind => [ undef, 3 ] }, { -bind => [ undef, 4 ] } ] },
714 ] }
715 # query
717 VALUES (?, ?), (?, ?)
718 [ 1, 2, 3, 4 ]
719 list
721 Expects a value or an arrayref of values, expands them, and returns
722 just the expanded aqt for a single entry or a comma operator for
723 multiple:
724 # expr
726 { -list => [ { -ident => 'foo' } ] }
727 # aqt
729 { -op => [ ',', { -ident => [ 'foo' ] } ] }
730 # query
732 foo
733 []
734 # expr
736 { -list => [ { -ident => 'foo' }, { -ident => 'bar' } ] }
737 # aqt
739 { -op => [ ',', { -ident => [ 'foo' ] }, { -ident => [ 'bar' ] } ] }
740 # query
742 foo, bar
743 []
744 between op
746 The RHS of between must either be a pair of exprs/plain values, or a
747 single literal expr:
748 # expr
750 { -between => [ 'size', 3, { -ident => 'max_size' } ] }
751 # aqt
753 { -op => [
754 'between', { -ident => [ 'size' ] }, { -bind => [ undef, 3 ] },
755 { -ident => [ 'max_size' ] },
756 ] }
757 # query
759 ( size BETWEEN ? AND max_size )
760 [ 3 ]
761 # expr
763 { size => { -between => [ 3, { -ident => 'max_size' } ] } }
764 # aqt
766 { -op => [
767 'between', { -ident => [ 'size' ] }, { -bind => [ 'size', 3 ] },
768 { -ident => [ 'max_size' ] },
769 ] }
770 # query
772 ( size BETWEEN ? AND max_size )
773 [ 3 ]
774 # expr
776 { size => { -between => \"3 AND 7" } }
777 # aqt
779 { -op =>
780 [
781 'between', { -ident => [ 'size' ] },
782 { -literal => [ '3 AND 7' ] },
783 ]
784 }
785 # query
787 ( size BETWEEN 3 AND 7 )
788 []
789 not_between is also expanded:
791 # expr
793 { size => { -not_between => [ 3, 7 ] } }
794 # aqt
796 { -op => [
797 'not_between', { -ident => [ 'size' ] },
798 { -bind => [ 'size', 3 ] }, { -bind => [ 'size', 7 ] },
799 ] }
800 # query
802 ( size NOT BETWEEN ? AND ? )
803 [ 3, 7 ]
804 in op
806 The RHS of in/not_in is either an expr/value or an arrayref of
807 exprs/values:
808 # expr
810 { foo => { -in => [ 1, 2 ] } }
811 # aqt
813 { -op => [
814 'in', { -ident => [ 'foo' ] }, { -bind => [ 'foo', 1 ] },
815 { -bind => [ 'foo', 2 ] },
816 ] }
817 # query
819 foo IN ( ?, ? )
820 [ 1, 2 ]
821 # expr
823 { bar => { -not_in => \"(1, 2)" } }
824 # aqt
826 { -op =>
827 [ 'not_in', { -ident => [ 'bar' ] }, { -literal => [ '1, 2' ] } ]
828 }
829 # query
831 bar NOT IN ( 1, 2 )
832 []
833 A non-trivial LHS is expanded with ident as the default rather than
835 value:
836 # expr
838 { -in => [
839 { -row => [ 'x', 'y' ] }, { -row => [ 1, 2 ] },
840 { -row => [ 3, 4 ] },
841 ] }
842 # aqt
844 { -op => [
845 'in', { -row => [ { -ident => [ 'x' ] }, { -ident => [ 'y' ] } ] },
846 { -row => [ { -bind => [ undef, 1 ] }, { -bind => [ undef, 2 ] } ] },
847 { -row => [ { -bind => [ undef, 3 ] }, { -bind => [ undef, 4 ] } ] },
848 ] }
849 # query
851 (x, y) IN ( (?, ?), (?, ?) )
852 [ 1, 2, 3, 4 ]
853 and/or ops
855 expands the same way as a plain arrayref/hashref expression but with
856 the logic type set to the op name.
857 is op
859 Expands is and is_not to null checks, RHS value must be undef:
860 # expr
862 { -is => [ 'foo', undef ] }
863 # aqt
865 { -op => [ 'is_null', { -ident => [ 'foo' ] } ] }
866 # query
868 foo IS NULL
869 []
870 # expr
872 { bar => { -is_not => undef } }
873 # aqt
875 { -op => [ 'is_not_null', { -ident => [ 'bar' ] } ] }
876 # query
878 bar IS NOT NULL
879 []
880 ident op
882 Expands a string ident to an arrayref by splitting on the configured
883 separator, almost always '.':
884 # expr
886 { -ident => 'foo.bar' }
887 # aqt
889 { -ident => [ 'foo', 'bar' ] }
890 # query
892 foo.bar
893 []
894 value op
896 Expands to a bind node with the currently applicable column name if
897 known:
898 # expr
900 { foo => { '=' => { -value => 3 } } }
901 # aqt
903 { -op => [ '=', { -ident => [ 'foo' ] }, { -bind => [ 'foo', 3 ] } ] }
904 # query
906 foo = ?
907 [ 3 ]
908
910 select
911 A select node accepts select, from, where and order_by clauses.
912 The select clause is expanded as a list expression with a -ident
914 default:
915 # expr
917 { -select => { _ => [ 'foo', 'bar', { -count => 'baz' } ] } }
918 # aqt
920 { -select => { select => { -op => [
921 ',', { -ident => [ 'foo' ] }, { -ident => [ 'bar' ] },
922 { -func => [ 'count', { -ident => [ 'baz' ] } ] },
923 ] } } }
924 # query
926 SELECT foo, bar, COUNT(baz)
927 []
928 The from clause is expanded as a list expression with a -ident default:
930 # expr
932 { -select => {
933 from => [ 'schema1.table1', { -ident => [ 'schema2', 'table2' ] } ]
934 } }
935 # aqt
937 { -select => { from => { -from_list => [
938 { -ident => [ 'schema1', 'table1' ] },
939 { -ident => [ 'schema2', 'table2' ] },
940 ] } } }
941 # query
943 FROM schema1.table1, schema2.table2
944 []
945 The where clause is expanded as a plain expression:
947 # expr
949 { -select => { where => { foo => 3 } } }
950 # aqt
952 { -select => { where => {
953 -op => [ '=', { -ident => [ 'foo' ] }, { -bind => [ 'foo', 3 ] } ]
954 } } }
955 # query
957 WHERE foo = ?
958 [ 3 ]
959 The order_by clause expands as a list expression at top level, but a
961 hashref element may be either an expr or a hashpair with key -asc or
962 -desc to indicate an order by direction:
963 # expr
965 { -select =>
966 { order_by => [ 'foo', { -desc => 'bar' }, { -max => 'baz' } ] }
967 }
968 # aqt
970 { -select => { order_by => { -op => [
971 ',', { -ident => [ 'foo' ] }, {
972 -op => [ ',', { -op => [ 'desc', { -ident => [ 'bar' ] } ] } ]
973 }, { -func => [ 'max', { -ident => [ 'baz' ] } ] },
974 ] } } }
975 # query
977 ORDER BY foo, bar DESC, MAX(baz)
978 []
979 An insert node accepts an into/target clause, a fields clause, a
982 values/from clause, and a returning clause.
983 The target clause is expanded with an ident default.
985 The fields clause is expanded as a list expression if an arrayref, and
987 otherwise passed through.
988 The from clause may either be an expr, a literal, an arrayref of column
990 values, or a hashref mapping colum names to values.
991 The returning clause is expanded as a list expr with an ident default.
993 # expr
995 { -insert => {
996 into => 'foo',
997 returning => 'id',
998 values => { bar => 'yay', baz => 'argh' },
999 } }
1000 # aqt
1002 { -insert => {
1003 fields =>
1004 { -row => [ { -ident => [ 'bar' ] }, { -ident => [ 'baz' ] } ] },
1005 from => { -values => [ { -row => [
1006 { -bind => [ 'bar', 'yay' ] },
1007 { -bind => [ 'baz', 'argh' ] },
1008 ] } ] },
1009 returning => { -op => [ ',', { -ident => [ 'id' ] } ] },
1010 target => { -ident => [ 'foo' ] },
1011 } }
1012 # query
1014 INSERT INTO foo (bar, baz) VALUES (?, ?) RETURNING id
1015 [ 'yay', 'argh' ]
1016 # expr
1018 { -insert => {
1019 fields => [ 'bar', 'baz' ],
1020 from => { -select => { _ => [ 'bar', 'baz' ], from => 'other' } },
1021 into => 'foo',
1022 } }
1023 # aqt
1025 { -insert => {
1026 fields => { -row => [ { -op =>
1027 [ ',', { -ident => [ 'bar' ] }, { -ident => [ 'baz' ] } ]
1028 } ] },
1029 from => { -select => {
1030 from => { -ident => [ 'other' ] },
1031 select => { -op =>
1032 [ ',', { -ident => [ 'bar' ] }, { -ident => [ 'baz' ] } ]
1033 },
1034 } },
1035 target => { -ident => [ 'foo' ] },
1036 } }
1037 # query
1039 INSERT INTO foo (bar, baz) SELECT bar, baz FROM other
1040 []
1041 update
1043 An update node accepts update/target (either may be used at expansion
1044 time), set, where, and returning clauses.
1045 The target clause is expanded with an ident default.
1047 The set clause (if not already a list expr) is expanded as a hashref
1049 where the keys are identifiers to be set and the values are
1050 exprs/values.
1051 The where clauses is expanded as a normal expr.
1053 The returning clause is expanded as a list expr with an ident default.
1055 # expr
1057 { -update => {
1058 _ => 'foo',
1059 returning => [ 'id', 'baz' ],
1060 set => { bar => 3, baz => { baz => { '+' => 1 } } },
1061 where => { -not => { -ident => 'quux' } },
1062 } }
1063 # aqt
1065 { -update => {
1066 returning =>
1067 {
1068 -op => [ ',', { -ident => [ 'id' ] }, { -ident => [ 'baz' ] } ]
1069 },
1070 set => { -op => [
1071 ',', { -op =>
1072 [ '=', { -ident => [ 'bar' ] }, { -bind => [ 'bar', 3 ] } ]
1073 }, { -op => [
1074 '=', { -ident => [ 'baz' ] }, { -op => [
1075 '+', { -ident => [ 'baz' ] },
1076 { -bind => [ 'baz', 1 ] },
1077 ] },
1078 ] },
1079 ] },
1080 target => { -ident => [ 'foo' ] },
1081 where => { -op => [ 'not', { -ident => [ 'quux' ] } ] },
1082 } }
1083 # query
1085 UPDATE foo SET bar = ?, baz = baz + ? WHERE (NOT quux) RETURNING id, baz
1086 [ 3, 1 ]
1087 delete
1089 delete accepts from/target, where, and returning clauses.
1090 The target clause is expanded with an ident default.
1092 The where clauses is expanded as a normal expr.
1094 The returning clause is expanded as a list expr with an ident default.
1096 # expr
1098 { -delete => {
1099 from => 'foo',
1100 returning => 'id',
1101 where => { bar => { '<' => 10 } },
1102 } }
1103 # aqt
1105 { -delete => {
1106 returning => { -op => [ ',', { -ident => [ 'id' ] } ] },
1107 target => { -op => [ ',', { -ident => [ 'foo' ] } ] },
1108 where => { -op =>
1109 [ '<', { -ident => [ 'bar' ] }, { -bind => [ 'bar', 10 ] } ]
1110 },
1111 } }
1112 # query
1114 DELETE FROM foo WHERE bar < ? RETURNING id
1115 [ 10 ]
1116perl v5.36.0 2023-01-20 SQL::Abstract::Reference(3)