0

I am looking to subscribe my code to listen to insert events produced by postgres. My frontend can detect insert queries to a specific table, but when I try to return a payload I get two types of errors.

I removed some business logic, so it might not be a 100% working example.

First error if I use notify instead of pg_notify I cannot return the new row. But when I use pg_notify I get a query error stating more than one row returned by a subquery used as an expression.

Here is an example of the trigger:

CREATE OR REPLACE FUNCTION notify_link_insert() RETURNS trigger LANGUAGE plpgsql AS $$
 begin
  select pg_notify("link_topic_manager", to_jsonb(new));
 return new;
end;
$$;

CREATE TRIGGER link_manager_trigger AFTER INSERT ON link FOR EACH ROW EXECUTE PROCEDURE notify_link_insert();

Query:

INSERT INTO link (network_id, sender_id, target_id, protocol) VALUES ((SELECT id from network WHERE name='ZZ'), 'zz44', 'zzz', 'z123');
Steven Garcia
  • 1
  • You have more than one row in the table `network` with the name `ZZ` –  Oct 13 '22 at 12:50
  • I removed the data from the table, now I get a new error: `ERROR: column "link_topic_manager" does not exist Where: PL/pgSQL function notify_link_insert() line 3 at PERFORM` – Steven Garcia Oct 13 '22 at 12:58
  • Double quotes are for identifiers per [Identifiers](https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS). The notify channel name is a function argument as a string so it needs to be single quoted: `'link_topic_manager'`. Per [Norify](https://www.postgresql.org/docs/15/sql-notify.html): *"To send a notification you can also use the function pg_notify(text, text). ..."* – Adrian Klaver Oct 13 '22 at 15:01
  • I actually figured that out a few hours ago, I did not know there was a difference between single and double quotes. Thank you all for your help! – Steven Garcia Oct 13 '22 at 15:34

1 Answers1

0

The answers are in the comments above, one I needed to use single quotes and two I had to delete the records in the database and execute the queries again.

Steven Garcia
  • 1