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;