I have a java method calling postgresql procedure like so:
public void cleanupCustomFields(String keyField, String keyFieldValue, String[] fieldsToCleanup)
throws ServiceWareException {
Connection conn = null;
CallableStatement cleanupFields = null;
try {
conn = getConnection();
cleanupFields = conn.prepareCall("{call cleanup_custom_tags(?, ?, ?)}");
cleanupFields.setString(1, keyField);
cleanupFields.setString(2, keyFieldValue);
cleanupFields.setArray(3, conn.createArrayOf("varchar", fieldsToCleanup));
cleanupFields.execute();
} catch (SQLException ex) {
throw new ServiceWareException(Messages.DATABASE_GENERAL_FAILURE, ex);
} finally {
closeConnection(conn, null, cleanupFields);
}
}
and so:
CREATE OR REPLACE FUNCTION public.cleanup_custom_tags(
_tag_name character varying,
_tag_value character varying,
_tags_list character varying[])
RETURNS void
LANGUAGE 'plpgsql'
COST 100
VOLATILE SECURITY DEFINER PARALLEL UNSAFE
AS $BODY$
DECLARE
tag_id_list int[];
BEGIN
SELECT ARRAY_AGG(id) INTO tag_id_list FROM tbl_custom_tags WHERE tag_name = ANY(_tags_list);
DELETE
FROM tbl_custom_tag_values
WHERE tag_id = ANY(tag_id_list) AND
entity_id IN (SELECT s.id
FROM tbl_servers s INNER JOIN
tbl_custom_tag_values ctv ON s.id = ctv.entity_id INNER JOIN
tbl_custom_tags ct ON ct.id = ctv.tag_id
WHERE ct.tag_name = _tag_name AND ctv.value = _tag_value);
END;
$BODY$;
I wish to give the var keyFieldValue (coresponding to _tag_value in pgAdmin) a value that will match everything in ctv.value (like the regex .*). what is the syntax needed?
I could not make it work after reading the docs.
Thanks.