title: awk (or sed, or something else) - reference match on a previous line and add a line
body: <p>I want to add a procedure call to the first line of every single stored procedure in a MySQL database (more than 50 stored procedures) that passes the executing routine's name to the new call.</p>
  <p>There's no easy way to do this from within MySQL that I can work out, so I thought I'd give it a go exporting the routines using MySQLdump, editing the file with awk/sed/something and then re-creating the SPs. It's proved trickier than I expected.</p>
  <p>This involves finding the function name from the <code>CREATE ... PROCEDURE</code> line, and then adding the <code>CALL NewFunc(currentFuncName)</code> line after the next <code>BEGIN</code> statement. Importantly, the next BEGIN statement isn't always the same number of lines after the CREATE.</p>
  <p>I'm part-way there, I just can't work out how to tie up the two bits. I can get the function name with this:</p>
  <pre><code>awk '/CREATE.*PROCEDURE/ {gsub(/^.*PROCEDURE `|`\(.*$/,&quot;&quot;);print &quot;call newFuncHere(&quot; $1 &quot;);&quot;}'
  </code></pre>
  <p>How do I get awk to add the result of this <em>after</em> the next BEGIN statement?</p>
  <p>Example source:</p>
  <pre><code>CREATE DEFINER=`blah@%` PROCEDURE `theProcedure`(`param1` BIGINT(21) UNSIGNED,
  theExisting BIGINT(21) UNSIGNED,
  INOUT `myStatus` VARCHAR(255) )
  SQL SECURITY INVOKER
  BEGIN
      DECLARE blah VARCHAR(32) DEFAULT NULL;
  </code></pre>
  <p>Example output:</p>
  <pre><code>CREATE DEFINER=`blah@%` PROCEDURE `theProcedure`(`param1` BIGINT(21) UNSIGNED,
  theExisting BIGINT(21) UNSIGNED,
  INOUT `myStatus` VARCHAR(255) )
  SQL SECURITY INVOKER
  BEGIN
      CALL newFuncHere(theProcedure);
      DECLARE blah VARCHAR(32) DEFAULT NULL;
  </code></pre>
  <p>Indentation is not relevant - just shown for example</p>
  <hr />
  <p>Edited to add, forgot about the requirement that DECLARE statements must come first, so this is what I've ended up with...</p>
  <pre><code># Initialise variables
  BEGIN { seen=0; beginCount=-1; createCount=-1; done=0; }
  
  # Ignore blank lines but still print them out
  /^$|^\t$/ { print $0 ; next ;}
  
  # If matches CREATE.*PROCEDURE|FUNCTION - increase createCount and set name of function/beginCount variables
  /^CREATE/{createCount++} ($5~/PROCEDURE/ || $5~/FUNCTION/)  { name=$6 ; beginCount=-1 }
  
  # If BEGIN is found - increase beginCount - this is to count the depth of BEGIN statements
  /BEGIN/ {beginCount++}
  
  # Increment seen variable if DECLARE is in this line
  /DECLARE/{seen++}
  
  # If beginCounter = 0, we're at BEGIN level 0 (starts at -1) and if done is not set we've not yet done anything. A
  # create depth of 0 (starts at -1), and we've seen at least one DECLARE line,
  # if this line does NOT contain DECLARE, add the new line we want, followed
  # by a newline and the current line. Reset some variables.
  !done &amp;&amp; !beginCount &amp;&amp; !createCount &amp;&amp; seen &amp;&amp; !/DECLARE/{ print &quot;CALL newFunction('&quot; name &quot;');&quot; ORS $0 ; seen=0;count++; done=1 ; next;}
  
  #{print createCount seen beginCount done}
  
  # If END is seen and we're inside a nested BEGIN statement, decrement beginCount
  # print out the line and skip to the next line
  /END.*;/ &amp;&amp; beginCount {beginCount--; print $0 ; next ;}
  
  # If END is seen and we're at the top level, reset some counters
  /END.*;/ &amp;&amp; !beginCount {createCount=-1 ; beginCount=-1 ; seen=0; done=0; }
  
  # print out the line
  { print $0 ;}
  </code></pre>
  <p>It's not perfect, it needs work to handle END IF statements, doesn't handle situations where the DECLARE statement covers multiple lines and there's a few other situations it doesn't work, and there's some redundant stuff in there from previous experiments - but it's good enough for my needs right now.</p>