Add dblink function to check if a named connection exists - Mailing list pgsql-hackers

I have locked down access to all dblink_* functions, so that only
certain privileged users have access to them, and instead provide a set
of SRF functions defined as security definer functions, where I connect
to the remote server, fetch some data, disconnect from remote server,
and return the data.
One obvious disadvantage of this approach, is that I need to connect and
disconnect in every function. A possible solution to this, would be
having a function f.ex dblink_exists('connection_name') that returns
true/false depending on whether the  connection already exists. This
way, I could just check if a named connection exists, and establish a
connection if not, and wait until the end of the session to disconnect
all established connections.

I've attached a patch with a suggested implementation of such a function.


--
Tommy Gildseth
Index: dblink.c
===================================================================
RCS file: /projects/cvsroot/pgsql/contrib/dblink/dblink.c,v
retrieving revision 1.73
diff -c -c -r1.73 dblink.c
*** dblink.c    4 Apr 2008 17:02:56 -0000    1.73
--- dblink.c    28 May 2008 08:06:23 -0000
***************
*** 192,208 ****
                  freeconn = true; \
              } \
      } while (0)
-
  #define DBLINK_GET_NAMED_CONN \
      do { \
!             char *conname = text_to_cstring(PG_GETARG_TEXT_PP(0)); \
              rconn = getConnectionByName(conname); \
              if(rconn) \
                  conn = rconn->conn; \
-             else \
-                 DBLINK_CONN_NOT_AVAIL; \
      } while (0)

  #define DBLINK_INIT \
      do { \
              if (!pconn) \
--- 192,214 ----
                  freeconn = true; \
              } \
      } while (0)
  #define DBLINK_GET_NAMED_CONN \
      do { \
!             char *conname = NULL; \
!             DBLINK_GET_NAMED_CONN_IF_EXISTS; \
!             if(!rconn) \
!                 DBLINK_CONN_NOT_AVAIL; \
!     } while (0)
!
! #define DBLINK_GET_NAMED_CONN_IF_EXISTS \
!     do { \
!             conname = text_to_cstring(PG_GETARG_TEXT_PP(0)); \
              rconn = getConnectionByName(conname); \
              if(rconn) \
                  conn = rconn->conn; \
      } while (0)

+
  #define DBLINK_INIT \
      do { \
              if (!pconn) \
***************
*** 1056,1061 ****
--- 1062,1090 ----
      PG_RETURN_INT32(PQisBusy(conn));
  }

+
+ /*
+  * Checks if a given named remote connection exists
+  *
+  * Returns 1 if the connection is busy, 0 otherwise
+  * Params:
+  *    text connection_name - name of the connection to check
+  *
+  */
+ PG_FUNCTION_INFO_V1(dblink_exists);
+ Datum
+ dblink_exists(PG_FUNCTION_ARGS)
+ {
+     PGconn       *conn = NULL;
+     remoteConn *rconn = NULL;
+     char *conname = NULL;
+
+     DBLINK_INIT;
+     DBLINK_GET_NAMED_CONN_IF_EXISTS;
+
+     PG_RETURN_BOOL(conn != NULL);
+ }
+
  /*
   * Cancels a running request on a connection
   *
Index: dblink.h
===================================================================
RCS file: /projects/cvsroot/pgsql/contrib/dblink/dblink.h,v
retrieving revision 1.20
diff -c -c -r1.20 dblink.h
*** dblink.h    4 Apr 2008 16:57:21 -0000    1.20
--- dblink.h    28 May 2008 08:06:23 -0000
***************
*** 49,54 ****
--- 49,55 ----
  extern Datum dblink_get_result(PG_FUNCTION_ARGS);
  extern Datum dblink_get_connections(PG_FUNCTION_ARGS);
  extern Datum dblink_is_busy(PG_FUNCTION_ARGS);
+ extern Datum dblink_exists(PG_FUNCTION_ARGS);
  extern Datum dblink_cancel_query(PG_FUNCTION_ARGS);
  extern Datum dblink_error_message(PG_FUNCTION_ARGS);
  extern Datum dblink_exec(PG_FUNCTION_ARGS);
Index: dblink.sql.in
===================================================================
RCS file: /projects/cvsroot/pgsql/contrib/dblink/dblink.sql.in,v
retrieving revision 1.17
diff -c -c -r1.17 dblink.sql.in
*** dblink.sql.in    5 Apr 2008 02:44:42 -0000    1.17
--- dblink.sql.in    28 May 2008 08:06:23 -0000
***************
*** 178,183 ****
--- 178,188 ----
  AS 'MODULE_PATHNAME', 'dblink_is_busy'
  LANGUAGE C STRICT;

+ CREATE OR REPLACE FUNCTION dblink_exists(text)
+ RETURNS boolean
+ AS 'MODULE_PATHNAME', 'dblink_exists'
+ LANGUAGE C STRICT;
+
  CREATE OR REPLACE FUNCTION dblink_get_result(text)
  RETURNS SETOF record
  AS 'MODULE_PATHNAME', 'dblink_get_result'
Index: expected/dblink.out
===================================================================
RCS file: /projects/cvsroot/pgsql/contrib/dblink/expected/dblink.out,v
retrieving revision 1.23
diff -c -c -r1.23 dblink.out
*** expected/dblink.out    6 Apr 2008 16:54:48 -0000    1.23
--- expected/dblink.out    28 May 2008 08:06:23 -0000
***************
*** 731,736 ****
--- 731,748 ----
                0
  (1 row)

+ SELECT dblink_exists('dtest1');
+  dblink_exists
+ ---------------
+  t
+ (1 row)
+
+ SELECT dblink_exists('doesnotexist');
+  dblink_exists
+ ---------------
+  f
+ (1 row)
+
  SELECT dblink_disconnect('dtest1');
   dblink_disconnect
  -------------------
Index: sql/dblink.sql
===================================================================
RCS file: /projects/cvsroot/pgsql/contrib/dblink/sql/dblink.sql,v
retrieving revision 1.20
diff -c -c -r1.20 dblink.sql
*** sql/dblink.sql    6 Apr 2008 16:54:48 -0000    1.20
--- sql/dblink.sql    28 May 2008 08:06:23 -0000
***************
*** 351,356 ****
--- 351,359 ----

  SELECT dblink_is_busy('dtest1');

+ SELECT dblink_exists('dtest1');
+ SELECT dblink_exists('doesnotexist');
+
  SELECT dblink_disconnect('dtest1');
  SELECT dblink_disconnect('dtest2');
  SELECT dblink_disconnect('dtest3');