Escaping pipeline in PostgreSQL procedure varchar2

159 views Asked by At

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;
0

There are 0 answers