1SQL::Abstract::ReferencUes(e3r)Contributed Perl DocumentSaQtLi:o:nAbstract::Reference(3)
2
3
4

NAME

6       SQL::Abstract::Reference - Reference documentation for SQL::Abstract
7

TERMS

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
15   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
21   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
25         my ($sql, @bind) = $sqla->some_method(@args);
26         my @hashes = @{$dbh->do($sql, { Slice => {} }, @bind)};
27
28       If you see a comment before a code block saying "# query", the SQL +
29       bind array is what's being described.
30
31   Expander
32       An expander subroutine is written as:
33
34         sub {
35           my ($sqla, $name, $value, $k) = @_;
36           ...
37           return $aqt;
38         }
39
40       $name is the expr node type for node expanders, the op name for op
41       expanders, and the clause name for clause expanders.
42
43       $value is the body of the thing being expanded
44
45       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
49       This can trivially be converted to an "ident" type AQT node with:
50
51         my $ident = $sqla->expand_expr({ -ident => $k });
52
53   Renderer
54       A renderer subroutine looks like:
55
56         sub {
57           my ($sqla, $type, $value) = @_;
58           ...
59           $sqla->join_query_parts($join, @parts);
60         }
61
62       and can be registered on a per-type, per-op or per-clause basis.
63

AQT node types

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
69       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
72   literal
73         # expr
74         { -literal => [ 'SPANG(?, ?)', 1, 27 ] }
75
76         # query
77         SPANG(?, ?)
78         [ 1, 27 ]
79
80   ident
81         # expr
82         { -ident => 'foo' }
83
84         # query
85         foo
86         []
87
88         # expr
89         { -ident => [ 'foo', 'bar' ] }
90
91         # query
92         foo.bar
93         []
94
95   bind
96         # expr
97         { -bind => [ 'colname', 'value' ] }
98
99         # query
100         ?
101         [ 'value' ]
102
103   row
104         # expr
105         {
106           -row => [ { -bind => [ 'r', 1 ] }, { -ident => [ 'clown', 'car' ] } ]
107         }
108
109         # query
110         (?, clown.car)
111         [ 1 ]
112
113   func
114         # expr
115         {
116           -func => [ 'foo', { -ident => [ 'bar' ] }, { -bind => [ undef, 7 ] } ]
117         }
118
119         # query
120         FOO(bar, ?)
121         [ 7 ]
122
123   op
124       Standard binop:
125
126         # expr
127         { -op => [
128             '=', { -ident => [ 'bomb', 'status' ] },
129             { -value => 'unexploded' },
130         ] }
131
132         # query
133         bomb.status = ?
134         [ 'unexploded' ]
135
136       Prefix unop:
137
138         # expr
139         { -op => [ '-', { -ident => 'foo' } ] }
140
141         # query
142         - foo
143         []
144
145       Not as special case parenthesised unop:
146
147         # expr
148         { -op => [ 'not', { -ident => 'explosive' } ] }
149
150         # query
151         (NOT explosive)
152         []
153
154       Postfix unop: (is_null, is_not_null, asc, desc)
155
156         # expr
157         { -op => [ 'is_null', { -ident => [ 'bobby' ] } ] }
158
159         # query
160         bobby IS NULL
161         []
162
163       AND and OR:
164
165         # expr
166         { -op =>
167             [ 'and', { -ident => 'x' }, { -ident => 'y' }, { -ident => 'z' } ]
168         }
169
170         # query
171         ( x AND y AND z )
172         []
173
174       IN (and NOT IN):
175
176         # expr
177         { -op => [
178             'in', { -ident => 'card' }, { -bind => [ 'card', 3 ] },
179             { -bind => [ 'card', 'J' ] },
180         ] }
181
182         # query
183         card IN ( ?, ? )
184         [ 3, 'J' ]
185
186       BETWEEN (and NOT BETWEEN):
187
188         # expr
189         { -op => [
190             'between', { -ident => 'pints' }, { -bind => [ 'pints', 2 ] },
191             { -bind => [ 'pints', 4 ] },
192         ] }
193
194         # query
195         ( pints BETWEEN ? AND ? )
196         [ 2, 4 ]
197
198       Comma (use -row for parens):
199
200         # expr
201         { -op => [ ',', { -literal => [ 1 ] }, { -literal => [ 2 ] } ] }
202
203         # query
204         1, 2
205         []
206
207   values
208         # expr
209         { -values =>
210             { -row => [ { -bind => [ undef, 1 ] }, { -bind => [ undef, 2 ] } ] }
211         }
212
213         # query
214         VALUES (?, ?)
215         [ 1, 2 ]
216
217         # expr
218         { -values => [
219             { -row => [ { -literal => [ 1 ] }, { -literal => [ 2 ] } ] },
220             { -row => [ { -literal => [ 3 ] }, { -literal => [ 4 ] } ] },
221         ] }
222
223         # query
224         VALUES (1, 2), (3, 4)
225         []
226
227   keyword
228         # expr
229         { -keyword => 'insert_into' }
230
231         # query
232         INSERT INTO
233         []
234
235   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

