title: Escaping pipeline in PostgreSQL procedure varchar2
body: <p>In a PostgreSQL procedure, a variable <code>QUERY</code> of type <code>varchar2</code> holds a query string that is executed as</p>
  <p><code>OPEN resultset FOR QUERY;</code></p>
  <p>In <code>QUERY</code> there is a call to the function <code>string_to_array(mystring,'|')</code>. How do I escape <code>'|'</code> in the query string? I have tried</p>
  <p><code>QUERY := 'select string_to_array(mystring,''|'') from tablename where mycol=' ||fn_param|| '' ;</code></p>
  <p>Here <code>fn_param</code> is a character varying type input to the procedure. When I call the above procedure from Java it gives the error <code>ERROR: column &quot;test&quot; does not exist</code>, where <code>test</code> is the value I pass for <code>fn_param</code>. The procedure is defined as</p>
  <pre><code>PROCEDURE myproc(fn_param character varying DEFAULT '0'::character varying, OUT resultset refcursor, OUT err character varying) IS
  QUERY   VARCHAR2 (100);
  BEGIN
      QUERY := 'select string_to_array(mystring,''|'') from tablename where mycol=' ||fn_param|| ' ';
      OPEN resultset FOR QUERY;
  END;
  </code></pre>