I have an sql function that creates a materialized view:
CREATE FUNCTION reports_mt_views(exclude_ids int[]) RETURNS void AS
$BODY$
BEGIN
EXECUTE 'CREATE MATERIALIZED VIEW tx_materialized AS
SELECT tx.transaccion_id AS tx_transaccion_id,
...
WHERE creation_date > (current_date - interval '' ' || $2 || ' month '')
AND (account_id <> ALL (' || exclude_ids || '))'
RETURN;
END;
$BODY$ LANGUAGE plpgsql STRICT;
I have also tried as follows:
AND account_id NOT IN ' || exclude_ids || ')'
But this doesn't work when I run:
SELECT reports_mt_views(ARRAY [1,2,8,538524]);
I get this error:
ERROR: operator does not exist: text || integer[]
LINE 171: AND (account_id <> ALL (' || exclude_ids || '))'
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
There is no problem with the fix itself. I tried a FOR loop and it works. But passing it as a condition doesn't do it. What I can be doing wrong?
The solution is to concatenate the array as text so that it can be read by the where not in clause .
Thus, it can already be used as follows: