How to safely populate pg_notify payload? Data makes its way into my application with prepared statements and stored procedures, so I'm not too worried about injection. When a new user signs up, I want to send an activation email. Presently I'm doing this with a trigger and a notification. It looks something like this:

CREATE FUNCTION on_sign_up() RETURNS trigger as $$
  DECLARE
  BEGIN
    PERFORM pg_notify('sign_ups', NEW.user_id || ',' || NEW.email || ',' || NEW.activation_code);
    RETURN new;
  END;
$$ language plpgsql;

CREATE TRIGGER on_sign_up_trigger AFTER INSERT ON user_account
  FOR EACH ROW EXECUTE PROCEDURE on_sign_up();


My problem is the notification payload. If the email contains a comma, then I can't just split the string on ','. How do I escape anything and put it into the payload? I can add my own custom CSV escaping function and invoke it, but any time I think something like that is the right answer, I'm usually missing something.

It doesn't necessarily need to be CSV, I just thought that'd be the easiest.

Note: there are other solutions - e.g. strip the "prefix" and "suffix" and then treat the rest as the email. I'm more interested for this general class of sanitization/escaping from within PostgreSQL itself.

The table I'm trying to extract is as follows:

                  Table "user_account"
     Column      |  Type   |         Modifiers          
-----------------+---------+----------------------------
 user_id         | integer | not null
 email           | citext  | not null
 password_hash   | text    | not null
 activated       | boolean | not null default false
 activation_code | uuid    | default uuid_generate_v4()
Rather than worrying about how to escape it. Just use JSON as a transport layer. Here we use row_to_json

CREATE FUNCTION on_sign_up() RETURNS trigger as $$
  DECLARE
  BEGIN
    PERFORM pg_notify('sign_ups', row_to_json(NEW)::text );
    RETURN NEW;
  END;
$$ LANGUAGE plpgsql;