From: Matt S Trout <mst@shadowcat.co.uk>
Date: Mon, 30 Sep 2019 20:03:34 +0000 (+0000)
Subject: doc insert query
X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=scpubgit%2FQ-Branch.git;a=commitdiff_plain;h=47a7b2d1c138f80ec6c4e1ee21b21e691795e7ba

doc insert query
---

diff --git a/lib/SQL/Abstract/Reference.pm b/lib/SQL/Abstract/Reference.pm
index 41d95d0..44ad71c 100644
--- a/lib/SQL/Abstract/Reference.pm
+++ b/lib/SQL/Abstract/Reference.pm
@@ -984,6 +984,72 @@ an order by direction:
   ORDER BY foo, bar DESC, MAX(baz)
   []
 
+=head2
+
+An insert node accepts an into/target clause, a fields clause, a values/from
+clause, and a returning clause.
+
+Note that there is no clause named insert since into or values could be
+what the user meant by that, and it turned out to just be confusing.
+
+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 => { -op => [ ',', { -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 => { -from_list => [ { -ident => [ 'other' ] } ] },
+          select => { -op =>
+              [ ',', { -ident => [ 'bar' ] }, { -ident => [ 'baz' ] } ]
+          },
+      } },
+      target => { -op => [ ',', { -ident => [ 'foo' ] } ] },
+  } }
+
+  # query
+  INSERT INTO foo (bar, baz) SELECT bar, baz FROM other
+  []
+
 =head2 update
 
 An update node accepts update/target (either may be used at expansion time),