Convert a search and replace to a function

Question

Given this search and replace expression, which works fine on one or multiple visually selected lines:

:'<,'>s/\v.{-}(`.{-}`).{-}(\\'.{-}\\').*/$this->execSQL('DELETE FROM \1 WHERE id = \2;');\r&/

I would like to stick this in a function to be call'd or mapped to a key.

Something like this works for a single line, but behaves differently on multiple lines than when I run the search and replace directly:

 function! CreateDeleteStatements()
     :'<,'>s/\v.{-}(`.{-}`).{-}(\\'.{-}\\').*/$this->execSQL('DELETE FROM \1 WHERE id = \2;');\r&/
 endfunction

Edit #1: By behaves differently on multiple lines, it seems to be a cursor position problem.

By removing the leading :'<,'> from the command within the function:

 function! CreateDeleteStatements()
     s/\v.{-}(`.{-}`).{-}(\\'.{-}\\').*/$this->execSQL('DELETE FROM \1 WHERE id = \2;');\r&/
 endfunction

If I then select 4 lines, it will run 4 times, but only at the position of the first line (I end up with 4 lines above this, rather than interleaved between each line of the selection).

Have tried getpost() and setpos() and winsaveview() and winrestview(), but doesn't seem to be changing the cursor position after the substitution.

Edit #2: Adding sample text to work against

Example input text:

$this->execSQL('INSERT INTO `table_lowercase_and_underscores1` (`id`, `field1`, `field2`, `field3`, `field4`, `field5`) VALUES (\'first-guuid-lowercase-numbers-hyphens\', \'guuid-lowercase-numbers-hyphens\', \'guuid-lowercase-numbers-hyphens\', 0, \'2018-04-09 03:58:59\', 0);');
$this->execSQL('INSERT INTO `table_lowercase_and_underscores2` (`id`, `field1`, `field2`, `field3`, `field4`, `field5`) VALUES (\'first-guuid-lowercase-numbers-hyphens\', \'guuid-lowercase-numbers-hyphens\', \'guuid-lowercase-numbers-hyphens\', 0, \'2018-04-09 03:58:59\', 0);');
$this->execSQL('INSERT INTO `table_lowercase_and_underscores3` (`id`, `field1`, `field2`, `field3`, `field4`, `field5`) VALUES (\'first-guuid-lowercase-numbers-hyphens\', \'guuid-lowercase-numbers-hyphens\', \'guuid-lowercase-numbers-hyphens\', 0, \'2018-04-09 03:58:59\', 0);');

And expected output

$this->execSQL('DELETE FROM `table_lowercase_and_underscores1` WHERE id =  \'first-guuid-lowercase-numbers-hyphens\';');
$this->execSQL('INSERT INTO `table_lowercase_and_underscores1` (`id`, `field1`, `field2`, `field3`, `field4`, `field5`) VALUES (\'first-guuid-lowercase-numbers-hyphens\', \'guuid-lowercase-numbers-hyphens\', \'guuid-lowercase-numbers-hyphens\', 0, \'2018-04-09 03:58:59\', 0);');
$this->execSQL('DELETE FROM `table_lowercase_and_underscores2` WHERE id =  \'first-guuid-lowercase-numbers-hyphens\';');
$this->execSQL('INSERT INTO `table_lowercase_and_underscores2` (`id`, `field1`, `field2`, `field3`, `field4`, `field5`) VALUES (\'first-guuid-lowercase-numbers-hyphens\', \'guuid-lowercase-numbers-hyphens\', \'guuid-lowercase-numbers-hyphens\', 0, \'2018-04-09 03:58:59\', 0);');
$this->execSQL('DELETE FROM `table_lowercase_and_underscores3` WHERE id =  \'first-guuid-lowercase-numbers-hyphens\';');
$this->execSQL('INSERT INTO `table_lowercase_and_underscores3` (`id`, `field1`, `field2`, `field3`, `field4`, `field5`) VALUES (\'first-guuid-lowercase-numbers-hyphens\', \'guuid-lowercase-numbers-hyphens\', \'guuid-lowercase-numbers-hyphens\', 0, \'2018-04-09 03:58:59\', 0);');

Answer 1

The simplest way of doing what you want is probably not to use a function, but instead use a command:

command! -range MySubCmd <line1>,<line2>s/\v.{-}(`.{-}`).{-}(\\'.{-}\\').*/$this->execSQL('DELETE FROM \1 WHERE id = \2;');\r&/

We use the -range attribute to pass the range we execute over through to <line1> and <line2> that get expanded to the range we set with '<,'> when we selected visually. See :h command-range

If you really want to define a function for this the equivalent syntax is

function! MySubFunction() range
    execute a:firstline.','.a:lastline.'s/\v.{-}(`.{-}`).{-}(\\''.{-}\\'').*/$this->execSQL(''DELETE FROM \1 WHERE id = \2;'');\r&/'
endfunction

Add range after the function signature and then the range passed to :call will be available as a:firstline and a:lastline. We have to use execute magic to evaluate those variables, and so we need to double up quotes in the substitute expression. The reason that your original function wasn't interleaving (I think), was that after the function was called on each line, vim helpfully moved us back to our starting position before moving down to the next line, disregarding the fact that things had changed positions - hence performing the substitute on the same line 3 times. See :h function-range for details, but essentially the function will be called on each line if it doesn't handle the range itself, with vim saving and restoring the cursor postion afterwords - which is why nothing you tried inside the function changed anything. We just avoid this by making it handle the range iself, so it is only called once.