Expressions

241   node expr
242       The simplest expression is just an AQT node:
243
244         # expr
245         { -ident => [ 'foo', 'bar' ] }
246
247         # aqt
248         { -ident => [ 'foo', 'bar' ] }
249
250         # query
251         foo.bar
252         []
253
254       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
257         # expr
258         { -ident => 'foo.bar' }
259
260         # aqt
261         { -ident => [ 'foo', 'bar' ] }
262
263         # query
264         foo.bar
265         []
266
267   identifier hashpair types
268       hashtriple
269
270         # expr
271         { id => { op => 'value' } }
272
273         # aqt
274         { -op =>
275             [ 'op', { -ident => [ 'id' ] }, { -bind => [ 'id', 'value' ] } ]
276         }
277
278         # query
279         id OP ?
280         [ 'value' ]
281
282       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
285         # expr
286         { id => { '!=' => undef } }
287
288         # aqt
289         { -op => [ 'is_not_null', { -ident => [ 'id' ] } ] }
290
291         # query
292         id IS NOT NULL
293         []
294
295       identifier hashpair w/simple value
296
297       Equivalent to a hashtriple with an op of '='.
298
299         # expr
300         { id => 'value' }
301
302         # aqt
303         {
304           -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 'value' ] } ]
305         }
306
307         # query
308         id = ?
309         [ 'value' ]
310
311       (an object value will also follow this code path)
312
313       identifier hashpair w/undef RHS
314
315       Converted to IS NULL :
316
317         # expr
318         { id => undef }
319
320         # aqt
321         { -op => [ 'is_null', { -ident => [ 'id' ] } ] }
322
323         # query
324         id IS NULL
325         []
326
327       (equivalent to the -is operator) :
328
329         # expr
330         { id => { -is => undef } }
331
332         # aqt
333         { -op => [ 'is_null', { -ident => [ 'id' ] } ] }
334
335         # query
336         id IS NULL
337         []
338
339       identifier hashpair w/literal RHS
340
341       Directly appended to the key, remember you need to provide an operator:
342
343         # expr
344         { id => \"= dont_try_this_at_home" }
345
346         # aqt
347         { -literal => [ 'id = dont_try_this_at_home' ] }
348
349         # query
350         id = dont_try_this_at_home
351         []
352
353         # expr
354         { id => \[
355               "= seriously(?, ?, ?, ?)",
356               "use",
357               "-ident",
358               "and",
359               "-func",
360             ]
361         }
362
363         # aqt
364         { -literal =>
365             [ 'id = seriously(?, ?, ?, ?)', 'use', -ident => 'and', '-func' ]
366         }
367
368         # query
369         id = seriously(?, ?, ?, ?)
370         [ 'use', -ident => 'and', '-func' ]
371
372       (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
376       identifier hashpair w/arrayref value
377
378       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
381         # expr
382         { id => [ 3, 4, { '>' => 12 } ] }
383
384         # 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
394         # query
395         ( id = ? OR id = ? OR id > ? )
396         [ 3, 4, 12 ]
397
398         # expr
399         { -or => [ { id => 3 }, { id => 4 }, { id => { '>' => 12 } } ] }
400
401         # 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
411         # query
412         ( id = ? OR id = ? OR id > ? )
413         [ 3, 4, 12 ]
414
415       Special Case: If the first element of the arrayref is -or or -and,
416       that's used as the top level logic op:
417
418         # expr
419         { id => [ -and => { '>' => 3 }, { '<' => 6 } ] }
420
421         # aqt
422         { -op => [
423             'and',
424             { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
425             { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 6 ] } ] },
426         ] }
427
428         # query
429         ( id > ? AND id < ? )
430         [ 3, 6 ]
431
432       identifier hashpair w/hashref value
433
434       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
438         # expr
439         { id => { '<' => 4, '>' => 3 } }
440
441         # aqt
442         { -op => [
443             'and',
444             { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
445             { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
446         ] }
447
448         # query
449         ( id < ? AND id > ? )
450         [ 4, 3 ]
451
452       is sugar for:
453
454         # expr
455         { -and => [ { id => { '<' => 4 } }, { id => { '>' => 3 } } ] }
456
457         # aqt
458         { -op => [
459             'and',
460             { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
461             { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
462         ] }
463
464         # query
465         ( id < ? AND id > ? )
466         [ 4, 3 ]
467
468   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
473       operator hashpair w/node type
474
475       If a node type expander is registered for the key, the hashpair is
476       treated as a "node expr".
477
478       operator hashpair w/registered op
479
480       If an expander is registered for the op name, that's run and the result
481       returned:
482
483         # expr
484         { -in => [ 'foo', 1, 2, 3 ] }
485
486         # aqt
487         { -op => [
488             'in', { -ident => [ 'foo' ] }, { -bind => [ undef, 1 ] },
489             { -bind => [ undef, 2 ] }, { -bind => [ undef, 3 ] },
490         ] }
491
492         # query
493         foo IN ( ?, ?, ? )
494         [ 1, 2, 3 ]
495
496       operator hashpair w/not prefix
497
498       If the op name starts -not_ this is stripped and turned into a -not
499       wrapper around the result:
500
501         # expr
502         { -not_ident => 'foo' }
503
504         # aqt
505         { -op => [ 'not', { -ident => [ 'foo' ] } ] }
506
507         # query
508         (NOT foo)
509         []
510
511       is equivalent to:
512
513         # expr
514         { -not => { -ident => 'foo' } }
515
516         # aqt
517         { -op => [ 'not', { -ident => [ 'foo' ] } ] }
518
519         # query
520         (NOT foo)
521         []
522
523       operator hashpair with unknown op
524
525       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
529         # expr
530         { -count => { -ident => '*' } }
531
532         # aqt
533         { -func => [ 'count', { -ident => [ '*' ] } ] }
534
535         # query
536         COUNT(*)
537         []
538
539       If not, an unknown op will expand into a "-op" node.
540
541   hashref expr
542       A hashref with more than one pair becomes a "-and" over its hashpairs,
543       i.e.
544
545         # expr
546         { x => 1, y => 2 }
547
548         # aqt
549         { -op => [
550             'and',
551             { -op => [ '=', { -ident => [ 'x' ] }, { -bind => [ 'x', 1 ] } ] },
552             { -op => [ '=', { -ident => [ 'y' ] }, { -bind => [ 'y', 2 ] } ] },
553         ] }
554
555         # query
556         ( x = ? AND y = ? )
557         [ 1, 2 ]
558
559       is short hand for:
560
561         # expr
562         { -and => [ { x => 1 }, { y => 2 } ] }
563
564         # aqt
565         { -op => [
566             'and',
567             { -op => [ '=', { -ident => [ 'x' ] }, { -bind => [ 'x', 1 ] } ] },
568             { -op => [ '=', { -ident => [ 'y' ] }, { -bind => [ 'y', 2 ] } ] },
569         ] }
570
571         # query
572         ( x = ? AND y = ? )
573         [ 1, 2 ]
574
575   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
581         # expr
582         [ { x => 1 }, [ { y => 2 }, { z => 3 } ], 'key', 'value', \"lit()" ]
583
584         # 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
603         # query
604         ( x = ? OR ( y = ? OR z = ? ) OR key = ? OR lit() )
605         [ 1, 2, 3, 'value' ]
606

Default Expanders

608   bool
609       Turns the old -bool syntax into the value expression, i.e.
610
611         # expr
612         { -bool => { -ident => 'foo' } }
613
614         # aqt
615         { -ident => [ 'foo' ] }
616
617         # query
618         foo
619         []
620
621       behaves the same way as the now-directly-supported
622
623         # expr
624         { -ident => 'foo' }
625
626         # aqt
627         { -ident => [ 'foo' ] }
628
629         # query
630         foo
631         []
632
633   row
634       Expands the elements of the value arrayref:
635
636         # expr
637         { -row => [ 1, { -ident => 'foo' }, 2, 3 ] }
638
639         # aqt
640         { -row => [
641             { -bind => [ undef, 1 ] }, { -ident => [ 'foo' ] },
642             { -bind => [ undef, 2 ] }, { -bind => [ undef, 3 ] },
643         ] }
644
645         # query
646         (?, foo, ?, ?)
647         [ 1, 2, 3 ]
648
649   op
650       If an expander is registered for the op name, delegates to the
651       expander; if not, expands the argument values:
652
653         # expr
654         { -op => [ 'ident', 'foo.bar' ] }
655
656         # aqt
657         { -ident => [ 'foo', 'bar' ] }
658
659         # query
660         foo.bar
661         []
662
663         # expr
664         { -op => [ '=', { -ident => 'foo' }, 3 ] }
665
666         # aqt
667         { -op => [ '=', { -ident => [ 'foo' ] }, { -bind => [ undef, 3 ] } ] }
668
669         # query
670         foo = ?
671         [ 3 ]
672
673   func
674       Expands the argument values:
675
676         # expr
677         { -func => [ 'coalesce', { -ident => 'thing' }, 'fallback' ] }
678
679         # aqt
680         { -func => [
681             'coalesce', { -ident => [ 'thing' ] },
682             { -bind => [ undef, 'fallback' ] },
683         ] }
684
685         # query
686         COALESCE(thing, ?)
687         [ 'fallback' ]
688
689   values
690       A hashref value is expanded as an expression:
691
692         # expr
693         { -values => { -row => [ 1, 2 ] } }
694
695         # aqt
696         { -values => [
697             { -row => [ { -bind => [ undef, 1 ] }, { -bind => [ undef, 2 ] } ] }
698         ] }
699
700         # query
701         VALUES (?, ?)
702         [ 1, 2 ]
703
704       An arrayref value's elements are either expressions or arrayrefs to be
705       treated as rows:
706
707         # expr
708         { -values => [ { -row => [ 1, 2 ] }, [ 3, 4 ] ] }
709
710         # aqt
711         { -values => [
712             { -row => [ { -bind => [ undef, 1 ] }, { -bind => [ undef, 2 ] } ] },
713             { -row => [ { -bind => [ undef, 3 ] }, { -bind => [ undef, 4 ] } ] },
714         ] }
715
716         # query
717         VALUES (?, ?), (?, ?)
718         [ 1, 2, 3, 4 ]
719
720   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
725         # expr
726         { -list => [ { -ident => 'foo' } ] }
727
728         # aqt
729         { -op => [ ',', { -ident => [ 'foo' ] } ] }
730
731         # query
732         foo
733         []
734
735         # expr
736         { -list => [ { -ident => 'foo' }, { -ident => 'bar' } ] }
737
738         # aqt
739         { -op => [ ',', { -ident => [ 'foo' ] }, { -ident => [ 'bar' ] } ] }
740
741         # query
742         foo, bar
743         []
744
745   between op
746       The RHS of between must either be a pair of exprs/plain values, or a
747       single literal expr:
748
749         # expr
750         { -between => [ 'size', 3, { -ident => 'max_size' } ] }
751
752         # aqt
753         { -op => [
754             'between', { -ident => [ 'size' ] }, { -bind => [ undef, 3 ] },
755             { -ident => [ 'max_size' ] },
756         ] }
757
758         # query
759         ( size BETWEEN ? AND max_size )
760         [ 3 ]
761
762         # expr
763         { size => { -between => [ 3, { -ident => 'max_size' } ] } }
764
765         # aqt
766         { -op => [
767             'between', { -ident => [ 'size' ] }, { -bind => [ 'size', 3 ] },
768             { -ident => [ 'max_size' ] },
769         ] }
770
771         # query
772         ( size BETWEEN ? AND max_size )
773         [ 3 ]
774
775         # expr
776         { size => { -between => \"3 AND 7" } }
777
778         # aqt
779         { -op =>
780             [
781               'between', { -ident => [ 'size' ] },
782               { -literal => [ '3 AND 7' ] },
783             ]
784         }
785
786         # query
787         ( size BETWEEN 3 AND 7 )
788         []
789
790       not_between is also expanded:
791
792         # expr
793         { size => { -not_between => [ 3, 7 ] } }
794
795         # aqt
796         { -op => [
797             'not_between', { -ident => [ 'size' ] },
798             { -bind => [ 'size', 3 ] }, { -bind => [ 'size', 7 ] },
799         ] }
800
801         # query
802         ( size NOT BETWEEN ? AND ? )
803         [ 3, 7 ]
804
805   in op
806       The RHS of in/not_in is either an expr/value or an arrayref of
807       exprs/values:
808
809         # expr
810         { foo => { -in => [ 1, 2 ] } }
811
812         # aqt
813         { -op => [
814             'in', { -ident => [ 'foo' ] }, { -bind => [ 'foo', 1 ] },
815             { -bind => [ 'foo', 2 ] },
816         ] }
817
818         # query
819         foo IN ( ?, ? )
820         [ 1, 2 ]
821
822         # expr
823         { bar => { -not_in => \"(1, 2)" } }
824
825         # aqt
826         { -op =>
827             [ 'not_in', { -ident => [ 'bar' ] }, { -literal => [ '1, 2' ] } ]
828         }
829
830         # query
831         bar NOT IN ( 1, 2 )
832         []
833
834       A non-trivial LHS is expanded with ident as the default rather than
835       value:
836
837         # expr
838         { -in => [
839             { -row => [ 'x', 'y' ] }, { -row => [ 1, 2 ] },
840             { -row => [ 3, 4 ] },
841         ] }
842
843         # 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
850         # query
851         (x, y) IN ( (?, ?), (?, ?) )
852         [ 1, 2, 3, 4 ]
853
854   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
858   is op
859       Expands is and is_not to null checks, RHS value must be undef:
860
861         # expr
862         { -is => [ 'foo', undef ] }
863
864         # aqt
865         { -op => [ 'is_null', { -ident => [ 'foo' ] } ] }
866
867         # query
868         foo IS NULL
869         []
870
871         # expr
872         { bar => { -is_not => undef } }
873
874         # aqt
875         { -op => [ 'is_not_null', { -ident => [ 'bar' ] } ] }
876
877         # query
878         bar IS NOT NULL
879         []
880
881   ident op
882       Expands a string ident to an arrayref by splitting on the configured
883       separator, almost always '.':
884
885         # expr
886         { -ident => 'foo.bar' }
887
888         # aqt
889         { -ident => [ 'foo', 'bar' ] }
890
891         # query
892         foo.bar
893         []
894
895   value op
896       Expands to a bind node with the currently applicable column name if
897       known:
898
899         # expr
900         { foo => { '=' => { -value => 3 } } }
901
902         # aqt
903         { -op => [ '=', { -ident => [ 'foo' ] }, { -bind => [ 'foo', 3 ] } ] }
904
905         # query
906         foo = ?
907         [ 3 ]
908

Query Types

910   select
911       A select node accepts select, from, where and order_by clauses.
912
913       The select clause is expanded as a list expression with a -ident
914       default:
915
916         # expr
917         { -select => { _ => [ 'foo', 'bar', { -count => 'baz' } ] } }
918
919         # aqt
920         { -select => { select => { -op => [
921                 ',', { -ident => [ 'foo' ] }, { -ident => [ 'bar' ] },
922                 { -func => [ 'count', { -ident => [ 'baz' ] } ] },
923         ] } } }
924
925         # query
926         SELECT foo, bar, COUNT(baz)
927         []
928
929       The from clause is expanded as a list expression with a -ident default:
930
931         # expr
932         { -select => {
933             from => [ 'schema1.table1', { -ident => [ 'schema2', 'table2' ] } ]
934         } }
935
936         # aqt
937         { -select => { from => { -from_list => [
938                 { -ident => [ 'schema1', 'table1' ] },
939                 { -ident => [ 'schema2', 'table2' ] },
940         ] } } }
941
942         # query
943         FROM schema1.table1, schema2.table2
944         []
945
946       The where clause is expanded as a plain expression:
947
948         # expr
949         { -select => { where => { foo => 3 } } }
950
951         # aqt
952         { -select => { where => {
953               -op => [ '=', { -ident => [ 'foo' ] }, { -bind => [ 'foo', 3 ] } ]
954         } } }
955
956         # query
957         WHERE foo = ?
958         [ 3 ]
959
960       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
964         # expr
965         { -select =>
966             { order_by => [ 'foo', { -desc => 'bar' }, { -max => 'baz' } ] }
967         }
968
969         # aqt
970         { -select => { order_by => { -op => [
971                 ',', { -ident => [ 'foo' ] }, {
972                   -op => [ ',', { -op => [ 'desc', { -ident => [ 'bar' ] } ] } ]
973                 }, { -func => [ 'max', { -ident => [ 'baz' ] } ] },
974         ] } } }
975
976         # query
977         ORDER BY foo, bar DESC, MAX(baz)
978         []
979
980
981       An insert node accepts an into/target clause, a fields clause, a
982       values/from clause, and a returning clause.
983
984       The target clause is expanded with an ident default.
985
986       The fields clause is expanded as a list expression if an arrayref, and
987       otherwise passed through.
988
989       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
992       The returning clause is expanded as a list expr with an ident default.
993
994         # expr
995         { -insert => {
996             into => 'foo',
997             returning => 'id',
998             values => { bar => 'yay', baz => 'argh' },
999         } }
1000
1001         # 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
1013         # query
1014         INSERT INTO foo (bar, baz) VALUES (?, ?) RETURNING id
1015         [ 'yay', 'argh' ]
1016
1017         # expr
1018         { -insert => {
1019             fields => [ 'bar', 'baz' ],
1020             from => { -select => { _ => [ 'bar', 'baz' ], from => 'other' } },
1021             into => 'foo',
1022         } }
1023
1024         # 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
1038         # query
1039         INSERT INTO foo (bar, baz) SELECT bar, baz FROM other
1040         []
1041
1042   update
1043       An update node accepts update/target (either may be used at expansion
1044       time), set, where, and returning clauses.
1045
1046       The target clause is expanded with an ident default.
1047
1048       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
1052       The where clauses is expanded as a normal expr.
1053
1054       The returning clause is expanded as a list expr with an ident default.
1055
1056         # expr
1057         { -update => {
1058             _ => 'foo',
1059             returning => [ 'id', 'baz' ],
1060             set => { bar => 3, baz => { baz => { '+' => 1 } } },
1061             where => { -not => { -ident => 'quux' } },
1062         } }
1063
1064         # 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
1084         # query
1085         UPDATE foo SET bar = ?, baz = baz + ? WHERE (NOT quux) RETURNING id, baz
1086         [ 3, 1 ]
1087
1088   delete
1089       delete accepts from/target, where, and returning clauses.
1090
1091       The target clause is expanded with an ident default.
1092
1093       The where clauses is expanded as a normal expr.
1094
1095       The returning clause is expanded as a list expr with an ident default.
1096
1097         # expr
1098         { -delete => {
1099             from => 'foo',
1100             returning => 'id',
1101             where => { bar => { '<' => 10 } },
1102         } }
1103
1104         # 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
1113         # query
1114         DELETE FROM foo WHERE bar < ? RETURNING id
1115         [ 10 ]
1116
1117
1118
1119perl v5.32.1                      2021-01-27       SQL::Abstract::Reference(3)
Impressum