Add support for DELETE ... RETURNING ...

diff --git a/Changes b/Changes
index 0e9fe5a..9c9f93c 100644 (file)
--- a/Changes
+++ b/Changes
@@ -1,5 +1,7 @@
 Revision history for SQL::Abstract
 
+    - Support for DELETE ... RETURNING
+
 revision 1.82  2017-03-20
 -------------------------
     - Add explicit dependency on Sub::Quote (GH#8)

diff --git a/lib/SQL/Abstract.pm b/lib/SQL/Abstract.pm
index f4d4c0b..1b2fb07 100644 (file)
--- a/lib/SQL/Abstract.pm
+++ b/lib/SQL/Abstract.pm
@@ -469,17 +469,26 @@ sub select {
 
 
 sub delete {
-  my $self  = shift;
-  my $table = $self->_table(shift);
-  my $where = shift;
-
+  my $self    = shift;
+  my $table   = $self->_table(shift);
+  my $where   = shift;
+  my $options = shift;
 
   my($where_sql, @bind) = $self->where($where);
   my $sql = $self->_sqlcase('delete from') . " $table" . $where_sql;
 
+  if ($options->{returning}) {
+    my ($returning_sql, @returning_bind) = $self->_delete_returning ($options);
+    $sql .= $returning_sql;
+    push @bind, @returning_bind;
+  }
+
   return wantarray ? ($sql, @bind) : $sql;
 }
 
+sub _delete_returning { shift->_returning(@_) }
+
+
 
 #======================================================================
 # WHERE: entry point
@@ -2150,11 +2159,24 @@ for details.
 =back
 
 
-=head2 delete($table, \%where)
+=head2 delete($table, \%where, \%options)
 
 This takes a table name and optional hashref L<WHERE clause|/WHERE CLAUSES>.
 It returns an SQL DELETE statement and list of bind values.
 
+The optional C<\%options> hash reference may contain additional
+options to generate the delete SQL. Currently supported options
+are:
+
+=over 4
+
+=item returning
+
+See the C<returning> option to
+L<insert|/insert($table, \@values || \%fieldvals, \%options)>.
+
+=back
+
 =head2 where(\%where, $order)
 
 This is used to generate just the WHERE clause. For example,
@@ -3269,4 +3291,3 @@ terms as perl itself (either the GNU General Public License or
 the Artistic License)
 
 =cut
-

diff --git a/t/01generate.t b/t/01generate.t
index 020dfa5..e6d1c6c 100644 (file)
--- a/t/01generate.t
+++ b/t/01generate.t
@@ -595,6 +595,27 @@ my @tests = (
               stmt_q => 'UPDATE `mytable` SET `foo` = ? WHERE `baz` = ? RETURNING `id`, `created_at`',
               bind => [42, 32],
       },
+      {
+              func   => 'delete',
+              args   => ['test', {requestor => undef}, {returning => 'id'}],
+              stmt   => 'DELETE FROM test WHERE ( requestor IS NULL ) RETURNING id',
+              stmt_q => 'DELETE FROM `test` WHERE ( `requestor` IS NULL ) RETURNING `id`',
+              bind   => []
+      },
+      {
+              func   => 'delete',
+              args   => ['test', {requestor => undef}, {returning => \'*'}],
+              stmt   => 'DELETE FROM test WHERE ( requestor IS NULL ) RETURNING *',
+              stmt_q => 'DELETE FROM `test` WHERE ( `requestor` IS NULL ) RETURNING *',
+              bind   => []
+      },
+      {
+              func   => 'delete',
+              args   => ['test', {requestor => undef}, {returning => ['id', 'created_at']}],
+              stmt   => 'DELETE FROM test WHERE ( requestor IS NULL ) RETURNING id, created_at',
+              stmt_q => 'DELETE FROM `test` WHERE ( `requestor` IS NULL ) RETURNING `id`, `created_at`',
+              bind   => []
+      },
 );
 
 # check is( not) => undef