Re: [GENERAL] A simple extension immitating pg_notify‏

> I understand that:> 1) you like to use postgres as a "bus" to transfer messages between connected> clients;> 2) only one database server is concerned (no redundancy at all);> 3) it is the client code (perl, php ...) that send the notification (ie,> notifications are not sent by triggers for example)> > May be you could dedicate one of your database to do this; all clients could> listen messages on this database and some client code would decode the payload> and does its job.> > Sylvain

> As to how to start writing an extension:> > https://www.postgresql.org/docs/9.5/static/extend-extensions.html> > As to how NOTIFY/LISTEN works:> > https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/commands/async.c;h=beef574076c257db0a868b39a946565185f6c53e;hb=ba37ac217791dfdf2b327c4b75e7083b6b03a2f5> > > I am not so sure another extension is needed. Would it not make more sense> just to use the dblink extension?> https://www.postgresql.org/docs/9.5/static/contrib-dblink-function.html> > Just make a table that contains the names of the other databases and> connection info.> Then the same trigger that calls the function for NOTIFY or pg_notify(text,> text) could just as easily call a function with dblink that determines which> database needs the notify and raises it there.> > -- > Adrian Klaver> adrian(dot)klaver(at)aklaver(dot)com
Thanks Adrian, that's great. I think I'll be giving the dblink / FDW a shot. Thedocumentation says that FDW is a newer more standard-compliant way to make theconnection, so I thought I give that one a try first. But then I can not find away to call a function / stored procedure from within the foreign database. Isthis possible or FDW is only for tables? I mean how to should I be sending thenotification after these steps:
CREATE DATABASE "CentralDb";CREATE USER "notify_only" WITH PASSWORD '123';GRANT ALL PRIVILEGES ON DATABASE "CentralDb" to "notify_only";CREATE FUNCTION notify_hq(text channel, text payload) RETURNS void LANGUAGE plpgsql VOLATILE COST 1 AS $$BEGIN PERFORME pg_notify(channel, payload);END;$$;
CREATE DATABASE "LocalDb";CREATE USER "local_user" WITH PASSWORD '321';GRANT ALL PRIVILEGES ON DATABASE "LocalDb" to "local_user";

CREATE EXTENSION postgres_fdw;
CREATE SERVER notify_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', dbname 'CentralDb', port '5432');
CREATE USER MAPPING FOR local_user SERVER notify_server OPTIONS (user 'notify_only', password '123');

At this point I'm stuck, because the next step would be CREATE FOREIGN TABLE butI'm not after a foreign table and a foreign function! How can I call thepg_notify / notify_hq from the LocalDb?
Regards,Mehran