Running a Postgres SELECT within a pg_notify

Question

Can I run (and return the results of) a SELECT query within a pg_notify?

My old code worked:

CREATE OR REPLACE FUNCTION outbound_notify_fn() RETURNS trigger AS $$
BEGIN
  PERFORM pg_notify('outbound_notify', json_build_object('table', TG_TABLE_NAME, 'type', TG_OP, 'row', row_to_json(NEW))::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

When I add a SELECT query:

CREATE OR REPLACE FUNCTION outbound_notify_fn() RETURNS trigger AS $$
BEGIN
  PERFORM pg_notify('outbound_notify', json_build_object('table', TG_TABLE_NAME, 'type', TG_OP, 'row', row_to_json(NEW), 'mt_addresses', row_to_json(SELECT * FROM mt_addresses WHERE mt = NEW.mt))::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

I get an error:

ERROR:  syntax error at or near "SELECT"
LINE 3: ...w', row_to_json(NEW), 'mt_addresses', row_to_json(SELECT * F...
                                                             ^

Update: I've also tried:

CREATE OR REPLACE FUNCTION outbound_notify_fn() RETURNS trigger AS $$
BEGIN
  PERFORM pg_notify('outbound_notify', json_build_object('table', TG_TABLE_NAME, 'type', TG_OP, 'row', row_to_json(NEW), 'mt_addresses', row_to_json(
    $func$
      BEGIN
        RETURN QUERY
        SELECT *
        FROM mt_addresses
        WHERE mt = NEW.mt
      END
    $func$
  ))::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

This, at least, can be added to the database without an error.

When it fires at runtime, it causes the error:

{ error: input of anonymous composite types is not implemented
    at Connection.parseE (/redacted/node_modules/pg/lib/connection.js:572:11)
    at Connection.parseMessage (/redacted/node_modules/pg/lib/connection.js:396:17)
    at Socket.<anonymous> (/redacted/node_modules/pg/lib/connection.js:132:22)
    at emitOne (events.js:96:13)
    at Socket.emit (events.js:188:7)
    at readableAddChunk (_stream_readable.js:172:18)
    at Socket.Readable.push (_stream_readable.js:130:10)
    at TCP.onread (net.js:535:20)
  name: 'error',
  length: 466,
  severity: 'ERROR',
  code: '0A000',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: '152',
  internalQuery: 'SELECT pg_notify(\'outbound_notify\', json_build_object(\'table\', TG_TABLE_NAME, \'type\', TG_OP, \'row\', row_to_json(NEW), \'mt_addresses\', row_to_json(\n    $func$\n      BEGIN\n        RETURN QUERY\n        SELECT *\n        FROM mt_addresses\n        WHERE mt = NEW.mt\n      END\n    $func$\n  ))::text)',
  where: 'PL/pgSQL function outbound_notify_fn() line 3 at PERFORM',
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'rowtypes.c',
  line: '103',
  routine: 'record_in' }

Answer 1

To make that work, you'll have to use a whole-row reference so that you get a tuple and add an extra set of parentheses for the subquery:

row_to_json((SELECT mt_addresses FROM mt_addresses WHERE mt = NEW.mt))