Conditional WHERE expression in dynamic query

First, a warning. What you are doing here gives you in-stored-proc sql injection. I highly recommend you reconsider so you can properly parameterize.

Now, having said this, the obvious option is to declare a text variable and then pre-process it.

In your DECLARE block you add:

 filterstring text;

then in your body, you add:

 filterstring := array_to_string(filters, ') AND ('))
 IF filterstring = '' or filterstring is null THEN
     filterstring := 'TRUE';
 END IF;

Then you use filterstring in place of the array_to_string call in the format() call.

Note that any time you assemble a query anywhere by string interpolation you have the possibility of sql injection.

To protect against SQL injection you will need to rethink your approach a little bit. Your best option is not to use format() for your query to the extent possible. So:

execute 'SELECT 1 FROM trackings t 
      LEFT JOIN visitors v ON v.id = t.visitor_id
          WHERE v.id = $1'
   USING id;

That causes planning and filling in the value to happen on two different points. That works well in the case of a simple parameter. However it doesn't work well in the case of the dynamic filters.

Instead of passing a one-dimensional array in, you could pass a two dimensional (nx3 array) with three elements per line. These would be column name, operator, and value. You can sanitize the column name by passing it through quote_ident and the value by passing it through quote_literal but sanitizing the operators is likely to be a problem so my recommendation would be to whitelist these and throw an exception if the operator is not found. Something like:

  DECLARE 
          ...
          op TEXT;
          allowed_ops TEXT[] := ARRAY['=', '<=', '>='];
  BEGIN
       ...
       IF not(op = ANY(allowed_ops)) THEN
           RAISE EXCEPTION 'Illegal operator in function, %', op;
       END IF;
       ...
  END;

This is not going to be easy but it is doable.

Since you have your filters in the form of a jsonb array to begin with, you should use that as a function parameter instead of a text[]. For one thing, it will allow you to protect against SQL-injection.

CREATE OR REPLACE FUNCTION match_custom_filter(filters jsonb, id text)
RETURNS boolean LANGUAGE plpgsql AS $$
DECLARE
    f text;
    r boolean;
BEGIN
    IF jsonb_array_length(filters) = 0 THEN
        -- If no filters are specified then run a straight SQL query against trackings
        PERFORM * FROM trackings WHERE visitor_id = quote_literal(id);
        RETURN FOUND;
    ELSE
        -- Build the filters from the jsonb array
        SELECT string_agg(
                   -- Concatenate the parts from a single json object into a filter
                   quote_ident(j->>'field') ||    -- avoid SQL injection on column name
                   CASE j->>'type'
                       WHEN 'greater_than' THEN ' > '
                       ...
                   END ||
                   quote_literal(j->>'value'),    -- avoid SQL injection on value

                   -- Aggregate individual filters with the AND operator
                   ' AND ') INTO f
        FROM jsonb_array_elements(filters) j;

        -- Run a dynamic query with the filters
        EXECUTE format('SELECT true FROM trackings t 
                        LEFT JOIN visitors v ON v.id = t.visitor_id
                        WHERE v.id = %L AND %s LIMIT 1', id, f) INTO r;
        RETURN r;
    END IF;
END $$;

You should call this function passing in the jsonb array, like so:

SELECT v.*, array_agg(g.name) AS groups
FROM visitors v JOIN groups g ON match_custom_filter(g.group->'filter', v.id)
WHERE v.id = 'cov4pisw00000sjctfyvwq126'
GROUP BY v.id;