1

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.

Liran_T
  • 69
  • 7
  • Apparently, what I needed was to follow this question: https://stackoverflow.com/questions/13474207/sql-query-if-parameter-is-null-select-all – Liran_T Aug 29 '22 at 13:49

0 Answers0