This is a multi-part message in MIME format.
--------------020002050505070802040508
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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
--------------020002050505070802040508
Content-Type: text/x-diff;
name="dblink.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="dblink.patch"
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');
--------------020002050505070802040508
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0
--
Sent via pgsql-hackers mailing list (pgsql-hackers@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--------------020002050505070802040508--
|
