title: Escaping a LIKE pattern or regexp string in Postgres 8.4 inside a stored procedure
body: <p>I am writing a stored procedure to find the sub-items of a main item and update it. The scenario is like this     </p>
  
  <pre><code>Id  Item Name               Parent Id  
  1   Item A 14.1             NULL  
  2   Item B 14.1.1           1      
  3   Item C 14.1.2           1       
  4   Item B 14.1.3           1       
  5   Item A 14.1.1.1         2 
  </code></pre>
  
  <p>I have posted another question on SO <a href="https://stackoverflow.com/questions/29407320/string-matching-in-postgresql-8-4">String matching in PostgreSQL 8.4</a> to get the children of an item. According to that answer i have to escape the item code and have to use it in the query. But I haven't got any method to escape that. The SP is as below:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION updateitemparentnew(bigint, int) RETURNS text
      LANGUAGE plpgsql STRICT
      AS $$
  DECLARE
      itemdetail RECORD;    
      codeSearch text;
      codeEscapedSearch text;
      result text;
  BEGIN   
  
  --Get th details of the current item
  SELECT INTO itemdetail * FROM om_item WHERE item_id = $1;
  
  codeSearch = itemdetail.item_code||'.';
  codeEscapedSearch = codeSearch;  --Need to be corrected. It should escape the item code
  
  -- Event 1=&gt; add 2 =&gt; edit 3 =&gt; delete
  IF $2 = 1 THEN
      --Find new children and update then
      result =  'UPDATE om_item SET item_parentid = '||itemdetail.item_id
             ||' WHERE item_id IN (
                   SELECT item_id FROM om_item
                   WHERE  item_code LIKE \''||codesearch||'%\'
                   AND item_code ~ \''||codeEscapedsearch||'[^.]+$\');';
  
  END IF;
  
  return result;
  END;
  $$;
  </code></pre>
  
  <p>In the query <code>codeEscapedSearch</code> should be escaped to handle the <code>.</code> in the code itself and the <code>.</code> in the regex.</p>