This patch adds the ability to retrieve async notifications using dblink, via the addition of the function dblink_get_notify.
It is written against cvs head, includes documentation and regression testing. It compiles, tests and works well. I would be interested in some feedback on the regression test. My initial thought was to test the function as thoroughly as possible. So I perform listen and notify commands within the test to be able to test all aspects of the code. Even though this works well for me, I get the feeling that this is not the correct way to do it. I can find no other testing of the listen/notify functionality in the regression tests, and I imagine this is for good reason. If someone would care to explain this, and maybe give a hint about what amount of testing is appropriate for this fairly trivial patch, it would be appreciated. Best regards, Marcus Kempe
*** ./pgsql/contrib/dblink/dblink.c.orig 2008-11-17 00:23:45.000000000 +0100 --- ./pgsql/contrib/dblink/dblink.c 2008-11-18 23:21:39.000000000 +0100 *************** *** 1597,1602 **** --- 1597,1644 ---- PG_RETURN_TEXT_P(cstring_to_text(sql)); } + + /* + * Retrieve async notifications for a connection. + * + * Returns an array of notifications, or NULL if none recieved. + * Can optionally take a named connection as parameter, but uses the unnamed connection per default. + * + */ + PG_FUNCTION_INFO_V1(dblink_get_notify); + Datum + dblink_get_notify(PG_FUNCTION_ARGS) + { + PGconn *conn = NULL; + remoteConn *rconn = NULL; + ArrayBuildState *astate = NULL; + PGnotify *notify; + + DBLINK_INIT; + if (PG_NARGS() == 1) + DBLINK_GET_NAMED_CONN; + else + conn = pconn->conn; + + PQconsumeInput(conn); + + while ((notify = PQnotifies(conn)) != NULL) + { + /* stash away current value */ + astate = accumArrayResult(astate, + PointerGetDatum(GET_TEXT(notify->relname)), + false, TEXTOID, CurrentMemoryContext); + PQfreemem(notify); + PQconsumeInput(conn); + } + + if (astate) + PG_RETURN_ARRAYTYPE_P(makeArrayResult(astate, + CurrentMemoryContext)); + else + PG_RETURN_NULL(); + } + /************************************************************* * internal functions */ *** ./pgsql/contrib/dblink/dblink.h.orig 2008-11-17 00:23:48.000000000 +0100 --- ./pgsql/contrib/dblink/dblink.h 2008-11-18 23:22:12.000000000 +0100 *************** *** 56,60 **** --- 56,61 ---- extern Datum dblink_build_sql_insert(PG_FUNCTION_ARGS); extern Datum dblink_build_sql_delete(PG_FUNCTION_ARGS); extern Datum dblink_build_sql_update(PG_FUNCTION_ARGS); + extern Datum dblink_get_notify(PG_FUNCTION_ARGS); #endif /* DBLINK_H */ *** ./pgsql/contrib/dblink/dblink.sql.in.orig 2008-11-17 00:24:28.000000000 +0100 --- ./pgsql/contrib/dblink/dblink.sql.in 2008-11-18 23:23:09.000000000 +0100 *************** *** 202,204 **** --- 202,214 ---- RETURNS text AS 'MODULE_PATHNAME', 'dblink_error_message' LANGUAGE C STRICT; + + CREATE OR REPLACE FUNCTION dblink_get_notify() + RETURNS text[] + AS '$libdir/dblink', 'dblink_get_notify' + LANGUAGE C STRICT; + + CREATE OR REPLACE FUNCTION dblink_get_notify(conname text) + RETURNS text[] + AS '$libdir/dblink', 'dblink_get_notify' + LANGUAGE C STRICT; *** ./pgsql/contrib/dblink/expected/dblink.out.orig 2008-11-17 00:24:04.000000000 +0100 --- ./pgsql/contrib/dblink/expected/dblink.out 2008-11-18 23:47:37.000000000 +0100 *************** *** 784,786 **** --- 784,827 ---- OK (1 row) + -- test asynchronous notifications + SELECT dblink_connect('dbname=contrib_regression'); + dblink_connect + ---------------- + OK + (1 row) + + --should return listen + SELECT dblink_exec('LISTEN regression'); + dblink_exec + ------------- + LISTEN + (1 row) + + --should return NOTIFY + SELECT dblink_exec('NOTIFY regression'); + dblink_exec + ------------- + NOTIFY + (1 row) + + --should return {regression} + SELECT dblink_get_notify(); + dblink_get_notify + ------------------- + {regression} + (1 row) + + --should return null + SELECT dblink_get_notify(); + dblink_get_notify + ------------------- + + (1 row) + + SELECT dblink_disconnect(); + dblink_disconnect + ------------------- + OK + (1 row) + *** ./pgsql/contrib/dblink/sql/dblink.sql.orig 2008-11-17 00:24:12.000000000 +0100 --- ./pgsql/contrib/dblink/sql/dblink.sql 2008-11-18 23:48:17.000000000 +0100 *************** *** 364,366 **** --- 364,384 ---- SELECT dblink_cancel_query('dtest1'); SELECT dblink_error_message('dtest1'); SELECT dblink_disconnect('dtest1'); + + -- test asynchronous notifications + SELECT dblink_connect('dbname=contrib_regression'); + + --should return listen + SELECT dblink_exec('LISTEN regression'); + + --should return NOTIFY + SELECT dblink_exec('NOTIFY regression'); + + --should return {regression} + SELECT dblink_get_notify(); + + --should return null + SELECT dblink_get_notify(); + + SELECT dblink_disconnect(); + *** ./pgsql/contrib/dblink/uninstall_dblink.sql.orig 2008-11-17 00:24:57.000000000 +0100 --- ./pgsql/contrib/dblink/uninstall_dblink.sql 2008-11-18 23:25:21.000000000 +0100 *************** *** 76,78 **** --- 76,83 ---- DROP FUNCTION dblink_is_busy(text); DROP FUNCTION dblink_send_query(text, text); + + DROP FUNCTION dblink_get_notify(); + + DROP FUNCTION dblink_get_notify(text); + *** ./pgsql/doc/src/sgml/dblink.sgml.orig 2008-11-17 00:23:16.000000000 +0100 --- ./pgsql/doc/src/sgml/dblink.sgml 2008-11-18 23:55:20.000000000 +0100 *************** *** 1199,1204 **** --- 1199,1278 ---- </refsect1> </refentry> + <refentry id="CONTRIB-DBLINK-GET-NOTIFY"> + <refnamediv> + <refname>dblink_get_notify</refname> + <refpurpose>retrieve async notifications on a connection</refpurpose> + </refnamediv> + + <refsynopsisdiv> + <synopsis> + dblink_get_notify() returns text[] + dblink_get_notify(text connname) returns text[] + </synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + <function>dblink_get_notify</> retrieves notifications on either + the unnamed connection, or on a named connection if specified. + To recieve notifications via dblink, <function>LISTEN</> must + first be issued, using <function>dblink_exec</>. + For details see <xref linkend="sql-listen"> and <xref linkend="sql-notify">. + </para> + + </refsect1> + + <refsect1> + <title>Arguments</title> + + <variablelist> + <varlistentry> + <term><parameter>conname</parameter></term> + <listitem> + <para> + The name of a named connection to get notifications on. + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect1> + + <refsect1> + <title>Return Value</title> + <para>Returns a text array of notification names, or NULL if none.</para> + </refsect1> + + <refsect1> + <title>Example</title> + + <programlisting> + test=# SELECT dblink_exec('LISTEN virtual'); + dblink_exec + ------------- + LISTEN + (1 row) + + test=# SELECT dblink_get_notify(); + dblink_get_notify + ------------------- + + (1 row) + + test=# NOTIFY virtual; + NOTIFY + + test=# SELECT dblink_get_notify(); + dblink_get_notify + ------------------- + {virtual} + (1 row) + </programlisting> + </refsect1> + </refentry> + <refentry id="CONTRIB-DBLINK-GET-RESULT"> <refmeta> <refentrytitle>dblink_get_result</refentrytitle>
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers