PostgreSQL: Return a trigger error (raise exception) in JSON format

Question

I'm trying to find out is it possible in ANY way to exit a trigger procedure (return/raise exception, whatever) with a JSON output. I want to do something like this:

CREATE FUNCTION users_do_before_insert() RETURNS TRIGGER AS
  $$
  BEGIN
    IF (NEW.username = '' OR NEW.full_name = '' OR NEW.email = '') THEN
      RAISE EXCEPTION json_build_object('error', 'All fields are mandatory.');
    END IF;

    RETURN NEW;
  END;
  $$
LANGUAGE 'plpgsql';

CREATE TRIGGER users_bi_trigger
  BEFORE INSERT ON users
  FOR EACH ROW
  EXECUTE PROCEDURE users_do_before_insert();

So, the expected output would be:

{"error" : "All fields are mandatory."}

I would appreciate any help with this. Thanks in advance!

Pavel Stehule · Accepted Answer · 2019-03-12 03:38:18Z

2

RAISE statement requires format string or USING clause

RAISE EXCEPTION '%', json_build_object(..);
RAISE EXCEPTION USING MESSAGE=json_build_object(..);

answered Mar 12, 2019 at 3:38

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Arfeo Over a year ago

God bless ya! Thanks

Collectives™ on Stack Overflow

PostgreSQL: Return a trigger error (raise exception) in JSON format

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related