In a PostgreSQL procedure, a variable QUERY of type varchar2 holds a query string that is executed as
OPEN resultset FOR QUERY;
In QUERY there is a call to the function string_to_array(mystring,'|'). How do I escape '|' in the query string? I have tried
QUERY := 'select string_to_array(mystring,''|'') from tablename where mycol=' ||fn_param|| '' ;
Here fn_param is a character varying type input to the procedure. When I call the above procedure from Java it gives the error ERROR: column "test" does not exist, where test is the value I pass for fn_param. The procedure is defined as
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;