0

I would like to write a function to define triggers. I would like to call it this way:

CALL make_mutable('mytable', '{"mycolumn3", "mycolumn4"}');

I think I need a dynamic function for this:

CREATE PROCEDURE make_mutable(IN t text, IN cs text[])
LANGUAGE plpgsql
AS $$
BEGIN
  EXECUTE format(
    'CREATE TRIGGER %I
     BEFORE UPDATE ON %I FOR EACH ROW
     EXECUTE FUNCTION mutable_trigger(%I);',
    concat(t, '_mutable_trigger'), t, cs);
END
$$;

Now I am wondering how to format cs? The format %I takes just one argument but I need to format an array of identifiers.

1 Answer 1

0

The function quote_literal seems to work.

CREATE PROCEDURE make_mutable(IN t text, IN cs text[])
LANGUAGE plpgsql
AS $$
BEGIN
  EXECUTE format(
    'CREATE TRIGGER %I
     BEFORE UPDATE ON %I FOR EACH ROW
     EXECUTE FUNCTION mutable_trigger(%s);',
    concat(t, '_mutable_trigger'), t, quote_literal(cs));
END
$$;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.