Thread: DBLink patch
Hello,
Please apply attached patch to contrib/dblink. It adds named persistent
connections to dblink.
I have been working on this with Joe Conway and this patch is seems to work
without any problem.
Thanks Joe, for your assistance.
Bye
Shridhar
--
Anthony's Law of the Workshop: Any tool when dropped, will roll into the least
accessible corner of the workshop.Corollary: On the way to the corner, any
dropped tool will first strike your toes.
Index: contrib/dblink/README.dblink
===================================================================
RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/README.dblink,v
retrieving revision 1.7
diff -c -r1.7 README.dblink
*** contrib/dblink/README.dblink 23 Nov 2002 18:59:25 -0000 1.7
--- contrib/dblink/README.dblink 15 Jun 2003 05:17:46 -0000
***************
*** 4,11 ****
* Functions returning results from a remote database
*
* Joe Conway <mail@joeconway.com>
*
! * Copyright (c) 2001, 2002 by PostgreSQL Global Development Group
* ALL RIGHTS RESERVED;
*
* Permission to use, copy, modify, and distribute this software and its
--- 4,14 ----
* Functions returning results from a remote database
*
* Joe Conway <mail@joeconway.com>
+ * And contributors:
+ * Darko Prenosil <Darko.Prenosil@finteh.hr>
+ * Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
*
! * Copyright (c) 2001, 2002, 2003 by PostgreSQL Global Development Group
* ALL RIGHTS RESERVED;
*
* Permission to use, copy, modify, and distribute this software and its
***************
*** 27,40 ****
*
*/
! Version 0.5 (25 August, 2002):
! Major overhaul to work with new backend "table function" capability. Removed
! dblink_strtok() and dblink_replace() functions because they are now
! available as backend functions (split() and replace() respectively).
! Tested under Linux (Red Hat 7.3) and PostgreSQL 7.3devel. This version
! is no longer backwards portable to PostgreSQL 7.2.
Release Notes:
Version 0.5
- dblink now supports use directly as a table function; this is the new
preferred usage going forward
--- 30,45 ----
*
*/
! Version 0.6 (14 June, 2003):
! Completely removed previously deprecated functions. Added ability
! to create "named" persistent connections in addition to the single global
! "unnamed" persistent connection.
! Tested under Linux (Red Hat 9) and PostgreSQL 7.4devel.
Release Notes:
+ Version 0.6
+ - functions deprecated in 0.5 have been removed
+ - added ability to create "named" persistent connections
Version 0.5
- dblink now supports use directly as a table function; this is the new
preferred usage going forward
***************
*** 87,121 ****
connection
------------
dblink_connect(text) RETURNS text
! - opens a connection that will persist for duration of current
backend or until it is disconnected
dblink_disconnect() RETURNS text
! - disconnects a persistent connection
cursor
------------
dblink_open(text,text) RETURNS text
! - opens a cursor using connection already opened with dblink_connect()
! that will persist for duration of current backend or until it is
! closed
dblink_fetch(text, int) RETURNS setof record
! - fetches data from an already opened cursor
dblink_close(text) RETURNS text
! - closes a cursor
query
------------
dblink(text,text) RETURNS setof record
! - returns a set of results from remote SELECT query
! (Note: comment out in dblink.sql to use deprecated version)
dblink(text) RETURNS setof record
! - returns a set of results from remote SELECT query, using connection
! already opened with dblink_connect()
execute
------------
dblink_exec(text, text) RETURNS text
! - executes an INSERT/UPDATE/DELETE query remotely
dblink_exec(text) RETURNS text
- executes an INSERT/UPDATE/DELETE query remotely, using connection
already opened with dblink_connect()
--- 92,142 ----
connection
------------
dblink_connect(text) RETURNS text
! - opens an unnamed connection that will persist for duration of
! current backend or until it is disconnected
! dblink_connect(text,text) RETURNS text
! - opens a named connection that will persist for duration of current
backend or until it is disconnected
dblink_disconnect() RETURNS text
! - disconnects the unnamed persistent connection
! dblink_disconnect(text) RETURNS text
! - disconnects a named persistent connection
cursor
------------
dblink_open(text,text) RETURNS text
! - opens a cursor using unnamed connection already opened with
! dblink_connect() that will persist for duration of current backend
! or until it is closed
! dblink_open(text,text,text) RETURNS text
! - opens a cursor using a named connection already opened with
! dblink_connect() that will persist for duration of current backend
! or until it is closed
dblink_fetch(text, int) RETURNS setof record
! - fetches data from an already opened cursor on the unnamed connection
! dblink_fetch(text, text, int) RETURNS setof record
! - fetches data from an already opened cursor on a named connection
dblink_close(text) RETURNS text
! - closes a cursor on the unnamed connection
! dblink_close(text,text) RETURNS text
! - closes a cursor on a named connection
query
------------
dblink(text,text) RETURNS setof record
! - returns a set of results from remote SELECT query; the first argument
! is either a connection string, or the name of an already opened
! persistant connection
dblink(text) RETURNS setof record
! - returns a set of results from remote SELECT query, using the unnamed
! connection already opened with dblink_connect()
execute
------------
dblink_exec(text, text) RETURNS text
! - executes an INSERT/UPDATE/DELETE query remotely; the first argument
! is either a connection string, or the name of an already opened
! persistant connection
dblink_exec(text) RETURNS text
- executes an INSERT/UPDATE/DELETE query remotely, using connection
already opened with dblink_connect()
***************
*** 135,153 ****
dblink_build_sql_update(text,int2vector,int2,_text,_text) RETURNS text
- builds an update statement using a local tuple, replacing the
selection key field values with alternate supplied values
-
- Not installed by default
- deprecated
- ------------
- dblink(text,text) RETURNS setof int
- - *DEPRECATED* returns a resource id for results from remote query
- (Note: must uncomment in dblink.sql to use)
- dblink_tok(int,int) RETURNS text
- - *DEPRECATED* extracts and returns individual field results; used
- only in conjunction with the *DEPRECATED* form of dblink
- (Note: must uncomment in dblink.sql to use)
- dblink_last_oid(int) RETURNS oid
- - *DEPRECATED* returns the last inserted oid
Documentation:
--- 156,161 ----
Index: contrib/dblink/dblink.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/dblink.c,v
retrieving revision 1.20
diff -c -r1.20 dblink.c
*** contrib/dblink/dblink.c 28 May 2003 16:03:55 -0000 1.20
--- contrib/dblink/dblink.c 15 Jun 2003 04:17:52 -0000
***************
*** 4,11 ****
* Functions returning results from a remote database
*
* Joe Conway <mail@joeconway.com>
*
! * Copyright (c) 2001, 2002 by PostgreSQL Global Development Group
* ALL RIGHTS RESERVED;
*
* Permission to use, copy, modify, and distribute this software and its
--- 4,14 ----
* Functions returning results from a remote database
*
* Joe Conway <mail@joeconway.com>
+ * And contributors:
+ * Darko Prenosil <Darko.Prenosil@finteh.hr>
+ * Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
*
! * Copyright (c) 2001, 2002, 2003 by PostgreSQL Global Development Group
* ALL RIGHTS RESERVED;
*
* Permission to use, copy, modify, and distribute this software and its
***************
*** 27,35 ****
*
*/
#include "postgres.h"
-
#include "libpq-fe.h"
-
#include "fmgr.h"
#include "funcapi.h"
#include "access/tupdesc.h"
--- 30,36 ----
***************
*** 51,63 ****
#include "utils/array.h"
#include "utils/lsyscache.h"
#include "utils/syscache.h"
#include "dblink.h"
/*
* Internal declarations
*/
! static dblink_results *init_dblink_results(MemoryContext fn_mcxt);
static char **get_pkey_attnames(Oid relid, int16 *numatts);
static char *get_sql_insert(Oid relid, int16 *pkattnums, int16 pknumatts, char **src_pkattvals, char
**tgt_pkattvals);
static char *get_sql_delete(Oid relid, int16 *pkattnums, int16 pknumatts, char **tgt_pkattvals);
--- 52,78 ----
#include "utils/array.h"
#include "utils/lsyscache.h"
#include "utils/syscache.h"
+ #include "utils/palloc.h"
+ #include "utils/dynahash.h"
+ #include "utils/hsearch.h"
+ #include "utils/memutils.h"
#include "dblink.h"
+ typedef struct remoteConn
+ {
+ PGconn *con; /* Hold the remote connection */
+ bool remoteTrFlag; /* Indicates whether or not a transaction
+ * on remote database is in progress*/
+ } remoteConn;
+
/*
* Internal declarations
*/
! static remoteConn *getConnectionByName(const char *name);
! static HTAB *createConnHash(void);
! static bool createNewConnection(const char *name,remoteConn *con);
! static void deleteConnection(const char *name);
static char **get_pkey_attnames(Oid relid, int16 *numatts);
static char *get_sql_insert(Oid relid, int16 *pkattnums, int16 pknumatts, char **src_pkattvals, char
**tgt_pkattvals);
static char *get_sql_delete(Oid relid, int16 *pkattnums, int16 pknumatts, char **tgt_pkattvals);
***************
*** 67,83 ****
static int16 get_attnum_pk_pos(int16 *pkattnums, int16 pknumatts, int16 key);
static HeapTuple get_tuple_of_interest(Oid relid, int16 *pkattnums, int16 pknumatts, char **src_pkattvals);
static Oid get_relid_from_relname(text *relname_text);
- static dblink_results *get_res_ptr(int32 res_id_index);
- static void append_res_ptr(dblink_results * results);
- static void remove_res_ptr(dblink_results * results);
static TupleDesc pgresultGetTupleDesc(PGresult *res);
static char *generate_relation_name(Oid relid);
/* Global */
! List *res_id = NIL;
! int res_id_index = 0;
! PGconn *persistent_conn = NULL;
#define GET_TEXT(cstrp) DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(cstrp)))
#define GET_STR(textp) DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(textp)))
#define xpfree(var_) \
--- 82,113 ----
static int16 get_attnum_pk_pos(int16 *pkattnums, int16 pknumatts, int16 key);
static HeapTuple get_tuple_of_interest(Oid relid, int16 *pkattnums, int16 pknumatts, char **src_pkattvals);
static Oid get_relid_from_relname(text *relname_text);
static TupleDesc pgresultGetTupleDesc(PGresult *res);
static char *generate_relation_name(Oid relid);
/* Global */
! List *res_id = NIL;
! int res_id_index = 0;
! PGconn *persistent_conn = NULL;
! static HTAB *remoteConnHash=NULL;
!
! /*
! Following is list that holds multiple remote connections.
! Calling convention of each dblink function changes to accept
! connection name as the first parameter. The connection list is
! much like ecpg e.g. a mapping between a name and a PGconn object.
! */
!
! typedef struct remoteConnHashEnt
! {
! char name[NAMEDATALEN];
! remoteConn *rcon;
! } remoteConnHashEnt;
!
! /* initial number of connection hashes */
! #define NUMCONN 16
+ /* general utility */
#define GET_TEXT(cstrp) DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(cstrp)))
#define GET_STR(textp) DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(textp)))
#define xpfree(var_) \
***************
*** 88,93 ****
--- 118,158 ----
var_ = NULL; \
} \
} while (0)
+ #define DBLINK_RES_ERROR(p1, p2) \
+ do { \
+ msg = pstrdup(PQerrorMessage(conn)); \
+ if (res) \
+ PQclear(res); \
+ elog(ERROR, "%s: %s: %s", p1, p2, msg); \
+ } while (0)
+ #define DBLINK_CONN_NOT_AVAIL(p1) \
+ do { \
+ if(conname) \
+ elog(ERROR, "%s: connection %s not available", p1, conname); \
+ else \
+ elog(ERROR, "%s: connection not available", p1); \
+ } while (0)
+ #define DBLINK_GET_CONN(p1) \
+ do { \
+ char *conname_or_str = GET_STR(PG_GETARG_TEXT_P(0)); \
+ rcon = getConnectionByName(conname_or_str); \
+ if(rcon) \
+ { \
+ conn = rcon->con; \
+ freeconn = false; \
+ } \
+ else \
+ { \
+ connstr = conname_or_str; \
+ conn = PQconnectdb(connstr); \
+ if (PQstatus(conn) == CONNECTION_BAD) \
+ { \
+ msg = pstrdup(PQerrorMessage(conn)); \
+ PQfinish(conn); \
+ elog(ERROR, "%s: connection error: %s", p1, msg); \
+ } \
+ } \
+ } while (0)
/*
***************
*** 97,124 ****
Datum
dblink_connect(PG_FUNCTION_ARGS)
{
! char *connstr = GET_STR(PG_GETARG_TEXT_P(0));
char *msg;
- text *result_text;
MemoryContext oldcontext;
! if (persistent_conn != NULL)
! PQfinish(persistent_conn);
oldcontext = MemoryContextSwitchTo(TopMemoryContext);
! persistent_conn = PQconnectdb(connstr);
MemoryContextSwitchTo(oldcontext);
! if (PQstatus(persistent_conn) == CONNECTION_BAD)
{
! msg = pstrdup(PQerrorMessage(persistent_conn));
! PQfinish(persistent_conn);
! persistent_conn = NULL;
elog(ERROR, "dblink_connect: connection error: %s", msg);
}
! result_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum("OK")));
! PG_RETURN_TEXT_P(result_text);
}
/*
--- 162,213 ----
Datum
dblink_connect(PG_FUNCTION_ARGS)
{
! char *connstr = NULL;
! char *connname = NULL;
char *msg;
MemoryContext oldcontext;
+ PGconn *conn = NULL;
+ remoteConn *rcon = NULL;
! if(PG_NARGS()==2)
! {
! connstr = GET_STR(PG_GETARG_TEXT_P(1));
! connname = GET_STR(PG_GETARG_TEXT_P(0));
! }
! else if(PG_NARGS()==1)
! connstr = GET_STR(PG_GETARG_TEXT_P(0));
oldcontext = MemoryContextSwitchTo(TopMemoryContext);
!
! if(connname)
! rcon=(remoteConn *) palloc(sizeof(remoteConn));
! conn = PQconnectdb(connstr);
!
MemoryContextSwitchTo(oldcontext);
! if (PQstatus(conn) == CONNECTION_BAD)
{
! msg = pstrdup(PQerrorMessage(conn));
! PQfinish(conn);
! if(rcon)
! pfree(rcon);
elog(ERROR, "dblink_connect: connection error: %s", msg);
}
! if(connname)
! {
! rcon->con = conn;
! if(createNewConnection(connname, rcon) == false)
! {
! PQfinish(conn);
! pfree(rcon);
! elog(ERROR, "dblink_connect: cannot save named connection");
! }
! }
! else
! persistent_conn = conn;
!
! PG_RETURN_TEXT_P(GET_TEXT("OK"));
}
/*
***************
*** 128,142 ****
Datum
dblink_disconnect(PG_FUNCTION_ARGS)
{
! text *result_text;
! if (persistent_conn != NULL)
! PQfinish(persistent_conn);
! persistent_conn = NULL;
! result_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum("OK")));
! PG_RETURN_TEXT_P(result_text);
}
/*
--- 217,253 ----
Datum
dblink_disconnect(PG_FUNCTION_ARGS)
{
! char *str = NULL;
! remoteConn *rcon = NULL;
! PGconn *conn = NULL;
!
! if (PG_NARGS() ==1 )
! {
! str = GET_STR(PG_GETARG_TEXT_P(0));
! rcon = getConnectionByName(str);
! if (rcon)
! conn = rcon->con;
! }
! else
! conn = persistent_conn;
! if (!conn)
! {
! if (str)
! elog(ERROR,"dblink_disconnect: connection named \"%s\" not found",
! str);
! else
! elog(ERROR,"dblink_disconnect: connection not found");
! }
! PQfinish(conn);
! if (rcon)
! {
! deleteConnection(str);
! pfree(rcon);
! }
! PG_RETURN_TEXT_P(GET_TEXT("OK"));
}
/*
***************
*** 149,175 ****
char *msg;
PGresult *res = NULL;
PGconn *conn = NULL;
! text *result_text;
! char *curname = GET_STR(PG_GETARG_TEXT_P(0));
! char *sql = GET_STR(PG_GETARG_TEXT_P(1));
StringInfo str = makeStringInfo();
! if (persistent_conn != NULL)
conn = persistent_conn;
! else
! elog(ERROR, "dblink_open: no connection available");
res = PQexec(conn, "BEGIN");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
! {
! msg = pstrdup(PQerrorMessage(conn));
! PQclear(res);
!
! PQfinish(conn);
! persistent_conn = NULL;
- elog(ERROR, "dblink_open: begin error: %s", msg);
- }
PQclear(res);
appendStringInfo(str, "DECLARE %s CURSOR FOR %s", curname, sql);
--- 260,294 ----
char *msg;
PGresult *res = NULL;
PGconn *conn = NULL;
! char *curname = NULL;
! char *sql = NULL;
! char *conname = NULL;
StringInfo str = makeStringInfo();
+ remoteConn *rcon = NULL;
! if(PG_NARGS() == 2)
! {
! curname = GET_STR(PG_GETARG_TEXT_P(0));
! sql = GET_STR(PG_GETARG_TEXT_P(1));
conn = persistent_conn;
! }
! else if(PG_NARGS() == 3)
! {
! conname = GET_STR(PG_GETARG_TEXT_P(0));
! curname = GET_STR(PG_GETARG_TEXT_P(1));
! sql = GET_STR(PG_GETARG_TEXT_P(2));
! rcon = getConnectionByName(conname);
! if (rcon)
! conn = rcon->con;
! }
!
! if (!conn)
! DBLINK_CONN_NOT_AVAIL("dblink_open");
res = PQexec(conn, "BEGIN");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
! DBLINK_RES_ERROR("dblink_open", "begin error");
PQclear(res);
appendStringInfo(str, "DECLARE %s CURSOR FOR %s", curname, sql);
***************
*** 177,195 ****
if (!res ||
(PQresultStatus(res) != PGRES_COMMAND_OK &&
PQresultStatus(res) != PGRES_TUPLES_OK))
! {
! msg = pstrdup(PQerrorMessage(conn));
!
! PQclear(res);
! PQfinish(conn);
! persistent_conn = NULL;
!
! elog(ERROR, "dblink: sql error: %s", msg);
! }
! result_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum("OK")));
! PG_RETURN_TEXT_P(result_text);
}
/*
--- 296,306 ----
if (!res ||
(PQresultStatus(res) != PGRES_COMMAND_OK &&
PQresultStatus(res) != PGRES_TUPLES_OK))
! DBLINK_RES_ERROR("dblink_open", "sql error");
! PQclear(res);
! PG_RETURN_TEXT_P(GET_TEXT("OK"));
}
/*
***************
*** 201,249 ****
{
PGconn *conn = NULL;
PGresult *res = NULL;
! char *curname = GET_STR(PG_GETARG_TEXT_P(0));
StringInfo str = makeStringInfo();
- text *result_text;
char *msg;
! if (persistent_conn != NULL)
conn = persistent_conn;
! else
! elog(ERROR, "dblink_close: no connection available");
appendStringInfo(str, "CLOSE %s", curname);
/* close the cursor */
res = PQexec(conn, str->data);
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
! {
! msg = pstrdup(PQerrorMessage(conn));
! PQclear(res);
!
! PQfinish(persistent_conn);
! persistent_conn = NULL;
!
! elog(ERROR, "dblink_close: sql error: %s", msg);
! }
PQclear(res);
/* commit the transaction */
res = PQexec(conn, "COMMIT");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
! {
! msg = pstrdup(PQerrorMessage(conn));
! PQclear(res);
!
! PQfinish(persistent_conn);
! persistent_conn = NULL;
- elog(ERROR, "dblink_close: commit error: %s", msg);
- }
PQclear(res);
! result_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum("OK")));
! PG_RETURN_TEXT_P(result_text);
}
/*
--- 312,357 ----
{
PGconn *conn = NULL;
PGresult *res = NULL;
! char *curname = NULL;
! char *conname = NULL;
StringInfo str = makeStringInfo();
char *msg;
+ remoteConn *rcon = NULL;
! if (PG_NARGS() == 1)
! {
! curname = GET_STR(PG_GETARG_TEXT_P(0));
conn = persistent_conn;
! }
! else if (PG_NARGS()==2)
! {
! conname = GET_STR(PG_GETARG_TEXT_P(0));
! curname = GET_STR(PG_GETARG_TEXT_P(1));
! rcon = getConnectionByName(conname);
! if(rcon)
! conn = rcon->con;
! }
!
! if (!conn)
! DBLINK_CONN_NOT_AVAIL("dblink_close");
appendStringInfo(str, "CLOSE %s", curname);
/* close the cursor */
res = PQexec(conn, str->data);
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
! DBLINK_RES_ERROR("dblink_close", "sql error");
PQclear(res);
/* commit the transaction */
res = PQexec(conn, "COMMIT");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
! DBLINK_RES_ERROR("dblink_close", "commit error");
PQclear(res);
! PG_RETURN_TEXT_P(GET_TEXT("OK"));
}
/*
***************
*** 262,267 ****
--- 370,377 ----
char *msg;
PGresult *res = NULL;
MemoryContext oldcontext;
+ char *conname = NULL;
+ remoteConn *rcon=NULL;
/* stuff done only on the first call of the function */
if (SRF_IS_FIRSTCALL())
***************
*** 271,278 ****
Oid funcid = fcinfo->flinfo->fn_oid;
PGconn *conn = NULL;
StringInfo str = makeStringInfo();
! char *curname = GET_STR(PG_GETARG_TEXT_P(0));
! int howmany = PG_GETARG_INT32(1);
/* create a function context for cross-call persistence */
funcctx = SRF_FIRSTCALL_INIT();
--- 381,408 ----
Oid funcid = fcinfo->flinfo->fn_oid;
PGconn *conn = NULL;
StringInfo str = makeStringInfo();
! char *curname = NULL;
! int howmany = 0;
!
! if (PG_NARGS() == 3)
! {
! conname = GET_STR(PG_GETARG_TEXT_P(0));
! curname = GET_STR(PG_GETARG_TEXT_P(1));
! howmany = PG_GETARG_INT32(2);
!
! rcon = getConnectionByName(conname);
! if(rcon)
! conn = rcon->con;
! }
! else if (PG_NARGS() == 2)
! {
! curname = GET_STR(PG_GETARG_TEXT_P(0));
! howmany = PG_GETARG_INT32(1);
! conn = persistent_conn;
! }
!
! if(!conn)
! DBLINK_CONN_NOT_AVAIL("dblink_fetch");
/* create a function context for cross-call persistence */
funcctx = SRF_FIRSTCALL_INIT();
***************
*** 283,293 ****
*/
oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
- if (persistent_conn != NULL)
- conn = persistent_conn;
- else
- elog(ERROR, "dblink_fetch: no connection available");
-
appendStringInfo(str, "FETCH %d FROM %s", howmany, curname);
res = PQexec(conn, str->data);
--- 413,418 ----
***************
*** 295,313 ****
(PQresultStatus(res) != PGRES_COMMAND_OK &&
PQresultStatus(res) != PGRES_TUPLES_OK))
{
! msg = pstrdup(PQerrorMessage(conn));
! PQclear(res);
!
! PQfinish(persistent_conn);
! persistent_conn = NULL;
!
! elog(ERROR, "dblink_fetch: sql error: %s", msg);
}
else if (PQresultStatus(res) == PGRES_COMMAND_OK)
{
/* cursor does not exist - closed already or bad name */
PQclear(res);
! elog(ERROR, "dblink_fetch: cursor %s does not exist", curname);
}
funcctx->max_calls = PQntuples(res);
--- 420,432 ----
(PQresultStatus(res) != PGRES_COMMAND_OK &&
PQresultStatus(res) != PGRES_TUPLES_OK))
{
! DBLINK_RES_ERROR("dblink_fetch", "sql error");
}
else if (PQresultStatus(res) == PGRES_COMMAND_OK)
{
/* cursor does not exist - closed already or bad name */
PQclear(res);
! elog(ERROR, "dblink_fetch: cursor not found: %s", curname);
}
funcctx->max_calls = PQntuples(res);
***************
*** 382,389 ****
SRF_RETURN_NEXT(funcctx, result);
}
else
- /* do when there is no more left */
{
PQclear(res);
SRF_RETURN_DONE(funcctx);
}
--- 501,508 ----
SRF_RETURN_NEXT(funcctx, result);
}
else
{
+ /* do when there is no more left */
PQclear(res);
SRF_RETURN_DONE(funcctx);
}
***************
*** 407,412 ****
--- 526,532 ----
bool is_sql_cmd = false;
char *sql_cmd_status = NULL;
MemoryContext oldcontext;
+ bool freeconn = true;
/* stuff done only on the first call of the function */
if (SRF_IS_FIRSTCALL())
***************
*** 417,422 ****
--- 537,544 ----
PGconn *conn = NULL;
char *connstr = NULL;
char *sql = NULL;
+ char *conname = NULL;
+ remoteConn *rcon=NULL;
/* create a function context for cross-call persistence */
funcctx = SRF_FIRSTCALL_INIT();
***************
*** 427,496 ****
*/
oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
! if (fcinfo->nargs == 2)
{
! connstr = GET_STR(PG_GETARG_TEXT_P(0));
sql = GET_STR(PG_GETARG_TEXT_P(1));
-
- conn = PQconnectdb(connstr);
- if (PQstatus(conn) == CONNECTION_BAD)
- {
- msg = pstrdup(PQerrorMessage(conn));
- PQfinish(conn);
- elog(ERROR, "dblink: connection error: %s", msg);
- }
}
! else if (fcinfo->nargs == 1)
{
sql = GET_STR(PG_GETARG_TEXT_P(0));
-
- if (persistent_conn != NULL)
- conn = persistent_conn;
- else
- elog(ERROR, "dblink: no connection available");
}
else
elog(ERROR, "dblink: wrong number of arguments");
res = PQexec(conn, sql);
if (!res || (PQresultStatus(res) != PGRES_COMMAND_OK && PQresultStatus(res) != PGRES_TUPLES_OK))
{
! msg = pstrdup(PQerrorMessage(conn));
! PQclear(res);
! PQfinish(conn);
! if (fcinfo->nargs == 1)
! persistent_conn = NULL;
! elog(ERROR, "dblink: sql error: %s", msg);
}
else
! {
! if (PQresultStatus(res) == PGRES_COMMAND_OK)
! {
! is_sql_cmd = true;
! /* need a tuple descriptor representing one TEXT column */
! tupdesc = CreateTemplateTupleDesc(1, false);
! TupleDescInitEntry(tupdesc, (AttrNumber) 1, "status",
! TEXTOID, -1, 0, false);
!
! /*
! * and save a copy of the command status string to return
! * as our result tuple
! */
! sql_cmd_status = PQcmdStatus(res);
! funcctx->max_calls = 1;
! }
! else
! funcctx->max_calls = PQntuples(res);
!
! /* got results, keep track of them */
! funcctx->user_fctx = res;
! /* if needed, close the connection to the database and cleanup */
! if (fcinfo->nargs == 2)
! PQfinish(conn);
! }
/* fast track when no results */
if (funcctx->max_calls < 1)
--- 549,599 ----
*/
oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
! if (PG_NARGS() == 2)
{
! DBLINK_GET_CONN("dblink");
sql = GET_STR(PG_GETARG_TEXT_P(1));
}
! else if (PG_NARGS() == 1)
{
+ conn = persistent_conn;
sql = GET_STR(PG_GETARG_TEXT_P(0));
}
else
elog(ERROR, "dblink: wrong number of arguments");
+ if(!conn)
+ DBLINK_CONN_NOT_AVAIL("dblink_record");
+
res = PQexec(conn, sql);
if (!res || (PQresultStatus(res) != PGRES_COMMAND_OK && PQresultStatus(res) != PGRES_TUPLES_OK))
+ DBLINK_RES_ERROR("dblink", "sql error");
+
+ if (PQresultStatus(res) == PGRES_COMMAND_OK)
{
! is_sql_cmd = true;
!
! /* need a tuple descriptor representing one TEXT column */
! tupdesc = CreateTemplateTupleDesc(1, false);
! TupleDescInitEntry(tupdesc, (AttrNumber) 1, "status",
! TEXTOID, -1, 0, false);
! /*
! * and save a copy of the command status string to return
! * as our result tuple
! */
! sql_cmd_status = PQcmdStatus(res);
! funcctx->max_calls = 1;
}
else
! funcctx->max_calls = PQntuples(res);
! /* got results, keep track of them */
! funcctx->user_fctx = res;
! /* if needed, close the connection to the database and cleanup */
! if (freeconn && PG_NARGS() == 2)
! PQfinish(conn);
/* fast track when no results */
if (funcctx->max_calls < 1)
***************
*** 571,578 ****
SRF_RETURN_NEXT(funcctx, result);
}
else
- /* do when there is no more left */
{
PQclear(res);
SRF_RETURN_DONE(funcctx);
}
--- 674,681 ----
SRF_RETURN_NEXT(funcctx, result);
}
else
{
+ /* do when there is no more left */
PQclear(res);
SRF_RETURN_DONE(funcctx);
}
***************
*** 587,858 ****
{
char *msg;
PGresult *res = NULL;
! char *sql_cmd_status = NULL;
TupleDesc tupdesc = NULL;
- text *result_text;
PGconn *conn = NULL;
char *connstr = NULL;
char *sql = NULL;
! if (fcinfo->nargs == 2)
{
! connstr = GET_STR(PG_GETARG_TEXT_P(0));
sql = GET_STR(PG_GETARG_TEXT_P(1));
-
- conn = PQconnectdb(connstr);
- if (PQstatus(conn) == CONNECTION_BAD)
- {
- msg = pstrdup(PQerrorMessage(conn));
- PQfinish(conn);
- elog(ERROR, "dblink_exec: connection error: %s", msg);
- }
}
! else if (fcinfo->nargs == 1)
{
sql = GET_STR(PG_GETARG_TEXT_P(0));
-
- if (persistent_conn != NULL)
- conn = persistent_conn;
- else
- elog(ERROR, "dblink_exec: no connection available");
}
else
elog(ERROR, "dblink_exec: wrong number of arguments");
res = PQexec(conn, sql);
! if (!res || (PQresultStatus(res) != PGRES_COMMAND_OK && PQresultStatus(res) != PGRES_TUPLES_OK))
{
! msg = pstrdup(PQerrorMessage(conn));
! PQclear(res);
! PQfinish(conn);
! if (fcinfo->nargs == 1)
! persistent_conn = NULL;
! elog(ERROR, "dblink_exec: sql error: %s", msg);
}
else
! {
! if (PQresultStatus(res) == PGRES_COMMAND_OK)
! {
! /* need a tuple descriptor representing one TEXT column */
! tupdesc = CreateTemplateTupleDesc(1, false);
! TupleDescInitEntry(tupdesc, (AttrNumber) 1, "status",
! TEXTOID, -1, 0, false);
- /*
- * and save a copy of the command status string to return as
- * our result tuple
- */
- sql_cmd_status = PQcmdStatus(res);
- }
- else
- elog(ERROR, "dblink_exec: queries returning results not allowed");
- }
PQclear(res);
/* if needed, close the connection to the database and cleanup */
! if (fcinfo->nargs == 2)
PQfinish(conn);
! result_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(sql_cmd_status)));
! PG_RETURN_TEXT_P(result_text);
}
- /*
- * Note: this original version of dblink is DEPRECATED;
- * it *will* be removed in favor of the new version on next release
- */
- PG_FUNCTION_INFO_V1(dblink);
- Datum
- dblink(PG_FUNCTION_ARGS)
- {
- PGconn *conn = NULL;
- PGresult *res = NULL;
- dblink_results *results;
- char *optstr;
- char *sqlstatement;
- char *execstatement;
- char *msg;
- int ntuples = 0;
- ReturnSetInfo *rsi;
-
- if (fcinfo->resultinfo == NULL || !IsA(fcinfo->resultinfo, ReturnSetInfo))
- elog(ERROR, "dblink: function called in context that does not accept a set result");
-
- optstr = DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(PG_GETARG_TEXT_P(0))));
- sqlstatement = DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(PG_GETARG_TEXT_P(1))));
-
- if (fcinfo->flinfo->fn_extra == NULL)
- {
-
- conn = PQconnectdb(optstr);
- if (PQstatus(conn) == CONNECTION_BAD)
- {
- msg = pstrdup(PQerrorMessage(conn));
- PQfinish(conn);
- elog(ERROR, "dblink: connection error: %s", msg);
- }
-
- execstatement = (char *) palloc(strlen(sqlstatement) + 1);
- if (execstatement != NULL)
- {
- strcpy(execstatement, sqlstatement);
- strcat(execstatement, "\0");
- }
- else
- elog(ERROR, "dblink: insufficient memory");
-
- res = PQexec(conn, execstatement);
- if (!res || (PQresultStatus(res) != PGRES_COMMAND_OK && PQresultStatus(res) != PGRES_TUPLES_OK))
- {
- msg = pstrdup(PQerrorMessage(conn));
- PQclear(res);
- PQfinish(conn);
- elog(ERROR, "dblink: sql error: %s", msg);
- }
- else
- {
- /*
- * got results, start fetching them
- */
- ntuples = PQntuples(res);
-
- /*
- * increment resource index
- */
- res_id_index++;
-
- results = init_dblink_results(fcinfo->flinfo->fn_mcxt);
- results->tup_num = 0;
- results->res_id_index = res_id_index;
- results->res = res;
-
- /*
- * Append node to res_id to hold pointer to results. Needed by
- * dblink_tok to access the data
- */
- append_res_ptr(results);
-
- /*
- * save pointer to results for the next function manager call
- */
- fcinfo->flinfo->fn_extra = (void *) results;
-
- /* close the connection to the database and cleanup */
- PQfinish(conn);
-
- rsi = (ReturnSetInfo *) fcinfo->resultinfo;
- rsi->isDone = ExprMultipleResult;
-
- PG_RETURN_INT32(res_id_index);
- }
- }
- else
- {
- /*
- * check for more results
- */
- results = fcinfo->flinfo->fn_extra;
-
- results->tup_num++;
- res_id_index = results->res_id_index;
- ntuples = PQntuples(results->res);
-
- if (results->tup_num < ntuples)
- {
- /*
- * fetch them if available
- */
-
- rsi = (ReturnSetInfo *) fcinfo->resultinfo;
- rsi->isDone = ExprMultipleResult;
-
- PG_RETURN_INT32(res_id_index);
- }
- else
- {
- /*
- * or if no more, clean things up
- */
- results = fcinfo->flinfo->fn_extra;
-
- remove_res_ptr(results);
- PQclear(results->res);
- pfree(results);
- fcinfo->flinfo->fn_extra = NULL;
-
- rsi = (ReturnSetInfo *) fcinfo->resultinfo;
- rsi->isDone = ExprEndResult;
-
- PG_RETURN_NULL();
- }
- }
- PG_RETURN_NULL();
- }
-
- /*
- * Note: dblink_tok is DEPRECATED;
- * it *will* be removed in favor of the new version on next release
- *
- * dblink_tok
- * parse dblink output string
- * return fldnum item (0 based)
- * based on provided field separator
- */
- PG_FUNCTION_INFO_V1(dblink_tok);
- Datum
- dblink_tok(PG_FUNCTION_ARGS)
- {
- dblink_results *results;
- int fldnum;
- text *result_text;
- char *result;
- int nfields = 0;
- int text_len = 0;
-
- results = get_res_ptr(PG_GETARG_INT32(0));
- if (results == NULL)
- {
- if (res_id != NIL)
- {
- freeList(res_id);
- res_id = NIL;
- res_id_index = 0;
- }
-
- elog(ERROR, "dblink_tok: function called with invalid resource id");
- }
-
- fldnum = PG_GETARG_INT32(1);
- if (fldnum < 0)
- elog(ERROR, "dblink_tok: field number < 0 not permitted");
-
- nfields = PQnfields(results->res);
- if (fldnum > (nfields - 1))
- elog(ERROR, "dblink_tok: field number %d does not exist", fldnum);
-
- if (PQgetisnull(results->res, results->tup_num, fldnum) == 1)
- PG_RETURN_NULL();
- else
- {
- text_len = PQgetlength(results->res, results->tup_num, fldnum);
-
- result = (char *) palloc(text_len + 1);
-
- if (result != NULL)
- {
- strcpy(result, PQgetvalue(results->res, results->tup_num, fldnum));
- strcat(result, "\0");
- }
- else
- elog(ERROR, "dblink: insufficient memory");
-
- result_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(result)));
-
- PG_RETURN_TEXT_P(result_text);
- }
- }
/*
* dblink_get_pkey
--- 690,751 ----
{
char *msg;
PGresult *res = NULL;
! text *sql_cmd_status = NULL;
TupleDesc tupdesc = NULL;
PGconn *conn = NULL;
char *connstr = NULL;
char *sql = NULL;
+ char *conname = NULL;
+ remoteConn *rcon=NULL;
+ bool freeconn = true;
! if (PG_NARGS() == 2)
{
! DBLINK_GET_CONN("dblink_exec");
sql = GET_STR(PG_GETARG_TEXT_P(1));
}
! else if (PG_NARGS() == 1)
{
+ conn = persistent_conn;
sql = GET_STR(PG_GETARG_TEXT_P(0));
}
else
elog(ERROR, "dblink_exec: wrong number of arguments");
+ if(!conn)
+ DBLINK_CONN_NOT_AVAIL("dblink_exec");
res = PQexec(conn, sql);
! if (!res ||
! (PQresultStatus(res) != PGRES_COMMAND_OK &&
! PQresultStatus(res) != PGRES_TUPLES_OK))
! DBLINK_RES_ERROR("dblink_exec", "sql error");
!
! if (PQresultStatus(res) == PGRES_COMMAND_OK)
{
! /* need a tuple descriptor representing one TEXT column */
! tupdesc = CreateTemplateTupleDesc(1, false);
! TupleDescInitEntry(tupdesc, (AttrNumber) 1, "status",
! TEXTOID, -1, 0, false);
! /*
! * and save a copy of the command status string to return as
! * our result tuple
! */
! sql_cmd_status = GET_TEXT(PQcmdStatus(res));
}
else
! elog(ERROR, "dblink_exec: queries returning results not allowed");
PQclear(res);
/* if needed, close the connection to the database and cleanup */
! if (freeconn && fcinfo->nargs == 2)
PQfinish(conn);
! PG_RETURN_TEXT_P(sql_cmd_status);
}
/*
* dblink_get_pkey
***************
*** 927,933 ****
funcctx->user_fctx = results;
}
else
! /* fast track when no results */
SRF_RETURN_DONE(funcctx);
MemoryContextSwitchTo(oldcontext);
--- 820,826 ----
funcctx->user_fctx = results;
}
else
! /* fast track when no results */
SRF_RETURN_DONE(funcctx);
MemoryContextSwitchTo(oldcontext);
***************
*** 969,1005 ****
SRF_RETURN_NEXT(funcctx, result);
}
else
- /* do when there is no more left */
- SRF_RETURN_DONE(funcctx);
- }
-
- /*
- * Note: dblink_last_oid is DEPRECATED;
- * it *will* be removed on next release
- *
- * dblink_last_oid
- * return last inserted oid
- */
- PG_FUNCTION_INFO_V1(dblink_last_oid);
- Datum
- dblink_last_oid(PG_FUNCTION_ARGS)
- {
- dblink_results *results;
-
- results = get_res_ptr(PG_GETARG_INT32(0));
- if (results == NULL)
{
! if (res_id != NIL)
! {
! freeList(res_id);
! res_id = NIL;
! res_id_index = 0;
! }
!
! elog(ERROR, "dblink_tok: function called with invalid resource id");
}
-
- PG_RETURN_OID(PQoidValue(results->res));
}
--- 862,871 ----
SRF_RETURN_NEXT(funcctx, result);
}
else
{
! /* do when there is no more left */
! SRF_RETURN_DONE(funcctx);
}
}
***************
*** 1047,1053 ****
int i;
char *ptr;
char *sql;
- text *sql_text;
int16 typlen;
bool typbyval;
char typalign;
--- 913,918 ----
***************
*** 1143,1156 ****
sql = get_sql_insert(relid, pkattnums, pknumatts, src_pkattvals, tgt_pkattvals);
/*
- * Make it into TEXT for return to the client
- */
- sql_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(sql)));
-
- /*
* And send it
*/
! PG_RETURN_TEXT_P(sql_text);
}
--- 1008,1016 ----
sql = get_sql_insert(relid, pkattnums, pknumatts, src_pkattvals, tgt_pkattvals);
/*
* And send it
*/
! PG_RETURN_TEXT_P(GET_TEXT(sql));
}
***************
*** 1186,1192 ****
int i;
char *ptr;
char *sql;
- text *sql_text;
int16 typlen;
bool typbyval;
char typalign;
--- 1046,1051 ----
***************
*** 1251,1264 ****
sql = get_sql_delete(relid, pkattnums, pknumatts, tgt_pkattvals);
/*
- * Make it into TEXT for return to the client
- */
- sql_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(sql)));
-
- /*
* And send it
*/
! PG_RETURN_TEXT_P(sql_text);
}
--- 1110,1118 ----
sql = get_sql_delete(relid, pkattnums, pknumatts, tgt_pkattvals);
/*
* And send it
*/
! PG_RETURN_TEXT_P(GET_TEXT(sql));
}
***************
*** 1303,1309 ****
int i;
char *ptr;
char *sql;
- text *sql_text;
int16 typlen;
bool typbyval;
char typalign;
--- 1157,1162 ----
***************
*** 1399,1412 ****
sql = get_sql_update(relid, pkattnums, pknumatts, src_pkattvals, tgt_pkattvals);
/*
- * Make it into TEXT for return to the client
- */
- sql_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(sql)));
-
- /*
* And send it
*/
! PG_RETURN_TEXT_P(sql_text);
}
/*
--- 1252,1260 ----
sql = get_sql_update(relid, pkattnums, pknumatts, src_pkattvals, tgt_pkattvals);
/*
* And send it
*/
! PG_RETURN_TEXT_P(GET_TEXT(sql));
}
/*
***************
*** 1419,1428 ****
Datum
dblink_current_query(PG_FUNCTION_ARGS)
{
! text *result_text;
!
! result_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(debug_query_string)));
! PG_RETURN_TEXT_P(result_text);
}
--- 1267,1273 ----
Datum
dblink_current_query(PG_FUNCTION_ARGS)
{
! PG_RETURN_TEXT_P(GET_TEXT(debug_query_string));
}
***************
*** 1432,1460 ****
/*
- * init_dblink_results
- * - create an empty dblink_results data structure
- */
- static dblink_results *
- init_dblink_results(MemoryContext fn_mcxt)
- {
- MemoryContext oldcontext;
- dblink_results *retval;
-
- oldcontext = MemoryContextSwitchTo(fn_mcxt);
-
- retval = (dblink_results *) palloc0(sizeof(dblink_results));
-
- retval->tup_num = -1;
- retval->res_id_index = -1;
- retval->res = NULL;
-
- MemoryContextSwitchTo(oldcontext);
-
- return retval;
- }
-
- /*
* get_pkey_attnames
*
* Get the primary key attnames for the given relation.
--- 1277,1282 ----
***************
*** 1492,1498 ****
/* we're only interested if it is the primary key */
if (index->indisprimary == TRUE)
{
! *numatts = index->indnatts;
if (*numatts > 0)
{
result = (char **) palloc(*numatts * sizeof(char *));
--- 1314,1323 ----
/* we're only interested if it is the primary key */
if (index->indisprimary == TRUE)
{
! i = 0;
! while (index->indkey[i++] != 0)
! (*numatts)++;
!
if (*numatts > 0)
{
result = (char **) palloc(*numatts * sizeof(char *));
***************
*** 1911,1962 ****
return relid;
}
- static dblink_results *
- get_res_ptr(int32 res_id_index)
- {
- List *ptr;
-
- /*
- * short circuit empty list
- */
- if (res_id == NIL)
- return NULL;
-
- /*
- * OK, should be good to go
- */
- foreach(ptr, res_id)
- {
- dblink_results *this_res_id = (dblink_results *) lfirst(ptr);
-
- if (this_res_id->res_id_index == res_id_index)
- return this_res_id;
- }
- return NULL;
- }
-
- /*
- * Add node to global List res_id
- */
- static void
- append_res_ptr(dblink_results * results)
- {
- res_id = lappend(res_id, results);
- }
-
- /*
- * Remove node from global List
- * using res_id_index
- */
- static void
- remove_res_ptr(dblink_results * results)
- {
- res_id = lremove(results, res_id);
-
- if (res_id == NIL)
- res_id_index = 0;
- }
-
static TupleDesc
pgresultGetTupleDesc(PGresult *res)
{
--- 1736,1741 ----
***************
*** 2042,2045 ****
--- 1821,1912 ----
ReleaseSysCache(tp);
return result;
+ }
+
+
+ static remoteConn *
+ getConnectionByName(const char *name)
+ {
+ remoteConnHashEnt *hentry;
+ char key[NAMEDATALEN];
+
+ if(!remoteConnHash)
+ remoteConnHash=createConnHash();
+
+ MemSet(key, 0, NAMEDATALEN);
+ snprintf(key, NAMEDATALEN - 1, "%s", name);
+ hentry = (remoteConnHashEnt*) hash_search(remoteConnHash,
+ key, HASH_FIND, NULL);
+
+ if(hentry)
+ return(hentry->rcon);
+
+ return(NULL);
+ }
+
+ static HTAB *
+ createConnHash(void)
+ {
+ HASHCTL ctl;
+ HTAB *ptr;
+
+ ctl.keysize = NAMEDATALEN;
+ ctl.entrysize = sizeof(remoteConnHashEnt);
+
+ ptr=hash_create("Remote Con hash", NUMCONN, &ctl, HASH_ELEM);
+
+ if(!ptr)
+ elog(ERROR,"Can not create connections hash table. Out of memory");
+
+ return(ptr);
+ }
+
+ static bool
+ createNewConnection(const char *name, remoteConn *con)
+ {
+ remoteConnHashEnt *hentry;
+ bool found;
+ char key[NAMEDATALEN];
+
+ if(!remoteConnHash)
+ remoteConnHash=createConnHash();
+
+ MemSet(key, 0, NAMEDATALEN);
+ snprintf(key, NAMEDATALEN - 1, "%s", name);
+ hentry = (remoteConnHashEnt *) hash_search(remoteConnHash, key,
+ HASH_ENTER, &found);
+
+ if(!hentry)
+ elog(ERROR, "failed to create connection");
+
+ if(found)
+ {
+ elog(NOTICE, "cannot use a connection name more than once");
+ return false;
+ }
+
+ hentry->rcon = con;
+ strncpy(hentry->name, name, NAMEDATALEN - 1);
+
+ return true;
+ }
+
+ static void
+ deleteConnection(const char *name)
+ {
+ remoteConnHashEnt *hentry;
+ bool found;
+ char key[NAMEDATALEN];
+
+ if(!remoteConnHash)
+ remoteConnHash=createConnHash();
+
+ MemSet(key, 0, NAMEDATALEN);
+ snprintf(key, NAMEDATALEN - 1, "%s", name);
+
+ hentry = (remoteConnHashEnt *) hash_search(remoteConnHash,
+ key, HASH_REMOVE, &found);
+
+ if(!hentry)
+ elog(WARNING,"Trying to delete a connection that does not exist");
}
Index: contrib/dblink/dblink.h
===================================================================
RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/dblink.h,v
retrieving revision 1.9
diff -c -r1.9 dblink.h
*** contrib/dblink/dblink.h 21 Oct 2002 18:57:34 -0000 1.9
--- contrib/dblink/dblink.h 15 Jun 2003 04:18:08 -0000
***************
*** 4,11 ****
* Functions returning results from a remote database
*
* Joe Conway <mail@joeconway.com>
*
! * Copyright (c) 2001, 2002 by PostgreSQL Global Development Group
* ALL RIGHTS RESERVED;
*
* Permission to use, copy, modify, and distribute this software and its
--- 4,14 ----
* Functions returning results from a remote database
*
* Joe Conway <mail@joeconway.com>
+ * And contributors:
+ * Darko Prenosil <Darko.Prenosil@finteh.hr>
+ * Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
*
! * Copyright (c) 2001, 2002, 2003 by PostgreSQL Global Development Group
* ALL RIGHTS RESERVED;
*
* Permission to use, copy, modify, and distribute this software and its
***************
*** 31,65 ****
#define DBLINK_H
/*
- * This struct holds the results of the remote query.
- * Use fn_extra to hold a pointer to it across calls
- */
- typedef struct
- {
- /*
- * last tuple number accessed
- */
- int tup_num;
-
- /*
- * resource index number for this context
- */
- int res_id_index;
-
- /*
- * the actual query results
- */
- PGresult *res;
- } dblink_results;
-
- /*
* External declarations
*/
- /* deprecated */
- extern Datum dblink(PG_FUNCTION_ARGS);
- extern Datum dblink_tok(PG_FUNCTION_ARGS);
-
- /* supported */
extern Datum dblink_connect(PG_FUNCTION_ARGS);
extern Datum dblink_disconnect(PG_FUNCTION_ARGS);
extern Datum dblink_open(PG_FUNCTION_ARGS);
--- 34,41 ----
***************
*** 68,74 ****
extern Datum dblink_record(PG_FUNCTION_ARGS);
extern Datum dblink_exec(PG_FUNCTION_ARGS);
extern Datum dblink_get_pkey(PG_FUNCTION_ARGS);
- extern Datum dblink_last_oid(PG_FUNCTION_ARGS);
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);
--- 44,49 ----
Index: contrib/dblink/dblink.sql.in
===================================================================
RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/dblink.sql.in,v
retrieving revision 1.7
diff -c -r1.7 dblink.sql.in
*** contrib/dblink/dblink.sql.in 18 Oct 2002 18:41:19 -0000 1.7
--- contrib/dblink/dblink.sql.in 15 Jun 2003 03:59:54 -0000
***************
*** 1,50 ****
- --
- -- Uncomment the following commented lines to use original DEPRECATED functions
- --
- --CREATE OR REPLACE FUNCTION dblink (text,text)
- --RETURNS setof int
- --AS 'MODULE_PATHNAME','dblink'
- --LANGUAGE 'C' WITH (isstrict);
- --CREATE OR REPLACE FUNCTION dblink_tok (int,int)
- --RETURNS text
- --AS 'MODULE_PATHNAME','dblink_tok'
- --LANGUAGE 'C' WITH (isstrict);
- --CREATE OR REPLACE FUNCTION dblink_last_oid (int)
- --RETURNS oid
- --AS 'MODULE_PATHNAME','dblink_last_oid'
- --LANGUAGE 'C' WITH (isstrict);
-
CREATE OR REPLACE FUNCTION dblink_connect (text)
RETURNS text
AS 'MODULE_PATHNAME','dblink_connect'
LANGUAGE 'C' WITH (isstrict);
CREATE OR REPLACE FUNCTION dblink_disconnect ()
RETURNS text
AS 'MODULE_PATHNAME','dblink_disconnect'
LANGUAGE 'C' WITH (isstrict);
CREATE OR REPLACE FUNCTION dblink_open (text,text)
RETURNS text
AS 'MODULE_PATHNAME','dblink_open'
LANGUAGE 'C' WITH (isstrict);
CREATE OR REPLACE FUNCTION dblink_fetch (text,int)
RETURNS setof record
AS 'MODULE_PATHNAME','dblink_fetch'
LANGUAGE 'C' WITH (isstrict);
CREATE OR REPLACE FUNCTION dblink_close (text)
RETURNS text
AS 'MODULE_PATHNAME','dblink_close'
LANGUAGE 'C' WITH (isstrict);
! -- Note: if this is not a first time install of dblink, uncomment the
! -- following DROP which prepares the database for the new, non-deprecated
! -- version.
! --DROP FUNCTION dblink (text,text);
- -- Comment out the following 3 lines if the DEPRECATED functions are used.
CREATE OR REPLACE FUNCTION dblink (text,text)
RETURNS setof record
AS 'MODULE_PATHNAME','dblink_record'
--- 1,53 ----
CREATE OR REPLACE FUNCTION dblink_connect (text)
RETURNS text
AS 'MODULE_PATHNAME','dblink_connect'
LANGUAGE 'C' WITH (isstrict);
+ CREATE OR REPLACE FUNCTION dblink_connect (text, text)
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_connect'
+ LANGUAGE 'C' WITH (isstrict);
+
CREATE OR REPLACE FUNCTION dblink_disconnect ()
RETURNS text
AS 'MODULE_PATHNAME','dblink_disconnect'
LANGUAGE 'C' WITH (isstrict);
+ CREATE OR REPLACE FUNCTION dblink_disconnect (text)
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_disconnect'
+ LANGUAGE 'C' WITH (isstrict);
+
CREATE OR REPLACE FUNCTION dblink_open (text,text)
RETURNS text
AS 'MODULE_PATHNAME','dblink_open'
LANGUAGE 'C' WITH (isstrict);
+ CREATE OR REPLACE FUNCTION dblink_open (text,text,text)
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_open'
+ LANGUAGE 'C' WITH (isstrict);
+
CREATE OR REPLACE FUNCTION dblink_fetch (text,int)
RETURNS setof record
AS 'MODULE_PATHNAME','dblink_fetch'
LANGUAGE 'C' WITH (isstrict);
+ CREATE OR REPLACE FUNCTION dblink_fetch (text,text,int)
+ RETURNS setof record
+ AS 'MODULE_PATHNAME','dblink_fetch'
+ LANGUAGE 'C' WITH (isstrict);
+
CREATE OR REPLACE FUNCTION dblink_close (text)
RETURNS text
AS 'MODULE_PATHNAME','dblink_close'
LANGUAGE 'C' WITH (isstrict);
! CREATE OR REPLACE FUNCTION dblink_close (text,text)
! RETURNS text
! AS 'MODULE_PATHNAME','dblink_close'
! LANGUAGE 'C' WITH (isstrict);
CREATE OR REPLACE FUNCTION dblink (text,text)
RETURNS setof record
AS 'MODULE_PATHNAME','dblink_record'
Index: contrib/dblink/doc/connection
===================================================================
RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/doc/connection,v
retrieving revision 1.1
diff -c -r1.1 connection
*** contrib/dblink/doc/connection 2 Sep 2002 06:32:41 -0000 1.1
--- contrib/dblink/doc/connection 15 Jun 2003 05:26:53 -0000
***************
*** 6,26 ****
Synopsis
dblink_connect(text connstr)
Inputs
connstr
standard libpq format connection string,
e.g. "hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd"
Outputs
Returns status = "OK"
Example usage
! test=# select dblink_connect('dbname=template1');
dblink_connect
----------------
OK
--- 6,40 ----
Synopsis
dblink_connect(text connstr)
+ dblink_connect(text connname, text connstr)
Inputs
+ connname
+ if 2 arguments are given, the first is used as a name for a persistent
+ connection
+
connstr
standard libpq format connection string,
e.g. "hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd"
+ if only one argument is given, the connection is unnamed; only one unnamed
+ connection can exist at a time
+
Outputs
Returns status = "OK"
Example usage
! select dblink_connect('dbname=template1');
! dblink_connect
! ----------------
! OK
! (1 row)
!
! select dblink_connect('myconn','dbname=template1');
dblink_connect
----------------
OK
***************
*** 29,43 ****
==================================================================
Name
! dblink_disconnect -- Closes the persistent connection to a remote database
Synopsis
dblink_disconnect()
Inputs
! none
Outputs
--- 43,60 ----
==================================================================
Name
! dblink_disconnect -- Closes a persistent connection to a remote database
Synopsis
dblink_disconnect()
+ dblink_disconnect(text connname)
Inputs
! connname
! if an argument is given, it is used as a name for a persistent
! connection to close; otherwiase the unnamed connection is closed
Outputs
***************
*** 51,53 ****
--- 68,75 ----
OK
(1 row)
+ select dblink_disconnect('myconn');
+ dblink_disconnect
+ -------------------
+ OK
+ (1 row)
Index: contrib/dblink/doc/cursor
===================================================================
RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/doc/cursor,v
retrieving revision 1.1
diff -c -r1.1 cursor
*** contrib/dblink/doc/cursor 2 Sep 2002 06:32:41 -0000 1.1
--- contrib/dblink/doc/cursor 15 Jun 2003 05:42:15 -0000
***************
*** 6,14 ****
--- 6,19 ----
Synopsis
dblink_open(text cursorname, text sql)
+ dblink_open(text connname, text cursorname, text sql)
Inputs
+ connname
+ if three arguments are present, the first is taken as the specific
+ connection name to use; otherwise the unnamed connection is assumed
+
cursorname
a reference name for the cursor
***************
*** 52,60 ****
--- 57,70 ----
Synopsis
dblink_fetch(text cursorname, int32 howmany)
+ dblink_fetch(text connname, text cursorname, int32 howmany)
Inputs
+ connname
+ if three arguments are present, the first is taken as the specific
+ connection name to use; otherwise the unnamed connection is assumed
+
cursorname
The reference name for the cursor
***************
*** 123,131 ****
--- 133,146 ----
Synopsis
dblink_close(text cursorname)
+ dblink_close(text connname, text cursorname)
Inputs
+ connname
+ if two arguments are present, the first is taken as the specific
+ connection name to use; otherwise the unnamed connection is assumed
+
cursorname
a reference name for the cursor
***************
*** 135,141 ****
Returns status = "OK"
Note
! dblink_connect(text connstr) must be executed first.
Example usage
--- 150,157 ----
Returns status = "OK"
Note
! dblink_connect(text connstr) or dblink_connect(text connname, text connstr)
! must be executed first.
Example usage
***************
*** 157,159 ****
--- 173,192 ----
OK
(1 row)
+ select dblink_connect('myconn','dbname=regression');
+ dblink_connect
+ ----------------
+ OK
+ (1 row)
+
+ select dblink_open('myconn','foo','select proname, prosrc from pg_proc');
+ dblink_open
+ -------------
+ OK
+ (1 row)
+
+ select dblink_close('myconn','foo');
+ dblink_close
+ --------------
+ OK
+ (1 row)
Index: contrib/dblink/doc/deprecated
===================================================================
RCS file: contrib/dblink/doc/deprecated
diff -N contrib/dblink/doc/deprecated
*** contrib/dblink/doc/deprecated 2 Sep 2002 06:32:41 -0000 1.1
--- /dev/null 1 Jan 1970 00:00:00 -0000
***************
*** 1,105 ****
- ==================================================================
- Name
-
- *DEPRECATED* use new dblink syntax
- dblink -- Returns a resource id for a data set from a remote database
-
- Synopsis
-
- dblink(text connstr, text sql)
-
- Inputs
-
- connstr
-
- standard libpq format connection srting,
- e.g. "hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd"
-
- sql
-
- sql statement that you wish to execute on the remote host
- e.g. "select * from pg_class"
-
- Outputs
-
- Returns setof int (res_id)
-
- Example usage
-
- select dblink('hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd'
- ,'select f1, f2 from mytable');
-
- ==================================================================
-
- Name
-
- *DEPRECATED* use new dblink syntax
- dblink_tok -- Returns individual select field results from a dblink remote query
-
- Synopsis
-
- dblink_tok(int res_id, int fnumber)
-
- Inputs
-
- res_id
-
- a resource id returned by a call to dblink()
-
- fnumber
-
- the ordinal position (zero based) of the field to be returned from the dblink result set
-
- Outputs
-
- Returns text
-
- Example usage
-
- select dblink_tok(t1.dblink_p,0) as f1, dblink_tok(t1.dblink_p,1) as f2
- from (select dblink('hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd'
- ,'select f1, f2 from mytable') as dblink_p) as t1;
-
-
- ==================================================================
- *DEPRECATED* use new dblink syntax
- A more convenient way to use dblink may be to create a view:
-
- create view myremotetable as
- select dblink_tok(t1.dblink_p,0) as f1, dblink_tok(t1.dblink_p,1) as f2
- from (select dblink('hostaddr=127.0.0.1 port=5432 dbname=template1 user=postgres password=postgres'
- ,'select proname, prosrc from pg_proc') as dblink_p) as t1;
-
- Then you can simply write:
-
- select f1, f2 from myremotetable where f1 like 'bytea%';
-
- ==================================================================
- Name
- *DEPRECATED* use new dblink_exec syntax
- dblink_last_oid -- Returns last inserted oid
-
- Synopsis
-
- dblink_last_oid(int res_id) RETURNS oid
-
- Inputs
-
- res_id
-
- any resource id returned by dblink function;
-
- Outputs
-
- Returns oid of last inserted tuple
-
- Example usage
-
- test=# select dblink_last_oid(dblink('hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd'
- ,'insert into mytable (f1, f2) values (1,2)'));
-
- dblink_last_oid
- ----------------
- 16553
- (1 row)
-
--- 0 ----
Index: contrib/dblink/doc/execute
===================================================================
RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/doc/execute,v
retrieving revision 1.1
diff -c -r1.1 execute
*** contrib/dblink/doc/execute 2 Sep 2002 06:32:41 -0000 1.1
--- contrib/dblink/doc/execute 15 Jun 2003 05:41:10 -0000
***************
*** 6,27 ****
Synopsis
dblink_exec(text connstr, text sql)
! - or -
dblink_exec(text sql)
Inputs
connstr
! standard libpq format connection string,
! e.g. "hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd"
! If the second form is used, then the dblink_connect(text connstr) must be
! executed first.
sql
sql statement that you wish to execute on the remote host, e.g.:
-
insert into foo values(0,'a','{"a0","b0","c0"}');
Outputs
--- 6,28 ----
Synopsis
dblink_exec(text connstr, text sql)
! dblink_exec(text connname, text sql)
dblink_exec(text sql)
Inputs
+ connname
connstr
+ If two arguments are present, the first is first assumed to be a specific
+ connection name to use. If the name is not found, the argument is then
+ assumed to be a valid connection string, of standard libpq format,
+ e.g.: "hostaddr=127.0.0.1 dbname=mydb user=postgres password=mypasswd"
! If only one argument is used, then the unnamed connection is used.
sql
sql statement that you wish to execute on the remote host, e.g.:
insert into foo values(0,'a','{"a0","b0","c0"}');
Outputs
***************
*** 36,49 ****
Example usage
! test=# select dblink_connect('dbname=dblink_test_slave');
dblink_connect
----------------
OK
(1 row)
! test=# select dblink_exec('insert into foo values(21,''z'',''{"a0","b0","c0"}'');');
dblink_exec
-----------------
INSERT 943366 1
(1 row)
--- 37,62 ----
Example usage
! select dblink_connect('dbname=dblink_test_slave');
dblink_connect
----------------
OK
(1 row)
! select dblink_exec('insert into foo values(21,''z'',''{"a0","b0","c0"}'');');
dblink_exec
-----------------
INSERT 943366 1
+ (1 row)
+
+ select dblink_connect('myconn','dbname=regression');
+ dblink_connect
+ ----------------
+ OK
+ (1 row)
+
+ select dblink_exec('myconn','insert into foo values(21,''z'',''{"a0","b0","c0"}'');');
+ dblink_exec
+ ------------------
+ INSERT 6432584 1
(1 row)
Index: contrib/dblink/doc/query
===================================================================
RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/doc/query,v
retrieving revision 1.1
diff -c -r1.1 query
*** contrib/dblink/doc/query 2 Sep 2002 06:32:41 -0000 1.1
--- contrib/dblink/doc/query 15 Jun 2003 05:39:31 -0000
***************
*** 6,22 ****
Synopsis
dblink(text connstr, text sql)
! - or -
dblink(text sql)
Inputs
connstr
! standard libpq format connection string,
! e.g. "hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd"
! If the second form is used, then the dblink_connect(text connstr) must be
! executed first.
sql
--- 6,24 ----
Synopsis
dblink(text connstr, text sql)
! dblink(text connname, text sql)
dblink(text sql)
Inputs
+ connname
connstr
+ If two arguments are present, the first is first assumed to be a specific
+ connection name to use. If the name is not found, the argument is then
+ assumed to be a valid connection string, of standard libpq format,
+ e.g.: "hostaddr=127.0.0.1 dbname=mydb user=postgres password=mypasswd"
! If only one argument is used, then the unnamed connection is used.
sql
***************
*** 29,35 ****
Example usage
! test=# select * from dblink('dbname=template1','select proname, prosrc from pg_proc')
as t1(proname name, prosrc text) where proname like 'bytea%';
proname | prosrc
------------+------------
--- 31,37 ----
Example usage
! select * from dblink('dbname=template1','select proname, prosrc from pg_proc')
as t1(proname name, prosrc text) where proname like 'bytea%';
proname | prosrc
------------+------------
***************
*** 47,59 ****
byteaout | byteaout
(12 rows)
! test=# select dblink_connect('dbname=template1');
dblink_connect
----------------
OK
(1 row)
! test=# select * from dblink('select proname, prosrc from pg_proc')
as t1(proname name, prosrc text) where proname like 'bytea%';
proname | prosrc
------------+------------
--- 49,61 ----
byteaout | byteaout
(12 rows)
! select dblink_connect('dbname=template1');
dblink_connect
----------------
OK
(1 row)
! select * from dblink('select proname, prosrc from pg_proc')
as t1(proname name, prosrc text) where proname like 'bytea%';
proname | prosrc
------------+------------
***************
*** 70,75 ****
--- 72,104 ----
byteain | byteain
byteaout | byteaout
(12 rows)
+
+ select dblink_connect('myconn','dbname=regression');
+ dblink_connect
+ ----------------
+ OK
+ (1 row)
+
+ select * from dblink('myconn','select proname, prosrc from pg_proc')
+ as t1(proname name, prosrc text) where proname like 'bytea%';
+ proname | prosrc
+ ------------+------------
+ bytearecv | bytearecv
+ byteasend | byteasend
+ byteale | byteale
+ byteagt | byteagt
+ byteage | byteage
+ byteane | byteane
+ byteacmp | byteacmp
+ bytealike | bytealike
+ byteanlike | byteanlike
+ byteacat | byteacat
+ byteaeq | byteaeq
+ bytealt | bytealt
+ byteain | byteain
+ byteaout | byteaout
+ (14 rows)
+
==================================================================
A more convenient way to use dblink may be to create a view:
Index: contrib/dblink/expected/dblink.out
===================================================================
RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/expected/dblink.out,v
retrieving revision 1.8
diff -c -r1.8 dblink.out
*** contrib/dblink/expected/dblink.out 14 May 2003 03:25:55 -0000 1.8
--- contrib/dblink/expected/dblink.out 15 Jun 2003 04:11:11 -0000
***************
*** 106,116 ****
9 | j | {a9,b9,c9}
(2 rows)
! -- should generate "no connection available" error
SELECT *
FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
WHERE t.a > 7;
! ERROR: dblink: no connection available
-- create a persistent connection
SELECT dblink_connect('dbname=regression');
dblink_connect
--- 106,116 ----
9 | j | {a9,b9,c9}
(2 rows)
! -- should generate "connection not available" error
SELECT *
FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
WHERE t.a > 7;
! ERROR: dblink_record: connection not available
-- create a persistent connection
SELECT dblink_connect('dbname=regression');
dblink_connect
***************
*** 172,181 ****
OK
(1 row)
! -- should generate "cursor rmt_foo_cursor does not exist" error
SELECT *
FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
! ERROR: dblink_fetch: cursor rmt_foo_cursor does not exist
-- close the persistent connection
SELECT dblink_disconnect();
dblink_disconnect
--- 172,181 ----
OK
(1 row)
! -- should generate "cursor not found: rmt_foo_cursor" error
SELECT *
FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
! ERROR: dblink_fetch: cursor not found: rmt_foo_cursor
-- close the persistent connection
SELECT dblink_disconnect();
dblink_disconnect
***************
*** 183,193 ****
OK
(1 row)
! -- should generate "no connection available" error
SELECT *
FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
WHERE t.a > 7;
! ERROR: dblink: no connection available
-- put more data into our slave table, first using arbitrary connection syntax
-- but truncate the actual return value so we can use diff to check for success
SELECT substr(dblink_exec('dbname=regression','INSERT INTO foo VALUES(10,''k'',''{"a10","b10","c10"}'')'),1,6);
--- 183,194 ----
OK
(1 row)
! -- should generate "no connection to the server" error
SELECT *
FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
WHERE t.a > 7;
! ERROR: dblink: sql error: no connection to the server
!
-- put more data into our slave table, first using arbitrary connection syntax
-- but truncate the actual return value so we can use diff to check for success
SELECT substr(dblink_exec('dbname=regression','INSERT INTO foo VALUES(10,''k'',''{"a10","b10","c10"}'')'),1,6);
***************
*** 268,270 ****
--- 269,466 ----
OK
(1 row)
+ --
+ -- tests for the new named persistent connection syntax
+ --
+ -- should generate "missing "=" after "myconn" in connection info string" error
+ SELECT *
+ FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
+ WHERE t.a > 7;
+ ERROR: dblink: connection error: missing "=" after "myconn" in connection info string
+
+ -- create a named persistent connection
+ SELECT dblink_connect('myconn','dbname=regression');
+ dblink_connect
+ ----------------
+ OK
+ (1 row)
+
+ -- use the named persistent connection
+ SELECT *
+ FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
+ WHERE t.a > 7;
+ a | b | c
+ ----+---+---------------
+ 8 | i | {a8,b8,c8}
+ 9 | j | {a9,b9,c9}
+ 10 | k | {a10,b10,c10}
+ (3 rows)
+
+ -- create a second named persistent connection
+ -- should error with "cannot save named connection"
+ SELECT dblink_connect('myconn','dbname=regression');
+ NOTICE: cannot use a connection name more than once
+ ERROR: dblink_connect: cannot save named connection
+ -- create a second named persistent connection with a new name
+ SELECT dblink_connect('myconn2','dbname=regression');
+ dblink_connect
+ ----------------
+ OK
+ (1 row)
+
+ -- use the second named persistent connection
+ SELECT *
+ FROM dblink('myconn2','SELECT * FROM foo') AS t(a int, b text, c text[])
+ WHERE t.a > 7;
+ a | b | c
+ ----+---+---------------
+ 8 | i | {a8,b8,c8}
+ 9 | j | {a9,b9,c9}
+ 10 | k | {a10,b10,c10}
+ (3 rows)
+
+ -- close the second named persistent connection
+ SELECT dblink_disconnect('myconn2');
+ dblink_disconnect
+ -------------------
+ OK
+ (1 row)
+
+ -- open a cursor
+ SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foo');
+ dblink_open
+ -------------
+ OK
+ (1 row)
+
+ -- fetch some data
+ SELECT *
+ FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
+ a | b | c
+ ---+---+------------
+ 0 | a | {a0,b0,c0}
+ 1 | b | {a1,b1,c1}
+ 2 | c | {a2,b2,c2}
+ 3 | d | {a3,b3,c3}
+ (4 rows)
+
+ SELECT *
+ FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
+ a | b | c
+ ---+---+------------
+ 4 | e | {a4,b4,c4}
+ 5 | f | {a5,b5,c5}
+ 6 | g | {a6,b6,c6}
+ 7 | h | {a7,b7,c7}
+ (4 rows)
+
+ -- this one only finds three rows left
+ SELECT *
+ FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
+ a | b | c
+ ----+---+---------------
+ 8 | i | {a8,b8,c8}
+ 9 | j | {a9,b9,c9}
+ 10 | k | {a10,b10,c10}
+ (3 rows)
+
+ -- close the cursor
+ SELECT dblink_close('myconn','rmt_foo_cursor');
+ dblink_close
+ --------------
+ OK
+ (1 row)
+
+ -- should generate "cursor not found: rmt_foo_cursor" error
+ SELECT *
+ FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
+ ERROR: dblink_fetch: cursor not found: rmt_foo_cursor
+ -- close the named persistent connection
+ SELECT dblink_disconnect('myconn');
+ dblink_disconnect
+ -------------------
+ OK
+ (1 row)
+
+ -- should generate "missing "=" after "myconn" in connection info string" error
+ SELECT *
+ FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
+ WHERE t.a > 7;
+ ERROR: dblink: connection error: missing "=" after "myconn" in connection info string
+
+ -- create a named persistent connection
+ SELECT dblink_connect('myconn','dbname=regression');
+ dblink_connect
+ ----------------
+ OK
+ (1 row)
+
+ -- put more data into our slave table, using named persistent connection syntax
+ -- but truncate the actual return value so we can use diff to check for success
+ SELECT substr(dblink_exec('myconn','INSERT INTO foo VALUES(11,''l'',''{"a11","b11","c11"}'')'),1,6);
+ substr
+ --------
+ INSERT
+ (1 row)
+
+ -- let's see it
+ SELECT *
+ FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]);
+ a | b | c
+ ----+---+---------------
+ 0 | a | {a0,b0,c0}
+ 1 | b | {a1,b1,c1}
+ 2 | c | {a2,b2,c2}
+ 3 | d | {a3,b3,c3}
+ 4 | e | {a4,b4,c4}
+ 5 | f | {a5,b5,c5}
+ 6 | g | {a6,b6,c6}
+ 7 | h | {a7,b7,c7}
+ 8 | i | {a8,b8,c8}
+ 9 | j | {a9,b9,c9}
+ 10 | k | {a10,b10,c10}
+ 11 | l | {a11,b11,c11}
+ (12 rows)
+
+ -- change some data
+ SELECT dblink_exec('myconn','UPDATE foo SET f3[2] = ''b99'' WHERE f1 = 11');
+ dblink_exec
+ -------------
+ UPDATE 1
+ (1 row)
+
+ -- let's see it
+ SELECT *
+ FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
+ WHERE a = 11;
+ a | b | c
+ ----+---+---------------
+ 11 | l | {a11,b99,c11}
+ (1 row)
+
+ -- delete some data
+ SELECT dblink_exec('myconn','DELETE FROM foo WHERE f1 = 11');
+ dblink_exec
+ -------------
+ DELETE 1
+ (1 row)
+
+ -- let's see it
+ SELECT *
+ FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
+ WHERE a = 11;
+ a | b | c
+ ---+---+---
+ (0 rows)
+
+ -- close the named persistent connection
+ SELECT dblink_disconnect('myconn');
+ dblink_disconnect
+ -------------------
+ OK
+ (1 row)
+
+ -- close the named persistent connection again
+ -- should get "connection named "myconn" not found" error
+ SELECT dblink_disconnect('myconn');
+ ERROR: dblink_disconnect: connection named "myconn" not found
Index: contrib/dblink/sql/dblink.sql
===================================================================
RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/sql/dblink.sql,v
retrieving revision 1.8
diff -c -r1.8 dblink.sql
*** contrib/dblink/sql/dblink.sql 14 May 2003 03:25:55 -0000 1.8
--- contrib/dblink/sql/dblink.sql 15 Jun 2003 04:10:59 -0000
***************
*** 68,74 ****
FROM dblink('dbname=regression','SELECT * FROM foo') AS t(a int, b text, c text[])
WHERE t.a > 7;
! -- should generate "no connection available" error
SELECT *
FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
WHERE t.a > 7;
--- 68,74 ----
FROM dblink('dbname=regression','SELECT * FROM foo') AS t(a int, b text, c text[])
WHERE t.a > 7;
! -- should generate "connection not available" error
SELECT *
FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
WHERE t.a > 7;
***************
*** 98,111 ****
-- close the cursor
SELECT dblink_close('rmt_foo_cursor');
! -- should generate "cursor rmt_foo_cursor does not exist" error
SELECT *
FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
-- close the persistent connection
SELECT dblink_disconnect();
! -- should generate "no connection available" error
SELECT *
FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
WHERE t.a > 7;
--- 98,111 ----
-- close the cursor
SELECT dblink_close('rmt_foo_cursor');
! -- should generate "cursor not found: rmt_foo_cursor" error
SELECT *
FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
-- close the persistent connection
SELECT dblink_disconnect();
! -- should generate "no connection to the server" error
SELECT *
FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
WHERE t.a > 7;
***************
*** 143,145 ****
--- 143,240 ----
-- close the persistent connection
SELECT dblink_disconnect();
+
+ --
+ -- tests for the new named persistent connection syntax
+ --
+
+ -- should generate "missing "=" after "myconn" in connection info string" error
+ SELECT *
+ FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
+ WHERE t.a > 7;
+
+ -- create a named persistent connection
+ SELECT dblink_connect('myconn','dbname=regression');
+
+ -- use the named persistent connection
+ SELECT *
+ FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
+ WHERE t.a > 7;
+
+ -- create a second named persistent connection
+ -- should error with "cannot save named connection"
+ SELECT dblink_connect('myconn','dbname=regression');
+
+ -- create a second named persistent connection with a new name
+ SELECT dblink_connect('myconn2','dbname=regression');
+
+ -- use the second named persistent connection
+ SELECT *
+ FROM dblink('myconn2','SELECT * FROM foo') AS t(a int, b text, c text[])
+ WHERE t.a > 7;
+
+ -- close the second named persistent connection
+ SELECT dblink_disconnect('myconn2');
+
+ -- open a cursor
+ SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foo');
+
+ -- fetch some data
+ SELECT *
+ FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
+
+ SELECT *
+ FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
+
+ -- this one only finds three rows left
+ SELECT *
+ FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
+
+ -- close the cursor
+ SELECT dblink_close('myconn','rmt_foo_cursor');
+
+ -- should generate "cursor not found: rmt_foo_cursor" error
+ SELECT *
+ FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
+
+ -- close the named persistent connection
+ SELECT dblink_disconnect('myconn');
+
+ -- should generate "missing "=" after "myconn" in connection info string" error
+ SELECT *
+ FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
+ WHERE t.a > 7;
+
+ -- create a named persistent connection
+ SELECT dblink_connect('myconn','dbname=regression');
+
+ -- put more data into our slave table, using named persistent connection syntax
+ -- but truncate the actual return value so we can use diff to check for success
+ SELECT substr(dblink_exec('myconn','INSERT INTO foo VALUES(11,''l'',''{"a11","b11","c11"}'')'),1,6);
+
+ -- let's see it
+ SELECT *
+ FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]);
+
+ -- change some data
+ SELECT dblink_exec('myconn','UPDATE foo SET f3[2] = ''b99'' WHERE f1 = 11');
+
+ -- let's see it
+ SELECT *
+ FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
+ WHERE a = 11;
+
+ -- delete some data
+ SELECT dblink_exec('myconn','DELETE FROM foo WHERE f1 = 11');
+
+ -- let's see it
+ SELECT *
+ FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
+ WHERE a = 11;
+
+ -- close the named persistent connection
+ SELECT dblink_disconnect('myconn');
+
+ -- close the named persistent connection again
+ -- should get "connection named "myconn" not found" error
+ SELECT dblink_disconnect('myconn');
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
I will try to apply it within the next 48 hours.
---------------------------------------------------------------------------
Shridhar Daithankar wrote:
Content-Description: Mail message body
> Hello,
>
> Please apply attached patch to contrib/dblink. It adds named persistent
> connections to dblink.
>
> I have been working on this with Joe Conway and this patch is seems to work
> without any problem.
>
> Thanks Joe, for your assistance.
>
>
>
> Bye
> Shridhar
>
> --
> Anthony's Law of the Workshop: Any tool when dropped, will roll into the least
> accessible corner of the workshop.Corollary: On the way to the corner, any
> dropped tool will first strike your toes.
>
>
Content-Description: Text from file 'dblink.02.diff'
> Index: contrib/dblink/README.dblink
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/README.dblink,v
> retrieving revision 1.7
> diff -c -r1.7 README.dblink
> *** contrib/dblink/README.dblink 23 Nov 2002 18:59:25 -0000 1.7
> --- contrib/dblink/README.dblink 15 Jun 2003 05:17:46 -0000
> ***************
> *** 4,11 ****
> * Functions returning results from a remote database
> *
> * Joe Conway <mail@joeconway.com>
> *
> ! * Copyright (c) 2001, 2002 by PostgreSQL Global Development Group
> * ALL RIGHTS RESERVED;
> *
> * Permission to use, copy, modify, and distribute this software and its
> --- 4,14 ----
> * Functions returning results from a remote database
> *
> * Joe Conway <mail@joeconway.com>
> + * And contributors:
> + * Darko Prenosil <Darko.Prenosil@finteh.hr>
> + * Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
> *
> ! * Copyright (c) 2001, 2002, 2003 by PostgreSQL Global Development Group
> * ALL RIGHTS RESERVED;
> *
> * Permission to use, copy, modify, and distribute this software and its
> ***************
> *** 27,40 ****
> *
> */
>
> ! Version 0.5 (25 August, 2002):
> ! Major overhaul to work with new backend "table function" capability. Removed
> ! dblink_strtok() and dblink_replace() functions because they are now
> ! available as backend functions (split() and replace() respectively).
> ! Tested under Linux (Red Hat 7.3) and PostgreSQL 7.3devel. This version
> ! is no longer backwards portable to PostgreSQL 7.2.
>
> Release Notes:
> Version 0.5
> - dblink now supports use directly as a table function; this is the new
> preferred usage going forward
> --- 30,45 ----
> *
> */
>
> ! Version 0.6 (14 June, 2003):
> ! Completely removed previously deprecated functions. Added ability
> ! to create "named" persistent connections in addition to the single global
> ! "unnamed" persistent connection.
> ! Tested under Linux (Red Hat 9) and PostgreSQL 7.4devel.
>
> Release Notes:
> + Version 0.6
> + - functions deprecated in 0.5 have been removed
> + - added ability to create "named" persistent connections
> Version 0.5
> - dblink now supports use directly as a table function; this is the new
> preferred usage going forward
> ***************
> *** 87,121 ****
> connection
> ------------
> dblink_connect(text) RETURNS text
> ! - opens a connection that will persist for duration of current
> backend or until it is disconnected
> dblink_disconnect() RETURNS text
> ! - disconnects a persistent connection
>
> cursor
> ------------
> dblink_open(text,text) RETURNS text
> ! - opens a cursor using connection already opened with dblink_connect()
> ! that will persist for duration of current backend or until it is
> ! closed
> dblink_fetch(text, int) RETURNS setof record
> ! - fetches data from an already opened cursor
> dblink_close(text) RETURNS text
> ! - closes a cursor
>
> query
> ------------
> dblink(text,text) RETURNS setof record
> ! - returns a set of results from remote SELECT query
> ! (Note: comment out in dblink.sql to use deprecated version)
> dblink(text) RETURNS setof record
> ! - returns a set of results from remote SELECT query, using connection
> ! already opened with dblink_connect()
>
> execute
> ------------
> dblink_exec(text, text) RETURNS text
> ! - executes an INSERT/UPDATE/DELETE query remotely
> dblink_exec(text) RETURNS text
> - executes an INSERT/UPDATE/DELETE query remotely, using connection
> already opened with dblink_connect()
> --- 92,142 ----
> connection
> ------------
> dblink_connect(text) RETURNS text
> ! - opens an unnamed connection that will persist for duration of
> ! current backend or until it is disconnected
> ! dblink_connect(text,text) RETURNS text
> ! - opens a named connection that will persist for duration of current
> backend or until it is disconnected
> dblink_disconnect() RETURNS text
> ! - disconnects the unnamed persistent connection
> ! dblink_disconnect(text) RETURNS text
> ! - disconnects a named persistent connection
>
> cursor
> ------------
> dblink_open(text,text) RETURNS text
> ! - opens a cursor using unnamed connection already opened with
> ! dblink_connect() that will persist for duration of current backend
> ! or until it is closed
> ! dblink_open(text,text,text) RETURNS text
> ! - opens a cursor using a named connection already opened with
> ! dblink_connect() that will persist for duration of current backend
> ! or until it is closed
> dblink_fetch(text, int) RETURNS setof record
> ! - fetches data from an already opened cursor on the unnamed connection
> ! dblink_fetch(text, text, int) RETURNS setof record
> ! - fetches data from an already opened cursor on a named connection
> dblink_close(text) RETURNS text
> ! - closes a cursor on the unnamed connection
> ! dblink_close(text,text) RETURNS text
> ! - closes a cursor on a named connection
>
> query
> ------------
> dblink(text,text) RETURNS setof record
> ! - returns a set of results from remote SELECT query; the first argument
> ! is either a connection string, or the name of an already opened
> ! persistant connection
> dblink(text) RETURNS setof record
> ! - returns a set of results from remote SELECT query, using the unnamed
> ! connection already opened with dblink_connect()
>
> execute
> ------------
> dblink_exec(text, text) RETURNS text
> ! - executes an INSERT/UPDATE/DELETE query remotely; the first argument
> ! is either a connection string, or the name of an already opened
> ! persistant connection
> dblink_exec(text) RETURNS text
> - executes an INSERT/UPDATE/DELETE query remotely, using connection
> already opened with dblink_connect()
> ***************
> *** 135,153 ****
> dblink_build_sql_update(text,int2vector,int2,_text,_text) RETURNS text
> - builds an update statement using a local tuple, replacing the
> selection key field values with alternate supplied values
> -
> - Not installed by default
> - deprecated
> - ------------
> - dblink(text,text) RETURNS setof int
> - - *DEPRECATED* returns a resource id for results from remote query
> - (Note: must uncomment in dblink.sql to use)
> - dblink_tok(int,int) RETURNS text
> - - *DEPRECATED* extracts and returns individual field results; used
> - only in conjunction with the *DEPRECATED* form of dblink
> - (Note: must uncomment in dblink.sql to use)
> - dblink_last_oid(int) RETURNS oid
> - - *DEPRECATED* returns the last inserted oid
>
> Documentation:
>
> --- 156,161 ----
> Index: contrib/dblink/dblink.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/dblink.c,v
> retrieving revision 1.20
> diff -c -r1.20 dblink.c
> *** contrib/dblink/dblink.c 28 May 2003 16:03:55 -0000 1.20
> --- contrib/dblink/dblink.c 15 Jun 2003 04:17:52 -0000
> ***************
> *** 4,11 ****
> * Functions returning results from a remote database
> *
> * Joe Conway <mail@joeconway.com>
> *
> ! * Copyright (c) 2001, 2002 by PostgreSQL Global Development Group
> * ALL RIGHTS RESERVED;
> *
> * Permission to use, copy, modify, and distribute this software and its
> --- 4,14 ----
> * Functions returning results from a remote database
> *
> * Joe Conway <mail@joeconway.com>
> + * And contributors:
> + * Darko Prenosil <Darko.Prenosil@finteh.hr>
> + * Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
> *
> ! * Copyright (c) 2001, 2002, 2003 by PostgreSQL Global Development Group
> * ALL RIGHTS RESERVED;
> *
> * Permission to use, copy, modify, and distribute this software and its
> ***************
> *** 27,35 ****
> *
> */
> #include "postgres.h"
> -
> #include "libpq-fe.h"
> -
> #include "fmgr.h"
> #include "funcapi.h"
> #include "access/tupdesc.h"
> --- 30,36 ----
> ***************
> *** 51,63 ****
> #include "utils/array.h"
> #include "utils/lsyscache.h"
> #include "utils/syscache.h"
>
> #include "dblink.h"
>
> /*
> * Internal declarations
> */
> ! static dblink_results *init_dblink_results(MemoryContext fn_mcxt);
> static char **get_pkey_attnames(Oid relid, int16 *numatts);
> static char *get_sql_insert(Oid relid, int16 *pkattnums, int16 pknumatts, char **src_pkattvals, char
**tgt_pkattvals);
> static char *get_sql_delete(Oid relid, int16 *pkattnums, int16 pknumatts, char **tgt_pkattvals);
> --- 52,78 ----
> #include "utils/array.h"
> #include "utils/lsyscache.h"
> #include "utils/syscache.h"
> + #include "utils/palloc.h"
> + #include "utils/dynahash.h"
> + #include "utils/hsearch.h"
> + #include "utils/memutils.h"
>
> #include "dblink.h"
>
> + typedef struct remoteConn
> + {
> + PGconn *con; /* Hold the remote connection */
> + bool remoteTrFlag; /* Indicates whether or not a transaction
> + * on remote database is in progress*/
> + } remoteConn;
> +
> /*
> * Internal declarations
> */
> ! static remoteConn *getConnectionByName(const char *name);
> ! static HTAB *createConnHash(void);
> ! static bool createNewConnection(const char *name,remoteConn *con);
> ! static void deleteConnection(const char *name);
> static char **get_pkey_attnames(Oid relid, int16 *numatts);
> static char *get_sql_insert(Oid relid, int16 *pkattnums, int16 pknumatts, char **src_pkattvals, char
**tgt_pkattvals);
> static char *get_sql_delete(Oid relid, int16 *pkattnums, int16 pknumatts, char **tgt_pkattvals);
> ***************
> *** 67,83 ****
> static int16 get_attnum_pk_pos(int16 *pkattnums, int16 pknumatts, int16 key);
> static HeapTuple get_tuple_of_interest(Oid relid, int16 *pkattnums, int16 pknumatts, char **src_pkattvals);
> static Oid get_relid_from_relname(text *relname_text);
> - static dblink_results *get_res_ptr(int32 res_id_index);
> - static void append_res_ptr(dblink_results * results);
> - static void remove_res_ptr(dblink_results * results);
> static TupleDesc pgresultGetTupleDesc(PGresult *res);
> static char *generate_relation_name(Oid relid);
>
> /* Global */
> ! List *res_id = NIL;
> ! int res_id_index = 0;
> ! PGconn *persistent_conn = NULL;
>
> #define GET_TEXT(cstrp) DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(cstrp)))
> #define GET_STR(textp) DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(textp)))
> #define xpfree(var_) \
> --- 82,113 ----
> static int16 get_attnum_pk_pos(int16 *pkattnums, int16 pknumatts, int16 key);
> static HeapTuple get_tuple_of_interest(Oid relid, int16 *pkattnums, int16 pknumatts, char **src_pkattvals);
> static Oid get_relid_from_relname(text *relname_text);
> static TupleDesc pgresultGetTupleDesc(PGresult *res);
> static char *generate_relation_name(Oid relid);
>
> /* Global */
> ! List *res_id = NIL;
> ! int res_id_index = 0;
> ! PGconn *persistent_conn = NULL;
> ! static HTAB *remoteConnHash=NULL;
> !
> ! /*
> ! Following is list that holds multiple remote connections.
> ! Calling convention of each dblink function changes to accept
> ! connection name as the first parameter. The connection list is
> ! much like ecpg e.g. a mapping between a name and a PGconn object.
> ! */
> !
> ! typedef struct remoteConnHashEnt
> ! {
> ! char name[NAMEDATALEN];
> ! remoteConn *rcon;
> ! } remoteConnHashEnt;
> !
> ! /* initial number of connection hashes */
> ! #define NUMCONN 16
>
> + /* general utility */
> #define GET_TEXT(cstrp) DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(cstrp)))
> #define GET_STR(textp) DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(textp)))
> #define xpfree(var_) \
> ***************
> *** 88,93 ****
> --- 118,158 ----
> var_ = NULL; \
> } \
> } while (0)
> + #define DBLINK_RES_ERROR(p1, p2) \
> + do { \
> + msg = pstrdup(PQerrorMessage(conn)); \
> + if (res) \
> + PQclear(res); \
> + elog(ERROR, "%s: %s: %s", p1, p2, msg); \
> + } while (0)
> + #define DBLINK_CONN_NOT_AVAIL(p1) \
> + do { \
> + if(conname) \
> + elog(ERROR, "%s: connection %s not available", p1, conname); \
> + else \
> + elog(ERROR, "%s: connection not available", p1); \
> + } while (0)
> + #define DBLINK_GET_CONN(p1) \
> + do { \
> + char *conname_or_str = GET_STR(PG_GETARG_TEXT_P(0)); \
> + rcon = getConnectionByName(conname_or_str); \
> + if(rcon) \
> + { \
> + conn = rcon->con; \
> + freeconn = false; \
> + } \
> + else \
> + { \
> + connstr = conname_or_str; \
> + conn = PQconnectdb(connstr); \
> + if (PQstatus(conn) == CONNECTION_BAD) \
> + { \
> + msg = pstrdup(PQerrorMessage(conn)); \
> + PQfinish(conn); \
> + elog(ERROR, "%s: connection error: %s", p1, msg); \
> + } \
> + } \
> + } while (0)
>
>
> /*
> ***************
> *** 97,124 ****
> Datum
> dblink_connect(PG_FUNCTION_ARGS)
> {
> ! char *connstr = GET_STR(PG_GETARG_TEXT_P(0));
> char *msg;
> - text *result_text;
> MemoryContext oldcontext;
>
> ! if (persistent_conn != NULL)
> ! PQfinish(persistent_conn);
>
> oldcontext = MemoryContextSwitchTo(TopMemoryContext);
> ! persistent_conn = PQconnectdb(connstr);
> MemoryContextSwitchTo(oldcontext);
>
> ! if (PQstatus(persistent_conn) == CONNECTION_BAD)
> {
> ! msg = pstrdup(PQerrorMessage(persistent_conn));
> ! PQfinish(persistent_conn);
> ! persistent_conn = NULL;
> elog(ERROR, "dblink_connect: connection error: %s", msg);
> }
>
> ! result_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum("OK")));
> ! PG_RETURN_TEXT_P(result_text);
> }
>
> /*
> --- 162,213 ----
> Datum
> dblink_connect(PG_FUNCTION_ARGS)
> {
> ! char *connstr = NULL;
> ! char *connname = NULL;
> char *msg;
> MemoryContext oldcontext;
> + PGconn *conn = NULL;
> + remoteConn *rcon = NULL;
>
> ! if(PG_NARGS()==2)
> ! {
> ! connstr = GET_STR(PG_GETARG_TEXT_P(1));
> ! connname = GET_STR(PG_GETARG_TEXT_P(0));
> ! }
> ! else if(PG_NARGS()==1)
> ! connstr = GET_STR(PG_GETARG_TEXT_P(0));
>
> oldcontext = MemoryContextSwitchTo(TopMemoryContext);
> !
> ! if(connname)
> ! rcon=(remoteConn *) palloc(sizeof(remoteConn));
> ! conn = PQconnectdb(connstr);
> !
> MemoryContextSwitchTo(oldcontext);
>
> ! if (PQstatus(conn) == CONNECTION_BAD)
> {
> ! msg = pstrdup(PQerrorMessage(conn));
> ! PQfinish(conn);
> ! if(rcon)
> ! pfree(rcon);
> elog(ERROR, "dblink_connect: connection error: %s", msg);
> }
>
> ! if(connname)
> ! {
> ! rcon->con = conn;
> ! if(createNewConnection(connname, rcon) == false)
> ! {
> ! PQfinish(conn);
> ! pfree(rcon);
> ! elog(ERROR, "dblink_connect: cannot save named connection");
> ! }
> ! }
> ! else
> ! persistent_conn = conn;
> !
> ! PG_RETURN_TEXT_P(GET_TEXT("OK"));
> }
>
> /*
> ***************
> *** 128,142 ****
> Datum
> dblink_disconnect(PG_FUNCTION_ARGS)
> {
> ! text *result_text;
>
> ! if (persistent_conn != NULL)
> ! PQfinish(persistent_conn);
>
> ! persistent_conn = NULL;
>
> ! result_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum("OK")));
> ! PG_RETURN_TEXT_P(result_text);
> }
>
> /*
> --- 217,253 ----
> Datum
> dblink_disconnect(PG_FUNCTION_ARGS)
> {
> ! char *str = NULL;
> ! remoteConn *rcon = NULL;
> ! PGconn *conn = NULL;
> !
> ! if (PG_NARGS() ==1 )
> ! {
> ! str = GET_STR(PG_GETARG_TEXT_P(0));
> ! rcon = getConnectionByName(str);
> ! if (rcon)
> ! conn = rcon->con;
> ! }
> ! else
> ! conn = persistent_conn;
>
> ! if (!conn)
> ! {
> ! if (str)
> ! elog(ERROR,"dblink_disconnect: connection named \"%s\" not found",
> ! str);
> ! else
> ! elog(ERROR,"dblink_disconnect: connection not found");
> ! }
>
> ! PQfinish(conn);
> ! if (rcon)
> ! {
> ! deleteConnection(str);
> ! pfree(rcon);
> ! }
>
> ! PG_RETURN_TEXT_P(GET_TEXT("OK"));
> }
>
> /*
> ***************
> *** 149,175 ****
> char *msg;
> PGresult *res = NULL;
> PGconn *conn = NULL;
> ! text *result_text;
> ! char *curname = GET_STR(PG_GETARG_TEXT_P(0));
> ! char *sql = GET_STR(PG_GETARG_TEXT_P(1));
> StringInfo str = makeStringInfo();
>
> ! if (persistent_conn != NULL)
> conn = persistent_conn;
> ! else
> ! elog(ERROR, "dblink_open: no connection available");
>
> res = PQexec(conn, "BEGIN");
> if (PQresultStatus(res) != PGRES_COMMAND_OK)
> ! {
> ! msg = pstrdup(PQerrorMessage(conn));
> ! PQclear(res);
> !
> ! PQfinish(conn);
> ! persistent_conn = NULL;
>
> - elog(ERROR, "dblink_open: begin error: %s", msg);
> - }
> PQclear(res);
>
> appendStringInfo(str, "DECLARE %s CURSOR FOR %s", curname, sql);
> --- 260,294 ----
> char *msg;
> PGresult *res = NULL;
> PGconn *conn = NULL;
> ! char *curname = NULL;
> ! char *sql = NULL;
> ! char *conname = NULL;
> StringInfo str = makeStringInfo();
> + remoteConn *rcon = NULL;
>
> ! if(PG_NARGS() == 2)
> ! {
> ! curname = GET_STR(PG_GETARG_TEXT_P(0));
> ! sql = GET_STR(PG_GETARG_TEXT_P(1));
> conn = persistent_conn;
> ! }
> ! else if(PG_NARGS() == 3)
> ! {
> ! conname = GET_STR(PG_GETARG_TEXT_P(0));
> ! curname = GET_STR(PG_GETARG_TEXT_P(1));
> ! sql = GET_STR(PG_GETARG_TEXT_P(2));
> ! rcon = getConnectionByName(conname);
> ! if (rcon)
> ! conn = rcon->con;
> ! }
> !
> ! if (!conn)
> ! DBLINK_CONN_NOT_AVAIL("dblink_open");
>
> res = PQexec(conn, "BEGIN");
> if (PQresultStatus(res) != PGRES_COMMAND_OK)
> ! DBLINK_RES_ERROR("dblink_open", "begin error");
>
> PQclear(res);
>
> appendStringInfo(str, "DECLARE %s CURSOR FOR %s", curname, sql);
> ***************
> *** 177,195 ****
> if (!res ||
> (PQresultStatus(res) != PGRES_COMMAND_OK &&
> PQresultStatus(res) != PGRES_TUPLES_OK))
> ! {
> ! msg = pstrdup(PQerrorMessage(conn));
> !
> ! PQclear(res);
>
> ! PQfinish(conn);
> ! persistent_conn = NULL;
> !
> ! elog(ERROR, "dblink: sql error: %s", msg);
> ! }
>
> ! result_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum("OK")));
> ! PG_RETURN_TEXT_P(result_text);
> }
>
> /*
> --- 296,306 ----
> if (!res ||
> (PQresultStatus(res) != PGRES_COMMAND_OK &&
> PQresultStatus(res) != PGRES_TUPLES_OK))
> ! DBLINK_RES_ERROR("dblink_open", "sql error");
>
> ! PQclear(res);
>
> ! PG_RETURN_TEXT_P(GET_TEXT("OK"));
> }
>
> /*
> ***************
> *** 201,249 ****
> {
> PGconn *conn = NULL;
> PGresult *res = NULL;
> ! char *curname = GET_STR(PG_GETARG_TEXT_P(0));
> StringInfo str = makeStringInfo();
> - text *result_text;
> char *msg;
>
> ! if (persistent_conn != NULL)
> conn = persistent_conn;
> ! else
> ! elog(ERROR, "dblink_close: no connection available");
>
> appendStringInfo(str, "CLOSE %s", curname);
>
> /* close the cursor */
> res = PQexec(conn, str->data);
> if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
> ! {
> ! msg = pstrdup(PQerrorMessage(conn));
> ! PQclear(res);
> !
> ! PQfinish(persistent_conn);
> ! persistent_conn = NULL;
> !
> ! elog(ERROR, "dblink_close: sql error: %s", msg);
> ! }
>
> PQclear(res);
>
> /* commit the transaction */
> res = PQexec(conn, "COMMIT");
> if (PQresultStatus(res) != PGRES_COMMAND_OK)
> ! {
> ! msg = pstrdup(PQerrorMessage(conn));
> ! PQclear(res);
> !
> ! PQfinish(persistent_conn);
> ! persistent_conn = NULL;
>
> - elog(ERROR, "dblink_close: commit error: %s", msg);
> - }
> PQclear(res);
>
> ! result_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum("OK")));
> ! PG_RETURN_TEXT_P(result_text);
> }
>
> /*
> --- 312,357 ----
> {
> PGconn *conn = NULL;
> PGresult *res = NULL;
> ! char *curname = NULL;
> ! char *conname = NULL;
> StringInfo str = makeStringInfo();
> char *msg;
> + remoteConn *rcon = NULL;
>
> ! if (PG_NARGS() == 1)
> ! {
> ! curname = GET_STR(PG_GETARG_TEXT_P(0));
> conn = persistent_conn;
> ! }
> ! else if (PG_NARGS()==2)
> ! {
> ! conname = GET_STR(PG_GETARG_TEXT_P(0));
> ! curname = GET_STR(PG_GETARG_TEXT_P(1));
> ! rcon = getConnectionByName(conname);
> ! if(rcon)
> ! conn = rcon->con;
> ! }
> !
> ! if (!conn)
> ! DBLINK_CONN_NOT_AVAIL("dblink_close");
>
> appendStringInfo(str, "CLOSE %s", curname);
>
> /* close the cursor */
> res = PQexec(conn, str->data);
> if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
> ! DBLINK_RES_ERROR("dblink_close", "sql error");
>
> PQclear(res);
>
> /* commit the transaction */
> res = PQexec(conn, "COMMIT");
> if (PQresultStatus(res) != PGRES_COMMAND_OK)
> ! DBLINK_RES_ERROR("dblink_close", "commit error");
>
> PQclear(res);
>
> ! PG_RETURN_TEXT_P(GET_TEXT("OK"));
> }
>
> /*
> ***************
> *** 262,267 ****
> --- 370,377 ----
> char *msg;
> PGresult *res = NULL;
> MemoryContext oldcontext;
> + char *conname = NULL;
> + remoteConn *rcon=NULL;
>
> /* stuff done only on the first call of the function */
> if (SRF_IS_FIRSTCALL())
> ***************
> *** 271,278 ****
> Oid funcid = fcinfo->flinfo->fn_oid;
> PGconn *conn = NULL;
> StringInfo str = makeStringInfo();
> ! char *curname = GET_STR(PG_GETARG_TEXT_P(0));
> ! int howmany = PG_GETARG_INT32(1);
>
> /* create a function context for cross-call persistence */
> funcctx = SRF_FIRSTCALL_INIT();
> --- 381,408 ----
> Oid funcid = fcinfo->flinfo->fn_oid;
> PGconn *conn = NULL;
> StringInfo str = makeStringInfo();
> ! char *curname = NULL;
> ! int howmany = 0;
> !
> ! if (PG_NARGS() == 3)
> ! {
> ! conname = GET_STR(PG_GETARG_TEXT_P(0));
> ! curname = GET_STR(PG_GETARG_TEXT_P(1));
> ! howmany = PG_GETARG_INT32(2);
> !
> ! rcon = getConnectionByName(conname);
> ! if(rcon)
> ! conn = rcon->con;
> ! }
> ! else if (PG_NARGS() == 2)
> ! {
> ! curname = GET_STR(PG_GETARG_TEXT_P(0));
> ! howmany = PG_GETARG_INT32(1);
> ! conn = persistent_conn;
> ! }
> !
> ! if(!conn)
> ! DBLINK_CONN_NOT_AVAIL("dblink_fetch");
>
> /* create a function context for cross-call persistence */
> funcctx = SRF_FIRSTCALL_INIT();
> ***************
> *** 283,293 ****
> */
> oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
>
> - if (persistent_conn != NULL)
> - conn = persistent_conn;
> - else
> - elog(ERROR, "dblink_fetch: no connection available");
> -
> appendStringInfo(str, "FETCH %d FROM %s", howmany, curname);
>
> res = PQexec(conn, str->data);
> --- 413,418 ----
> ***************
> *** 295,313 ****
> (PQresultStatus(res) != PGRES_COMMAND_OK &&
> PQresultStatus(res) != PGRES_TUPLES_OK))
> {
> ! msg = pstrdup(PQerrorMessage(conn));
> ! PQclear(res);
> !
> ! PQfinish(persistent_conn);
> ! persistent_conn = NULL;
> !
> ! elog(ERROR, "dblink_fetch: sql error: %s", msg);
> }
> else if (PQresultStatus(res) == PGRES_COMMAND_OK)
> {
> /* cursor does not exist - closed already or bad name */
> PQclear(res);
> ! elog(ERROR, "dblink_fetch: cursor %s does not exist", curname);
> }
>
> funcctx->max_calls = PQntuples(res);
> --- 420,432 ----
> (PQresultStatus(res) != PGRES_COMMAND_OK &&
> PQresultStatus(res) != PGRES_TUPLES_OK))
> {
> ! DBLINK_RES_ERROR("dblink_fetch", "sql error");
> }
> else if (PQresultStatus(res) == PGRES_COMMAND_OK)
> {
> /* cursor does not exist - closed already or bad name */
> PQclear(res);
> ! elog(ERROR, "dblink_fetch: cursor not found: %s", curname);
> }
>
> funcctx->max_calls = PQntuples(res);
> ***************
> *** 382,389 ****
> SRF_RETURN_NEXT(funcctx, result);
> }
> else
> - /* do when there is no more left */
> {
> PQclear(res);
> SRF_RETURN_DONE(funcctx);
> }
> --- 501,508 ----
> SRF_RETURN_NEXT(funcctx, result);
> }
> else
> {
> + /* do when there is no more left */
> PQclear(res);
> SRF_RETURN_DONE(funcctx);
> }
> ***************
> *** 407,412 ****
> --- 526,532 ----
> bool is_sql_cmd = false;
> char *sql_cmd_status = NULL;
> MemoryContext oldcontext;
> + bool freeconn = true;
>
> /* stuff done only on the first call of the function */
> if (SRF_IS_FIRSTCALL())
> ***************
> *** 417,422 ****
> --- 537,544 ----
> PGconn *conn = NULL;
> char *connstr = NULL;
> char *sql = NULL;
> + char *conname = NULL;
> + remoteConn *rcon=NULL;
>
> /* create a function context for cross-call persistence */
> funcctx = SRF_FIRSTCALL_INIT();
> ***************
> *** 427,496 ****
> */
> oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
>
> ! if (fcinfo->nargs == 2)
> {
> ! connstr = GET_STR(PG_GETARG_TEXT_P(0));
> sql = GET_STR(PG_GETARG_TEXT_P(1));
> -
> - conn = PQconnectdb(connstr);
> - if (PQstatus(conn) == CONNECTION_BAD)
> - {
> - msg = pstrdup(PQerrorMessage(conn));
> - PQfinish(conn);
> - elog(ERROR, "dblink: connection error: %s", msg);
> - }
> }
> ! else if (fcinfo->nargs == 1)
> {
> sql = GET_STR(PG_GETARG_TEXT_P(0));
> -
> - if (persistent_conn != NULL)
> - conn = persistent_conn;
> - else
> - elog(ERROR, "dblink: no connection available");
> }
> else
> elog(ERROR, "dblink: wrong number of arguments");
>
> res = PQexec(conn, sql);
> if (!res || (PQresultStatus(res) != PGRES_COMMAND_OK && PQresultStatus(res) != PGRES_TUPLES_OK))
> {
> ! msg = pstrdup(PQerrorMessage(conn));
> ! PQclear(res);
> ! PQfinish(conn);
> ! if (fcinfo->nargs == 1)
> ! persistent_conn = NULL;
>
> ! elog(ERROR, "dblink: sql error: %s", msg);
> }
> else
> ! {
> ! if (PQresultStatus(res) == PGRES_COMMAND_OK)
> ! {
> ! is_sql_cmd = true;
>
> ! /* need a tuple descriptor representing one TEXT column */
> ! tupdesc = CreateTemplateTupleDesc(1, false);
> ! TupleDescInitEntry(tupdesc, (AttrNumber) 1, "status",
> ! TEXTOID, -1, 0, false);
> !
> ! /*
> ! * and save a copy of the command status string to return
> ! * as our result tuple
> ! */
> ! sql_cmd_status = PQcmdStatus(res);
> ! funcctx->max_calls = 1;
> ! }
> ! else
> ! funcctx->max_calls = PQntuples(res);
> !
> ! /* got results, keep track of them */
> ! funcctx->user_fctx = res;
>
> ! /* if needed, close the connection to the database and cleanup */
> ! if (fcinfo->nargs == 2)
> ! PQfinish(conn);
> ! }
>
> /* fast track when no results */
> if (funcctx->max_calls < 1)
> --- 549,599 ----
> */
> oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
>
> ! if (PG_NARGS() == 2)
> {
> ! DBLINK_GET_CONN("dblink");
> sql = GET_STR(PG_GETARG_TEXT_P(1));
> }
> ! else if (PG_NARGS() == 1)
> {
> + conn = persistent_conn;
> sql = GET_STR(PG_GETARG_TEXT_P(0));
> }
> else
> elog(ERROR, "dblink: wrong number of arguments");
>
> + if(!conn)
> + DBLINK_CONN_NOT_AVAIL("dblink_record");
> +
> res = PQexec(conn, sql);
> if (!res || (PQresultStatus(res) != PGRES_COMMAND_OK && PQresultStatus(res) != PGRES_TUPLES_OK))
> + DBLINK_RES_ERROR("dblink", "sql error");
> +
> + if (PQresultStatus(res) == PGRES_COMMAND_OK)
> {
> ! is_sql_cmd = true;
> !
> ! /* need a tuple descriptor representing one TEXT column */
> ! tupdesc = CreateTemplateTupleDesc(1, false);
> ! TupleDescInitEntry(tupdesc, (AttrNumber) 1, "status",
> ! TEXTOID, -1, 0, false);
>
> ! /*
> ! * and save a copy of the command status string to return
> ! * as our result tuple
> ! */
> ! sql_cmd_status = PQcmdStatus(res);
> ! funcctx->max_calls = 1;
> }
> else
> ! funcctx->max_calls = PQntuples(res);
>
> ! /* got results, keep track of them */
> ! funcctx->user_fctx = res;
>
> ! /* if needed, close the connection to the database and cleanup */
> ! if (freeconn && PG_NARGS() == 2)
> ! PQfinish(conn);
>
> /* fast track when no results */
> if (funcctx->max_calls < 1)
> ***************
> *** 571,578 ****
> SRF_RETURN_NEXT(funcctx, result);
> }
> else
> - /* do when there is no more left */
> {
> PQclear(res);
> SRF_RETURN_DONE(funcctx);
> }
> --- 674,681 ----
> SRF_RETURN_NEXT(funcctx, result);
> }
> else
> {
> + /* do when there is no more left */
> PQclear(res);
> SRF_RETURN_DONE(funcctx);
> }
> ***************
> *** 587,858 ****
> {
> char *msg;
> PGresult *res = NULL;
> ! char *sql_cmd_status = NULL;
> TupleDesc tupdesc = NULL;
> - text *result_text;
> PGconn *conn = NULL;
> char *connstr = NULL;
> char *sql = NULL;
>
> ! if (fcinfo->nargs == 2)
> {
> ! connstr = GET_STR(PG_GETARG_TEXT_P(0));
> sql = GET_STR(PG_GETARG_TEXT_P(1));
> -
> - conn = PQconnectdb(connstr);
> - if (PQstatus(conn) == CONNECTION_BAD)
> - {
> - msg = pstrdup(PQerrorMessage(conn));
> - PQfinish(conn);
> - elog(ERROR, "dblink_exec: connection error: %s", msg);
> - }
> }
> ! else if (fcinfo->nargs == 1)
> {
> sql = GET_STR(PG_GETARG_TEXT_P(0));
> -
> - if (persistent_conn != NULL)
> - conn = persistent_conn;
> - else
> - elog(ERROR, "dblink_exec: no connection available");
> }
> else
> elog(ERROR, "dblink_exec: wrong number of arguments");
>
>
> res = PQexec(conn, sql);
> ! if (!res || (PQresultStatus(res) != PGRES_COMMAND_OK && PQresultStatus(res) != PGRES_TUPLES_OK))
> {
> ! msg = pstrdup(PQerrorMessage(conn));
> ! PQclear(res);
> ! PQfinish(conn);
> ! if (fcinfo->nargs == 1)
> ! persistent_conn = NULL;
>
> ! elog(ERROR, "dblink_exec: sql error: %s", msg);
> }
> else
> ! {
> ! if (PQresultStatus(res) == PGRES_COMMAND_OK)
> ! {
> ! /* need a tuple descriptor representing one TEXT column */
> ! tupdesc = CreateTemplateTupleDesc(1, false);
> ! TupleDescInitEntry(tupdesc, (AttrNumber) 1, "status",
> ! TEXTOID, -1, 0, false);
>
> - /*
> - * and save a copy of the command status string to return as
> - * our result tuple
> - */
> - sql_cmd_status = PQcmdStatus(res);
> - }
> - else
> - elog(ERROR, "dblink_exec: queries returning results not allowed");
> - }
> PQclear(res);
>
> /* if needed, close the connection to the database and cleanup */
> ! if (fcinfo->nargs == 2)
> PQfinish(conn);
>
> ! result_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(sql_cmd_status)));
> ! PG_RETURN_TEXT_P(result_text);
> }
>
> - /*
> - * Note: this original version of dblink is DEPRECATED;
> - * it *will* be removed in favor of the new version on next release
> - */
> - PG_FUNCTION_INFO_V1(dblink);
> - Datum
> - dblink(PG_FUNCTION_ARGS)
> - {
> - PGconn *conn = NULL;
> - PGresult *res = NULL;
> - dblink_results *results;
> - char *optstr;
> - char *sqlstatement;
> - char *execstatement;
> - char *msg;
> - int ntuples = 0;
> - ReturnSetInfo *rsi;
> -
> - if (fcinfo->resultinfo == NULL || !IsA(fcinfo->resultinfo, ReturnSetInfo))
> - elog(ERROR, "dblink: function called in context that does not accept a set result");
> -
> - optstr = DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(PG_GETARG_TEXT_P(0))));
> - sqlstatement = DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(PG_GETARG_TEXT_P(1))));
> -
> - if (fcinfo->flinfo->fn_extra == NULL)
> - {
> -
> - conn = PQconnectdb(optstr);
> - if (PQstatus(conn) == CONNECTION_BAD)
> - {
> - msg = pstrdup(PQerrorMessage(conn));
> - PQfinish(conn);
> - elog(ERROR, "dblink: connection error: %s", msg);
> - }
> -
> - execstatement = (char *) palloc(strlen(sqlstatement) + 1);
> - if (execstatement != NULL)
> - {
> - strcpy(execstatement, sqlstatement);
> - strcat(execstatement, "\0");
> - }
> - else
> - elog(ERROR, "dblink: insufficient memory");
> -
> - res = PQexec(conn, execstatement);
> - if (!res || (PQresultStatus(res) != PGRES_COMMAND_OK && PQresultStatus(res) != PGRES_TUPLES_OK))
> - {
> - msg = pstrdup(PQerrorMessage(conn));
> - PQclear(res);
> - PQfinish(conn);
> - elog(ERROR, "dblink: sql error: %s", msg);
> - }
> - else
> - {
> - /*
> - * got results, start fetching them
> - */
> - ntuples = PQntuples(res);
> -
> - /*
> - * increment resource index
> - */
> - res_id_index++;
> -
> - results = init_dblink_results(fcinfo->flinfo->fn_mcxt);
> - results->tup_num = 0;
> - results->res_id_index = res_id_index;
> - results->res = res;
> -
> - /*
> - * Append node to res_id to hold pointer to results. Needed by
> - * dblink_tok to access the data
> - */
> - append_res_ptr(results);
> -
> - /*
> - * save pointer to results for the next function manager call
> - */
> - fcinfo->flinfo->fn_extra = (void *) results;
> -
> - /* close the connection to the database and cleanup */
> - PQfinish(conn);
> -
> - rsi = (ReturnSetInfo *) fcinfo->resultinfo;
> - rsi->isDone = ExprMultipleResult;
> -
> - PG_RETURN_INT32(res_id_index);
> - }
> - }
> - else
> - {
> - /*
> - * check for more results
> - */
> - results = fcinfo->flinfo->fn_extra;
> -
> - results->tup_num++;
> - res_id_index = results->res_id_index;
> - ntuples = PQntuples(results->res);
> -
> - if (results->tup_num < ntuples)
> - {
> - /*
> - * fetch them if available
> - */
> -
> - rsi = (ReturnSetInfo *) fcinfo->resultinfo;
> - rsi->isDone = ExprMultipleResult;
> -
> - PG_RETURN_INT32(res_id_index);
> - }
> - else
> - {
> - /*
> - * or if no more, clean things up
> - */
> - results = fcinfo->flinfo->fn_extra;
> -
> - remove_res_ptr(results);
> - PQclear(results->res);
> - pfree(results);
> - fcinfo->flinfo->fn_extra = NULL;
> -
> - rsi = (ReturnSetInfo *) fcinfo->resultinfo;
> - rsi->isDone = ExprEndResult;
> -
> - PG_RETURN_NULL();
> - }
> - }
> - PG_RETURN_NULL();
> - }
> -
> - /*
> - * Note: dblink_tok is DEPRECATED;
> - * it *will* be removed in favor of the new version on next release
> - *
> - * dblink_tok
> - * parse dblink output string
> - * return fldnum item (0 based)
> - * based on provided field separator
> - */
> - PG_FUNCTION_INFO_V1(dblink_tok);
> - Datum
> - dblink_tok(PG_FUNCTION_ARGS)
> - {
> - dblink_results *results;
> - int fldnum;
> - text *result_text;
> - char *result;
> - int nfields = 0;
> - int text_len = 0;
> -
> - results = get_res_ptr(PG_GETARG_INT32(0));
> - if (results == NULL)
> - {
> - if (res_id != NIL)
> - {
> - freeList(res_id);
> - res_id = NIL;
> - res_id_index = 0;
> - }
> -
> - elog(ERROR, "dblink_tok: function called with invalid resource id");
> - }
> -
> - fldnum = PG_GETARG_INT32(1);
> - if (fldnum < 0)
> - elog(ERROR, "dblink_tok: field number < 0 not permitted");
> -
> - nfields = PQnfields(results->res);
> - if (fldnum > (nfields - 1))
> - elog(ERROR, "dblink_tok: field number %d does not exist", fldnum);
> -
> - if (PQgetisnull(results->res, results->tup_num, fldnum) == 1)
> - PG_RETURN_NULL();
> - else
> - {
> - text_len = PQgetlength(results->res, results->tup_num, fldnum);
> -
> - result = (char *) palloc(text_len + 1);
> -
> - if (result != NULL)
> - {
> - strcpy(result, PQgetvalue(results->res, results->tup_num, fldnum));
> - strcat(result, "\0");
> - }
> - else
> - elog(ERROR, "dblink: insufficient memory");
> -
> - result_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(result)));
> -
> - PG_RETURN_TEXT_P(result_text);
> - }
> - }
>
> /*
> * dblink_get_pkey
> --- 690,751 ----
> {
> char *msg;
> PGresult *res = NULL;
> ! text *sql_cmd_status = NULL;
> TupleDesc tupdesc = NULL;
> PGconn *conn = NULL;
> char *connstr = NULL;
> char *sql = NULL;
> + char *conname = NULL;
> + remoteConn *rcon=NULL;
> + bool freeconn = true;
>
> ! if (PG_NARGS() == 2)
> {
> ! DBLINK_GET_CONN("dblink_exec");
> sql = GET_STR(PG_GETARG_TEXT_P(1));
> }
> ! else if (PG_NARGS() == 1)
> {
> + conn = persistent_conn;
> sql = GET_STR(PG_GETARG_TEXT_P(0));
> }
> else
> elog(ERROR, "dblink_exec: wrong number of arguments");
>
> + if(!conn)
> + DBLINK_CONN_NOT_AVAIL("dblink_exec");
>
> res = PQexec(conn, sql);
> ! if (!res ||
> ! (PQresultStatus(res) != PGRES_COMMAND_OK &&
> ! PQresultStatus(res) != PGRES_TUPLES_OK))
> ! DBLINK_RES_ERROR("dblink_exec", "sql error");
> !
> ! if (PQresultStatus(res) == PGRES_COMMAND_OK)
> {
> ! /* need a tuple descriptor representing one TEXT column */
> ! tupdesc = CreateTemplateTupleDesc(1, false);
> ! TupleDescInitEntry(tupdesc, (AttrNumber) 1, "status",
> ! TEXTOID, -1, 0, false);
>
> ! /*
> ! * and save a copy of the command status string to return as
> ! * our result tuple
> ! */
> ! sql_cmd_status = GET_TEXT(PQcmdStatus(res));
> }
> else
> ! elog(ERROR, "dblink_exec: queries returning results not allowed");
>
> PQclear(res);
>
> /* if needed, close the connection to the database and cleanup */
> ! if (freeconn && fcinfo->nargs == 2)
> PQfinish(conn);
>
> ! PG_RETURN_TEXT_P(sql_cmd_status);
> }
>
>
> /*
> * dblink_get_pkey
> ***************
> *** 927,933 ****
> funcctx->user_fctx = results;
> }
> else
> ! /* fast track when no results */
> SRF_RETURN_DONE(funcctx);
>
> MemoryContextSwitchTo(oldcontext);
> --- 820,826 ----
> funcctx->user_fctx = results;
> }
> else
> ! /* fast track when no results */
> SRF_RETURN_DONE(funcctx);
>
> MemoryContextSwitchTo(oldcontext);
> ***************
> *** 969,1005 ****
> SRF_RETURN_NEXT(funcctx, result);
> }
> else
> - /* do when there is no more left */
> - SRF_RETURN_DONE(funcctx);
> - }
> -
> - /*
> - * Note: dblink_last_oid is DEPRECATED;
> - * it *will* be removed on next release
> - *
> - * dblink_last_oid
> - * return last inserted oid
> - */
> - PG_FUNCTION_INFO_V1(dblink_last_oid);
> - Datum
> - dblink_last_oid(PG_FUNCTION_ARGS)
> - {
> - dblink_results *results;
> -
> - results = get_res_ptr(PG_GETARG_INT32(0));
> - if (results == NULL)
> {
> ! if (res_id != NIL)
> ! {
> ! freeList(res_id);
> ! res_id = NIL;
> ! res_id_index = 0;
> ! }
> !
> ! elog(ERROR, "dblink_tok: function called with invalid resource id");
> }
> -
> - PG_RETURN_OID(PQoidValue(results->res));
> }
>
>
> --- 862,871 ----
> SRF_RETURN_NEXT(funcctx, result);
> }
> else
> {
> ! /* do when there is no more left */
> ! SRF_RETURN_DONE(funcctx);
> }
> }
>
>
> ***************
> *** 1047,1053 ****
> int i;
> char *ptr;
> char *sql;
> - text *sql_text;
> int16 typlen;
> bool typbyval;
> char typalign;
> --- 913,918 ----
> ***************
> *** 1143,1156 ****
> sql = get_sql_insert(relid, pkattnums, pknumatts, src_pkattvals, tgt_pkattvals);
>
> /*
> - * Make it into TEXT for return to the client
> - */
> - sql_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(sql)));
> -
> - /*
> * And send it
> */
> ! PG_RETURN_TEXT_P(sql_text);
> }
>
>
> --- 1008,1016 ----
> sql = get_sql_insert(relid, pkattnums, pknumatts, src_pkattvals, tgt_pkattvals);
>
> /*
> * And send it
> */
> ! PG_RETURN_TEXT_P(GET_TEXT(sql));
> }
>
>
> ***************
> *** 1186,1192 ****
> int i;
> char *ptr;
> char *sql;
> - text *sql_text;
> int16 typlen;
> bool typbyval;
> char typalign;
> --- 1046,1051 ----
> ***************
> *** 1251,1264 ****
> sql = get_sql_delete(relid, pkattnums, pknumatts, tgt_pkattvals);
>
> /*
> - * Make it into TEXT for return to the client
> - */
> - sql_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(sql)));
> -
> - /*
> * And send it
> */
> ! PG_RETURN_TEXT_P(sql_text);
> }
>
>
> --- 1110,1118 ----
> sql = get_sql_delete(relid, pkattnums, pknumatts, tgt_pkattvals);
>
> /*
> * And send it
> */
> ! PG_RETURN_TEXT_P(GET_TEXT(sql));
> }
>
>
> ***************
> *** 1303,1309 ****
> int i;
> char *ptr;
> char *sql;
> - text *sql_text;
> int16 typlen;
> bool typbyval;
> char typalign;
> --- 1157,1162 ----
> ***************
> *** 1399,1412 ****
> sql = get_sql_update(relid, pkattnums, pknumatts, src_pkattvals, tgt_pkattvals);
>
> /*
> - * Make it into TEXT for return to the client
> - */
> - sql_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(sql)));
> -
> - /*
> * And send it
> */
> ! PG_RETURN_TEXT_P(sql_text);
> }
>
> /*
> --- 1252,1260 ----
> sql = get_sql_update(relid, pkattnums, pknumatts, src_pkattvals, tgt_pkattvals);
>
> /*
> * And send it
> */
> ! PG_RETURN_TEXT_P(GET_TEXT(sql));
> }
>
> /*
> ***************
> *** 1419,1428 ****
> Datum
> dblink_current_query(PG_FUNCTION_ARGS)
> {
> ! text *result_text;
> !
> ! result_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(debug_query_string)));
> ! PG_RETURN_TEXT_P(result_text);
> }
>
>
> --- 1267,1273 ----
> Datum
> dblink_current_query(PG_FUNCTION_ARGS)
> {
> ! PG_RETURN_TEXT_P(GET_TEXT(debug_query_string));
> }
>
>
> ***************
> *** 1432,1460 ****
>
>
> /*
> - * init_dblink_results
> - * - create an empty dblink_results data structure
> - */
> - static dblink_results *
> - init_dblink_results(MemoryContext fn_mcxt)
> - {
> - MemoryContext oldcontext;
> - dblink_results *retval;
> -
> - oldcontext = MemoryContextSwitchTo(fn_mcxt);
> -
> - retval = (dblink_results *) palloc0(sizeof(dblink_results));
> -
> - retval->tup_num = -1;
> - retval->res_id_index = -1;
> - retval->res = NULL;
> -
> - MemoryContextSwitchTo(oldcontext);
> -
> - return retval;
> - }
> -
> - /*
> * get_pkey_attnames
> *
> * Get the primary key attnames for the given relation.
> --- 1277,1282 ----
> ***************
> *** 1492,1498 ****
> /* we're only interested if it is the primary key */
> if (index->indisprimary == TRUE)
> {
> ! *numatts = index->indnatts;
> if (*numatts > 0)
> {
> result = (char **) palloc(*numatts * sizeof(char *));
> --- 1314,1323 ----
> /* we're only interested if it is the primary key */
> if (index->indisprimary == TRUE)
> {
> ! i = 0;
> ! while (index->indkey[i++] != 0)
> ! (*numatts)++;
> !
> if (*numatts > 0)
> {
> result = (char **) palloc(*numatts * sizeof(char *));
> ***************
> *** 1911,1962 ****
> return relid;
> }
>
> - static dblink_results *
> - get_res_ptr(int32 res_id_index)
> - {
> - List *ptr;
> -
> - /*
> - * short circuit empty list
> - */
> - if (res_id == NIL)
> - return NULL;
> -
> - /*
> - * OK, should be good to go
> - */
> - foreach(ptr, res_id)
> - {
> - dblink_results *this_res_id = (dblink_results *) lfirst(ptr);
> -
> - if (this_res_id->res_id_index == res_id_index)
> - return this_res_id;
> - }
> - return NULL;
> - }
> -
> - /*
> - * Add node to global List res_id
> - */
> - static void
> - append_res_ptr(dblink_results * results)
> - {
> - res_id = lappend(res_id, results);
> - }
> -
> - /*
> - * Remove node from global List
> - * using res_id_index
> - */
> - static void
> - remove_res_ptr(dblink_results * results)
> - {
> - res_id = lremove(results, res_id);
> -
> - if (res_id == NIL)
> - res_id_index = 0;
> - }
> -
> static TupleDesc
> pgresultGetTupleDesc(PGresult *res)
> {
> --- 1736,1741 ----
> ***************
> *** 2042,2045 ****
> --- 1821,1912 ----
> ReleaseSysCache(tp);
>
> return result;
> + }
> +
> +
> + static remoteConn *
> + getConnectionByName(const char *name)
> + {
> + remoteConnHashEnt *hentry;
> + char key[NAMEDATALEN];
> +
> + if(!remoteConnHash)
> + remoteConnHash=createConnHash();
> +
> + MemSet(key, 0, NAMEDATALEN);
> + snprintf(key, NAMEDATALEN - 1, "%s", name);
> + hentry = (remoteConnHashEnt*) hash_search(remoteConnHash,
> + key, HASH_FIND, NULL);
> +
> + if(hentry)
> + return(hentry->rcon);
> +
> + return(NULL);
> + }
> +
> + static HTAB *
> + createConnHash(void)
> + {
> + HASHCTL ctl;
> + HTAB *ptr;
> +
> + ctl.keysize = NAMEDATALEN;
> + ctl.entrysize = sizeof(remoteConnHashEnt);
> +
> + ptr=hash_create("Remote Con hash", NUMCONN, &ctl, HASH_ELEM);
> +
> + if(!ptr)
> + elog(ERROR,"Can not create connections hash table. Out of memory");
> +
> + return(ptr);
> + }
> +
> + static bool
> + createNewConnection(const char *name, remoteConn *con)
> + {
> + remoteConnHashEnt *hentry;
> + bool found;
> + char key[NAMEDATALEN];
> +
> + if(!remoteConnHash)
> + remoteConnHash=createConnHash();
> +
> + MemSet(key, 0, NAMEDATALEN);
> + snprintf(key, NAMEDATALEN - 1, "%s", name);
> + hentry = (remoteConnHashEnt *) hash_search(remoteConnHash, key,
> + HASH_ENTER, &found);
> +
> + if(!hentry)
> + elog(ERROR, "failed to create connection");
> +
> + if(found)
> + {
> + elog(NOTICE, "cannot use a connection name more than once");
> + return false;
> + }
> +
> + hentry->rcon = con;
> + strncpy(hentry->name, name, NAMEDATALEN - 1);
> +
> + return true;
> + }
> +
> + static void
> + deleteConnection(const char *name)
> + {
> + remoteConnHashEnt *hentry;
> + bool found;
> + char key[NAMEDATALEN];
> +
> + if(!remoteConnHash)
> + remoteConnHash=createConnHash();
> +
> + MemSet(key, 0, NAMEDATALEN);
> + snprintf(key, NAMEDATALEN - 1, "%s", name);
> +
> + hentry = (remoteConnHashEnt *) hash_search(remoteConnHash,
> + key, HASH_REMOVE, &found);
> +
> + if(!hentry)
> + elog(WARNING,"Trying to delete a connection that does not exist");
> }
> Index: contrib/dblink/dblink.h
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/dblink.h,v
> retrieving revision 1.9
> diff -c -r1.9 dblink.h
> *** contrib/dblink/dblink.h 21 Oct 2002 18:57:34 -0000 1.9
> --- contrib/dblink/dblink.h 15 Jun 2003 04:18:08 -0000
> ***************
> *** 4,11 ****
> * Functions returning results from a remote database
> *
> * Joe Conway <mail@joeconway.com>
> *
> ! * Copyright (c) 2001, 2002 by PostgreSQL Global Development Group
> * ALL RIGHTS RESERVED;
> *
> * Permission to use, copy, modify, and distribute this software and its
> --- 4,14 ----
> * Functions returning results from a remote database
> *
> * Joe Conway <mail@joeconway.com>
> + * And contributors:
> + * Darko Prenosil <Darko.Prenosil@finteh.hr>
> + * Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
> *
> ! * Copyright (c) 2001, 2002, 2003 by PostgreSQL Global Development Group
> * ALL RIGHTS RESERVED;
> *
> * Permission to use, copy, modify, and distribute this software and its
> ***************
> *** 31,65 ****
> #define DBLINK_H
>
> /*
> - * This struct holds the results of the remote query.
> - * Use fn_extra to hold a pointer to it across calls
> - */
> - typedef struct
> - {
> - /*
> - * last tuple number accessed
> - */
> - int tup_num;
> -
> - /*
> - * resource index number for this context
> - */
> - int res_id_index;
> -
> - /*
> - * the actual query results
> - */
> - PGresult *res;
> - } dblink_results;
> -
> - /*
> * External declarations
> */
> - /* deprecated */
> - extern Datum dblink(PG_FUNCTION_ARGS);
> - extern Datum dblink_tok(PG_FUNCTION_ARGS);
> -
> - /* supported */
> extern Datum dblink_connect(PG_FUNCTION_ARGS);
> extern Datum dblink_disconnect(PG_FUNCTION_ARGS);
> extern Datum dblink_open(PG_FUNCTION_ARGS);
> --- 34,41 ----
> ***************
> *** 68,74 ****
> extern Datum dblink_record(PG_FUNCTION_ARGS);
> extern Datum dblink_exec(PG_FUNCTION_ARGS);
> extern Datum dblink_get_pkey(PG_FUNCTION_ARGS);
> - extern Datum dblink_last_oid(PG_FUNCTION_ARGS);
> 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);
> --- 44,49 ----
> Index: contrib/dblink/dblink.sql.in
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/dblink.sql.in,v
> retrieving revision 1.7
> diff -c -r1.7 dblink.sql.in
> *** contrib/dblink/dblink.sql.in 18 Oct 2002 18:41:19 -0000 1.7
> --- contrib/dblink/dblink.sql.in 15 Jun 2003 03:59:54 -0000
> ***************
> *** 1,50 ****
> - --
> - -- Uncomment the following commented lines to use original DEPRECATED functions
> - --
> - --CREATE OR REPLACE FUNCTION dblink (text,text)
> - --RETURNS setof int
> - --AS 'MODULE_PATHNAME','dblink'
> - --LANGUAGE 'C' WITH (isstrict);
> - --CREATE OR REPLACE FUNCTION dblink_tok (int,int)
> - --RETURNS text
> - --AS 'MODULE_PATHNAME','dblink_tok'
> - --LANGUAGE 'C' WITH (isstrict);
> - --CREATE OR REPLACE FUNCTION dblink_last_oid (int)
> - --RETURNS oid
> - --AS 'MODULE_PATHNAME','dblink_last_oid'
> - --LANGUAGE 'C' WITH (isstrict);
> -
> CREATE OR REPLACE FUNCTION dblink_connect (text)
> RETURNS text
> AS 'MODULE_PATHNAME','dblink_connect'
> LANGUAGE 'C' WITH (isstrict);
>
> CREATE OR REPLACE FUNCTION dblink_disconnect ()
> RETURNS text
> AS 'MODULE_PATHNAME','dblink_disconnect'
> LANGUAGE 'C' WITH (isstrict);
>
> CREATE OR REPLACE FUNCTION dblink_open (text,text)
> RETURNS text
> AS 'MODULE_PATHNAME','dblink_open'
> LANGUAGE 'C' WITH (isstrict);
>
> CREATE OR REPLACE FUNCTION dblink_fetch (text,int)
> RETURNS setof record
> AS 'MODULE_PATHNAME','dblink_fetch'
> LANGUAGE 'C' WITH (isstrict);
>
> CREATE OR REPLACE FUNCTION dblink_close (text)
> RETURNS text
> AS 'MODULE_PATHNAME','dblink_close'
> LANGUAGE 'C' WITH (isstrict);
>
> ! -- Note: if this is not a first time install of dblink, uncomment the
> ! -- following DROP which prepares the database for the new, non-deprecated
> ! -- version.
> ! --DROP FUNCTION dblink (text,text);
>
> - -- Comment out the following 3 lines if the DEPRECATED functions are used.
> CREATE OR REPLACE FUNCTION dblink (text,text)
> RETURNS setof record
> AS 'MODULE_PATHNAME','dblink_record'
> --- 1,53 ----
> CREATE OR REPLACE FUNCTION dblink_connect (text)
> RETURNS text
> AS 'MODULE_PATHNAME','dblink_connect'
> LANGUAGE 'C' WITH (isstrict);
>
> + CREATE OR REPLACE FUNCTION dblink_connect (text, text)
> + RETURNS text
> + AS 'MODULE_PATHNAME','dblink_connect'
> + LANGUAGE 'C' WITH (isstrict);
> +
> CREATE OR REPLACE FUNCTION dblink_disconnect ()
> RETURNS text
> AS 'MODULE_PATHNAME','dblink_disconnect'
> LANGUAGE 'C' WITH (isstrict);
>
> + CREATE OR REPLACE FUNCTION dblink_disconnect (text)
> + RETURNS text
> + AS 'MODULE_PATHNAME','dblink_disconnect'
> + LANGUAGE 'C' WITH (isstrict);
> +
> CREATE OR REPLACE FUNCTION dblink_open (text,text)
> RETURNS text
> AS 'MODULE_PATHNAME','dblink_open'
> LANGUAGE 'C' WITH (isstrict);
>
> + CREATE OR REPLACE FUNCTION dblink_open (text,text,text)
> + RETURNS text
> + AS 'MODULE_PATHNAME','dblink_open'
> + LANGUAGE 'C' WITH (isstrict);
> +
> CREATE OR REPLACE FUNCTION dblink_fetch (text,int)
> RETURNS setof record
> AS 'MODULE_PATHNAME','dblink_fetch'
> LANGUAGE 'C' WITH (isstrict);
>
> + CREATE OR REPLACE FUNCTION dblink_fetch (text,text,int)
> + RETURNS setof record
> + AS 'MODULE_PATHNAME','dblink_fetch'
> + LANGUAGE 'C' WITH (isstrict);
> +
> CREATE OR REPLACE FUNCTION dblink_close (text)
> RETURNS text
> AS 'MODULE_PATHNAME','dblink_close'
> LANGUAGE 'C' WITH (isstrict);
>
> ! CREATE OR REPLACE FUNCTION dblink_close (text,text)
> ! RETURNS text
> ! AS 'MODULE_PATHNAME','dblink_close'
> ! LANGUAGE 'C' WITH (isstrict);
>
> CREATE OR REPLACE FUNCTION dblink (text,text)
> RETURNS setof record
> AS 'MODULE_PATHNAME','dblink_record'
> Index: contrib/dblink/doc/connection
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/doc/connection,v
> retrieving revision 1.1
> diff -c -r1.1 connection
> *** contrib/dblink/doc/connection 2 Sep 2002 06:32:41 -0000 1.1
> --- contrib/dblink/doc/connection 15 Jun 2003 05:26:53 -0000
> ***************
> *** 6,26 ****
> Synopsis
>
> dblink_connect(text connstr)
>
> Inputs
>
> connstr
>
> standard libpq format connection string,
> e.g. "hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd"
>
> Outputs
>
> Returns status = "OK"
>
> Example usage
>
> ! test=# select dblink_connect('dbname=template1');
> dblink_connect
> ----------------
> OK
> --- 6,40 ----
> Synopsis
>
> dblink_connect(text connstr)
> + dblink_connect(text connname, text connstr)
>
> Inputs
>
> + connname
> + if 2 arguments are given, the first is used as a name for a persistent
> + connection
> +
> connstr
>
> standard libpq format connection string,
> e.g. "hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd"
>
> + if only one argument is given, the connection is unnamed; only one unnamed
> + connection can exist at a time
> +
> Outputs
>
> Returns status = "OK"
>
> Example usage
>
> ! select dblink_connect('dbname=template1');
> ! dblink_connect
> ! ----------------
> ! OK
> ! (1 row)
> !
> ! select dblink_connect('myconn','dbname=template1');
> dblink_connect
> ----------------
> OK
> ***************
> *** 29,43 ****
> ==================================================================
> Name
>
> ! dblink_disconnect -- Closes the persistent connection to a remote database
>
> Synopsis
>
> dblink_disconnect()
>
> Inputs
>
> ! none
>
> Outputs
>
> --- 43,60 ----
> ==================================================================
> Name
>
> ! dblink_disconnect -- Closes a persistent connection to a remote database
>
> Synopsis
>
> dblink_disconnect()
> + dblink_disconnect(text connname)
>
> Inputs
>
> ! connname
> ! if an argument is given, it is used as a name for a persistent
> ! connection to close; otherwiase the unnamed connection is closed
>
> Outputs
>
> ***************
> *** 51,53 ****
> --- 68,75 ----
> OK
> (1 row)
>
> + select dblink_disconnect('myconn');
> + dblink_disconnect
> + -------------------
> + OK
> + (1 row)
> Index: contrib/dblink/doc/cursor
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/doc/cursor,v
> retrieving revision 1.1
> diff -c -r1.1 cursor
> *** contrib/dblink/doc/cursor 2 Sep 2002 06:32:41 -0000 1.1
> --- contrib/dblink/doc/cursor 15 Jun 2003 05:42:15 -0000
> ***************
> *** 6,14 ****
> --- 6,19 ----
> Synopsis
>
> dblink_open(text cursorname, text sql)
> + dblink_open(text connname, text cursorname, text sql)
>
> Inputs
>
> + connname
> + if three arguments are present, the first is taken as the specific
> + connection name to use; otherwise the unnamed connection is assumed
> +
> cursorname
>
> a reference name for the cursor
> ***************
> *** 52,60 ****
> --- 57,70 ----
> Synopsis
>
> dblink_fetch(text cursorname, int32 howmany)
> + dblink_fetch(text connname, text cursorname, int32 howmany)
>
> Inputs
>
> + connname
> + if three arguments are present, the first is taken as the specific
> + connection name to use; otherwise the unnamed connection is assumed
> +
> cursorname
>
> The reference name for the cursor
> ***************
> *** 123,131 ****
> --- 133,146 ----
> Synopsis
>
> dblink_close(text cursorname)
> + dblink_close(text connname, text cursorname)
>
> Inputs
>
> + connname
> + if two arguments are present, the first is taken as the specific
> + connection name to use; otherwise the unnamed connection is assumed
> +
> cursorname
>
> a reference name for the cursor
> ***************
> *** 135,141 ****
> Returns status = "OK"
>
> Note
> ! dblink_connect(text connstr) must be executed first.
>
> Example usage
>
> --- 150,157 ----
> Returns status = "OK"
>
> Note
> ! dblink_connect(text connstr) or dblink_connect(text connname, text connstr)
> ! must be executed first.
>
> Example usage
>
> ***************
> *** 157,159 ****
> --- 173,192 ----
> OK
> (1 row)
>
> + select dblink_connect('myconn','dbname=regression');
> + dblink_connect
> + ----------------
> + OK
> + (1 row)
> +
> + select dblink_open('myconn','foo','select proname, prosrc from pg_proc');
> + dblink_open
> + -------------
> + OK
> + (1 row)
> +
> + select dblink_close('myconn','foo');
> + dblink_close
> + --------------
> + OK
> + (1 row)
> Index: contrib/dblink/doc/deprecated
> ===================================================================
> RCS file: contrib/dblink/doc/deprecated
> diff -N contrib/dblink/doc/deprecated
> *** contrib/dblink/doc/deprecated 2 Sep 2002 06:32:41 -0000 1.1
> --- /dev/null 1 Jan 1970 00:00:00 -0000
> ***************
> *** 1,105 ****
> - ==================================================================
> - Name
> -
> - *DEPRECATED* use new dblink syntax
> - dblink -- Returns a resource id for a data set from a remote database
> -
> - Synopsis
> -
> - dblink(text connstr, text sql)
> -
> - Inputs
> -
> - connstr
> -
> - standard libpq format connection srting,
> - e.g. "hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd"
> -
> - sql
> -
> - sql statement that you wish to execute on the remote host
> - e.g. "select * from pg_class"
> -
> - Outputs
> -
> - Returns setof int (res_id)
> -
> - Example usage
> -
> - select dblink('hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd'
> - ,'select f1, f2 from mytable');
> -
> - ==================================================================
> -
> - Name
> -
> - *DEPRECATED* use new dblink syntax
> - dblink_tok -- Returns individual select field results from a dblink remote query
> -
> - Synopsis
> -
> - dblink_tok(int res_id, int fnumber)
> -
> - Inputs
> -
> - res_id
> -
> - a resource id returned by a call to dblink()
> -
> - fnumber
> -
> - the ordinal position (zero based) of the field to be returned from the dblink result set
> -
> - Outputs
> -
> - Returns text
> -
> - Example usage
> -
> - select dblink_tok(t1.dblink_p,0) as f1, dblink_tok(t1.dblink_p,1) as f2
> - from (select dblink('hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd'
> - ,'select f1, f2 from mytable') as dblink_p) as t1;
> -
> -
> - ==================================================================
> - *DEPRECATED* use new dblink syntax
> - A more convenient way to use dblink may be to create a view:
> -
> - create view myremotetable as
> - select dblink_tok(t1.dblink_p,0) as f1, dblink_tok(t1.dblink_p,1) as f2
> - from (select dblink('hostaddr=127.0.0.1 port=5432 dbname=template1 user=postgres password=postgres'
> - ,'select proname, prosrc from pg_proc') as dblink_p) as t1;
> -
> - Then you can simply write:
> -
> - select f1, f2 from myremotetable where f1 like 'bytea%';
> -
> - ==================================================================
> - Name
> - *DEPRECATED* use new dblink_exec syntax
> - dblink_last_oid -- Returns last inserted oid
> -
> - Synopsis
> -
> - dblink_last_oid(int res_id) RETURNS oid
> -
> - Inputs
> -
> - res_id
> -
> - any resource id returned by dblink function;
> -
> - Outputs
> -
> - Returns oid of last inserted tuple
> -
> - Example usage
> -
> - test=# select dblink_last_oid(dblink('hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd'
> - ,'insert into mytable (f1, f2) values (1,2)'));
> -
> - dblink_last_oid
> - ----------------
> - 16553
> - (1 row)
> -
> --- 0 ----
> Index: contrib/dblink/doc/execute
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/doc/execute,v
> retrieving revision 1.1
> diff -c -r1.1 execute
> *** contrib/dblink/doc/execute 2 Sep 2002 06:32:41 -0000 1.1
> --- contrib/dblink/doc/execute 15 Jun 2003 05:41:10 -0000
> ***************
> *** 6,27 ****
> Synopsis
>
> dblink_exec(text connstr, text sql)
> ! - or -
> dblink_exec(text sql)
>
> Inputs
>
> connstr
>
> ! standard libpq format connection string,
> ! e.g. "hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd"
> ! If the second form is used, then the dblink_connect(text connstr) must be
> ! executed first.
>
> sql
>
> sql statement that you wish to execute on the remote host, e.g.:
> -
> insert into foo values(0,'a','{"a0","b0","c0"}');
>
> Outputs
> --- 6,28 ----
> Synopsis
>
> dblink_exec(text connstr, text sql)
> ! dblink_exec(text connname, text sql)
> dblink_exec(text sql)
>
> Inputs
>
> + connname
> connstr
> + If two arguments are present, the first is first assumed to be a specific
> + connection name to use. If the name is not found, the argument is then
> + assumed to be a valid connection string, of standard libpq format,
> + e.g.: "hostaddr=127.0.0.1 dbname=mydb user=postgres password=mypasswd"
>
> ! If only one argument is used, then the unnamed connection is used.
>
> sql
>
> sql statement that you wish to execute on the remote host, e.g.:
> insert into foo values(0,'a','{"a0","b0","c0"}');
>
> Outputs
> ***************
> *** 36,49 ****
>
> Example usage
>
> ! test=# select dblink_connect('dbname=dblink_test_slave');
> dblink_connect
> ----------------
> OK
> (1 row)
>
> ! test=# select dblink_exec('insert into foo values(21,''z'',''{"a0","b0","c0"}'');');
> dblink_exec
> -----------------
> INSERT 943366 1
> (1 row)
> --- 37,62 ----
>
> Example usage
>
> ! select dblink_connect('dbname=dblink_test_slave');
> dblink_connect
> ----------------
> OK
> (1 row)
>
> ! select dblink_exec('insert into foo values(21,''z'',''{"a0","b0","c0"}'');');
> dblink_exec
> -----------------
> INSERT 943366 1
> + (1 row)
> +
> + select dblink_connect('myconn','dbname=regression');
> + dblink_connect
> + ----------------
> + OK
> + (1 row)
> +
> + select dblink_exec('myconn','insert into foo values(21,''z'',''{"a0","b0","c0"}'');');
> + dblink_exec
> + ------------------
> + INSERT 6432584 1
> (1 row)
> Index: contrib/dblink/doc/query
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/doc/query,v
> retrieving revision 1.1
> diff -c -r1.1 query
> *** contrib/dblink/doc/query 2 Sep 2002 06:32:41 -0000 1.1
> --- contrib/dblink/doc/query 15 Jun 2003 05:39:31 -0000
> ***************
> *** 6,22 ****
> Synopsis
>
> dblink(text connstr, text sql)
> ! - or -
> dblink(text sql)
>
> Inputs
>
> connstr
>
> ! standard libpq format connection string,
> ! e.g. "hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd"
> ! If the second form is used, then the dblink_connect(text connstr) must be
> ! executed first.
>
> sql
>
> --- 6,24 ----
> Synopsis
>
> dblink(text connstr, text sql)
> ! dblink(text connname, text sql)
> dblink(text sql)
>
> Inputs
>
> + connname
> connstr
> + If two arguments are present, the first is first assumed to be a specific
> + connection name to use. If the name is not found, the argument is then
> + assumed to be a valid connection string, of standard libpq format,
> + e.g.: "hostaddr=127.0.0.1 dbname=mydb user=postgres password=mypasswd"
>
> ! If only one argument is used, then the unnamed connection is used.
>
> sql
>
> ***************
> *** 29,35 ****
>
> Example usage
>
> ! test=# select * from dblink('dbname=template1','select proname, prosrc from pg_proc')
> as t1(proname name, prosrc text) where proname like 'bytea%';
> proname | prosrc
> ------------+------------
> --- 31,37 ----
>
> Example usage
>
> ! select * from dblink('dbname=template1','select proname, prosrc from pg_proc')
> as t1(proname name, prosrc text) where proname like 'bytea%';
> proname | prosrc
> ------------+------------
> ***************
> *** 47,59 ****
> byteaout | byteaout
> (12 rows)
>
> ! test=# select dblink_connect('dbname=template1');
> dblink_connect
> ----------------
> OK
> (1 row)
>
> ! test=# select * from dblink('select proname, prosrc from pg_proc')
> as t1(proname name, prosrc text) where proname like 'bytea%';
> proname | prosrc
> ------------+------------
> --- 49,61 ----
> byteaout | byteaout
> (12 rows)
>
> ! select dblink_connect('dbname=template1');
> dblink_connect
> ----------------
> OK
> (1 row)
>
> ! select * from dblink('select proname, prosrc from pg_proc')
> as t1(proname name, prosrc text) where proname like 'bytea%';
> proname | prosrc
> ------------+------------
> ***************
> *** 70,75 ****
> --- 72,104 ----
> byteain | byteain
> byteaout | byteaout
> (12 rows)
> +
> + select dblink_connect('myconn','dbname=regression');
> + dblink_connect
> + ----------------
> + OK
> + (1 row)
> +
> + select * from dblink('myconn','select proname, prosrc from pg_proc')
> + as t1(proname name, prosrc text) where proname like 'bytea%';
> + proname | prosrc
> + ------------+------------
> + bytearecv | bytearecv
> + byteasend | byteasend
> + byteale | byteale
> + byteagt | byteagt
> + byteage | byteage
> + byteane | byteane
> + byteacmp | byteacmp
> + bytealike | bytealike
> + byteanlike | byteanlike
> + byteacat | byteacat
> + byteaeq | byteaeq
> + bytealt | bytealt
> + byteain | byteain
> + byteaout | byteaout
> + (14 rows)
> +
>
> ==================================================================
> A more convenient way to use dblink may be to create a view:
> Index: contrib/dblink/expected/dblink.out
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/expected/dblink.out,v
> retrieving revision 1.8
> diff -c -r1.8 dblink.out
> *** contrib/dblink/expected/dblink.out 14 May 2003 03:25:55 -0000 1.8
> --- contrib/dblink/expected/dblink.out 15 Jun 2003 04:11:11 -0000
> ***************
> *** 106,116 ****
> 9 | j | {a9,b9,c9}
> (2 rows)
>
> ! -- should generate "no connection available" error
> SELECT *
> FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
> WHERE t.a > 7;
> ! ERROR: dblink: no connection available
> -- create a persistent connection
> SELECT dblink_connect('dbname=regression');
> dblink_connect
> --- 106,116 ----
> 9 | j | {a9,b9,c9}
> (2 rows)
>
> ! -- should generate "connection not available" error
> SELECT *
> FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
> WHERE t.a > 7;
> ! ERROR: dblink_record: connection not available
> -- create a persistent connection
> SELECT dblink_connect('dbname=regression');
> dblink_connect
> ***************
> *** 172,181 ****
> OK
> (1 row)
>
> ! -- should generate "cursor rmt_foo_cursor does not exist" error
> SELECT *
> FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
> ! ERROR: dblink_fetch: cursor rmt_foo_cursor does not exist
> -- close the persistent connection
> SELECT dblink_disconnect();
> dblink_disconnect
> --- 172,181 ----
> OK
> (1 row)
>
> ! -- should generate "cursor not found: rmt_foo_cursor" error
> SELECT *
> FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
> ! ERROR: dblink_fetch: cursor not found: rmt_foo_cursor
> -- close the persistent connection
> SELECT dblink_disconnect();
> dblink_disconnect
> ***************
> *** 183,193 ****
> OK
> (1 row)
>
> ! -- should generate "no connection available" error
> SELECT *
> FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
> WHERE t.a > 7;
> ! ERROR: dblink: no connection available
> -- put more data into our slave table, first using arbitrary connection syntax
> -- but truncate the actual return value so we can use diff to check for success
> SELECT substr(dblink_exec('dbname=regression','INSERT INTO foo VALUES(10,''k'',''{"a10","b10","c10"}'')'),1,6);
> --- 183,194 ----
> OK
> (1 row)
>
> ! -- should generate "no connection to the server" error
> SELECT *
> FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
> WHERE t.a > 7;
> ! ERROR: dblink: sql error: no connection to the server
> !
> -- put more data into our slave table, first using arbitrary connection syntax
> -- but truncate the actual return value so we can use diff to check for success
> SELECT substr(dblink_exec('dbname=regression','INSERT INTO foo VALUES(10,''k'',''{"a10","b10","c10"}'')'),1,6);
> ***************
> *** 268,270 ****
> --- 269,466 ----
> OK
> (1 row)
>
> + --
> + -- tests for the new named persistent connection syntax
> + --
> + -- should generate "missing "=" after "myconn" in connection info string" error
> + SELECT *
> + FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
> + WHERE t.a > 7;
> + ERROR: dblink: connection error: missing "=" after "myconn" in connection info string
> +
> + -- create a named persistent connection
> + SELECT dblink_connect('myconn','dbname=regression');
> + dblink_connect
> + ----------------
> + OK
> + (1 row)
> +
> + -- use the named persistent connection
> + SELECT *
> + FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
> + WHERE t.a > 7;
> + a | b | c
> + ----+---+---------------
> + 8 | i | {a8,b8,c8}
> + 9 | j | {a9,b9,c9}
> + 10 | k | {a10,b10,c10}
> + (3 rows)
> +
> + -- create a second named persistent connection
> + -- should error with "cannot save named connection"
> + SELECT dblink_connect('myconn','dbname=regression');
> + NOTICE: cannot use a connection name more than once
> + ERROR: dblink_connect: cannot save named connection
> + -- create a second named persistent connection with a new name
> + SELECT dblink_connect('myconn2','dbname=regression');
> + dblink_connect
> + ----------------
> + OK
> + (1 row)
> +
> + -- use the second named persistent connection
> + SELECT *
> + FROM dblink('myconn2','SELECT * FROM foo') AS t(a int, b text, c text[])
> + WHERE t.a > 7;
> + a | b | c
> + ----+---+---------------
> + 8 | i | {a8,b8,c8}
> + 9 | j | {a9,b9,c9}
> + 10 | k | {a10,b10,c10}
> + (3 rows)
> +
> + -- close the second named persistent connection
> + SELECT dblink_disconnect('myconn2');
> + dblink_disconnect
> + -------------------
> + OK
> + (1 row)
> +
> + -- open a cursor
> + SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foo');
> + dblink_open
> + -------------
> + OK
> + (1 row)
> +
> + -- fetch some data
> + SELECT *
> + FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
> + a | b | c
> + ---+---+------------
> + 0 | a | {a0,b0,c0}
> + 1 | b | {a1,b1,c1}
> + 2 | c | {a2,b2,c2}
> + 3 | d | {a3,b3,c3}
> + (4 rows)
> +
> + SELECT *
> + FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
> + a | b | c
> + ---+---+------------
> + 4 | e | {a4,b4,c4}
> + 5 | f | {a5,b5,c5}
> + 6 | g | {a6,b6,c6}
> + 7 | h | {a7,b7,c7}
> + (4 rows)
> +
> + -- this one only finds three rows left
> + SELECT *
> + FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
> + a | b | c
> + ----+---+---------------
> + 8 | i | {a8,b8,c8}
> + 9 | j | {a9,b9,c9}
> + 10 | k | {a10,b10,c10}
> + (3 rows)
> +
> + -- close the cursor
> + SELECT dblink_close('myconn','rmt_foo_cursor');
> + dblink_close
> + --------------
> + OK
> + (1 row)
> +
> + -- should generate "cursor not found: rmt_foo_cursor" error
> + SELECT *
> + FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
> + ERROR: dblink_fetch: cursor not found: rmt_foo_cursor
> + -- close the named persistent connection
> + SELECT dblink_disconnect('myconn');
> + dblink_disconnect
> + -------------------
> + OK
> + (1 row)
> +
> + -- should generate "missing "=" after "myconn" in connection info string" error
> + SELECT *
> + FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
> + WHERE t.a > 7;
> + ERROR: dblink: connection error: missing "=" after "myconn" in connection info string
> +
> + -- create a named persistent connection
> + SELECT dblink_connect('myconn','dbname=regression');
> + dblink_connect
> + ----------------
> + OK
> + (1 row)
> +
> + -- put more data into our slave table, using named persistent connection syntax
> + -- but truncate the actual return value so we can use diff to check for success
> + SELECT substr(dblink_exec('myconn','INSERT INTO foo VALUES(11,''l'',''{"a11","b11","c11"}'')'),1,6);
> + substr
> + --------
> + INSERT
> + (1 row)
> +
> + -- let's see it
> + SELECT *
> + FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]);
> + a | b | c
> + ----+---+---------------
> + 0 | a | {a0,b0,c0}
> + 1 | b | {a1,b1,c1}
> + 2 | c | {a2,b2,c2}
> + 3 | d | {a3,b3,c3}
> + 4 | e | {a4,b4,c4}
> + 5 | f | {a5,b5,c5}
> + 6 | g | {a6,b6,c6}
> + 7 | h | {a7,b7,c7}
> + 8 | i | {a8,b8,c8}
> + 9 | j | {a9,b9,c9}
> + 10 | k | {a10,b10,c10}
> + 11 | l | {a11,b11,c11}
> + (12 rows)
> +
> + -- change some data
> + SELECT dblink_exec('myconn','UPDATE foo SET f3[2] = ''b99'' WHERE f1 = 11');
> + dblink_exec
> + -------------
> + UPDATE 1
> + (1 row)
> +
> + -- let's see it
> + SELECT *
> + FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
> + WHERE a = 11;
> + a | b | c
> + ----+---+---------------
> + 11 | l | {a11,b99,c11}
> + (1 row)
> +
> + -- delete some data
> + SELECT dblink_exec('myconn','DELETE FROM foo WHERE f1 = 11');
> + dblink_exec
> + -------------
> + DELETE 1
> + (1 row)
> +
> + -- let's see it
> + SELECT *
> + FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
> + WHERE a = 11;
> + a | b | c
> + ---+---+---
> + (0 rows)
> +
> + -- close the named persistent connection
> + SELECT dblink_disconnect('myconn');
> + dblink_disconnect
> + -------------------
> + OK
> + (1 row)
> +
> + -- close the named persistent connection again
> + -- should get "connection named "myconn" not found" error
> + SELECT dblink_disconnect('myconn');
> + ERROR: dblink_disconnect: connection named "myconn" not found
> Index: contrib/dblink/sql/dblink.sql
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/sql/dblink.sql,v
> retrieving revision 1.8
> diff -c -r1.8 dblink.sql
> *** contrib/dblink/sql/dblink.sql 14 May 2003 03:25:55 -0000 1.8
> --- contrib/dblink/sql/dblink.sql 15 Jun 2003 04:10:59 -0000
> ***************
> *** 68,74 ****
> FROM dblink('dbname=regression','SELECT * FROM foo') AS t(a int, b text, c text[])
> WHERE t.a > 7;
>
> ! -- should generate "no connection available" error
> SELECT *
> FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
> WHERE t.a > 7;
> --- 68,74 ----
> FROM dblink('dbname=regression','SELECT * FROM foo') AS t(a int, b text, c text[])
> WHERE t.a > 7;
>
> ! -- should generate "connection not available" error
> SELECT *
> FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
> WHERE t.a > 7;
> ***************
> *** 98,111 ****
> -- close the cursor
> SELECT dblink_close('rmt_foo_cursor');
>
> ! -- should generate "cursor rmt_foo_cursor does not exist" error
> SELECT *
> FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
>
> -- close the persistent connection
> SELECT dblink_disconnect();
>
> ! -- should generate "no connection available" error
> SELECT *
> FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
> WHERE t.a > 7;
> --- 98,111 ----
> -- close the cursor
> SELECT dblink_close('rmt_foo_cursor');
>
> ! -- should generate "cursor not found: rmt_foo_cursor" error
> SELECT *
> FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
>
> -- close the persistent connection
> SELECT dblink_disconnect();
>
> ! -- should generate "no connection to the server" error
> SELECT *
> FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
> WHERE t.a > 7;
> ***************
> *** 143,145 ****
> --- 143,240 ----
>
> -- close the persistent connection
> SELECT dblink_disconnect();
> +
> + --
> + -- tests for the new named persistent connection syntax
> + --
> +
> + -- should generate "missing "=" after "myconn" in connection info string" error
> + SELECT *
> + FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
> + WHERE t.a > 7;
> +
> + -- create a named persistent connection
> + SELECT dblink_connect('myconn','dbname=regression');
> +
> + -- use the named persistent connection
> + SELECT *
> + FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
> + WHERE t.a > 7;
> +
> + -- create a second named persistent connection
> + -- should error with "cannot save named connection"
> + SELECT dblink_connect('myconn','dbname=regression');
> +
> + -- create a second named persistent connection with a new name
> + SELECT dblink_connect('myconn2','dbname=regression');
> +
> + -- use the second named persistent connection
> + SELECT *
> + FROM dblink('myconn2','SELECT * FROM foo') AS t(a int, b text, c text[])
> + WHERE t.a > 7;
> +
> + -- close the second named persistent connection
> + SELECT dblink_disconnect('myconn2');
> +
> + -- open a cursor
> + SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foo');
> +
> + -- fetch some data
> + SELECT *
> + FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
> +
> + SELECT *
> + FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
> +
> + -- this one only finds three rows left
> + SELECT *
> + FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
> +
> + -- close the cursor
> + SELECT dblink_close('myconn','rmt_foo_cursor');
> +
> + -- should generate "cursor not found: rmt_foo_cursor" error
> + SELECT *
> + FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
> +
> + -- close the named persistent connection
> + SELECT dblink_disconnect('myconn');
> +
> + -- should generate "missing "=" after "myconn" in connection info string" error
> + SELECT *
> + FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
> + WHERE t.a > 7;
> +
> + -- create a named persistent connection
> + SELECT dblink_connect('myconn','dbname=regression');
> +
> + -- put more data into our slave table, using named persistent connection syntax
> + -- but truncate the actual return value so we can use diff to check for success
> + SELECT substr(dblink_exec('myconn','INSERT INTO foo VALUES(11,''l'',''{"a11","b11","c11"}'')'),1,6);
> +
> + -- let's see it
> + SELECT *
> + FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]);
> +
> + -- change some data
> + SELECT dblink_exec('myconn','UPDATE foo SET f3[2] = ''b99'' WHERE f1 = 11');
> +
> + -- let's see it
> + SELECT *
> + FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
> + WHERE a = 11;
> +
> + -- delete some data
> + SELECT dblink_exec('myconn','DELETE FROM foo WHERE f1 = 11');
> +
> + -- let's see it
> + SELECT *
> + FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
> + WHERE a = 11;
> +
> + -- close the named persistent connection
> + SELECT dblink_disconnect('myconn');
> +
> + -- close the named persistent connection again
> + -- should get "connection named "myconn" not found" error
> + SELECT dblink_disconnect('myconn');
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Patch applied. Thanks.
---------------------------------------------------------------------------
Shridhar Daithankar wrote:
Content-Description: Mail message body
> Hello,
>
> Please apply attached patch to contrib/dblink. It adds named persistent
> connections to dblink.
>
> I have been working on this with Joe Conway and this patch is seems to work
> without any problem.
>
> Thanks Joe, for your assistance.
>
>
>
> Bye
> Shridhar
>
> --
> Anthony's Law of the Workshop: Any tool when dropped, will roll into the least
> accessible corner of the workshop.Corollary: On the way to the corner, any
> dropped tool will first strike your toes.
>
>
Content-Description: Text from file 'dblink.02.diff'
> Index: contrib/dblink/README.dblink
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/README.dblink,v
> retrieving revision 1.7
> diff -c -r1.7 README.dblink
> *** contrib/dblink/README.dblink 23 Nov 2002 18:59:25 -0000 1.7
> --- contrib/dblink/README.dblink 15 Jun 2003 05:17:46 -0000
> ***************
> *** 4,11 ****
> * Functions returning results from a remote database
> *
> * Joe Conway <mail@joeconway.com>
> *
> ! * Copyright (c) 2001, 2002 by PostgreSQL Global Development Group
> * ALL RIGHTS RESERVED;
> *
> * Permission to use, copy, modify, and distribute this software and its
> --- 4,14 ----
> * Functions returning results from a remote database
> *
> * Joe Conway <mail@joeconway.com>
> + * And contributors:
> + * Darko Prenosil <Darko.Prenosil@finteh.hr>
> + * Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
> *
> ! * Copyright (c) 2001, 2002, 2003 by PostgreSQL Global Development Group
> * ALL RIGHTS RESERVED;
> *
> * Permission to use, copy, modify, and distribute this software and its
> ***************
> *** 27,40 ****
> *
> */
>
> ! Version 0.5 (25 August, 2002):
> ! Major overhaul to work with new backend "table function" capability. Removed
> ! dblink_strtok() and dblink_replace() functions because they are now
> ! available as backend functions (split() and replace() respectively).
> ! Tested under Linux (Red Hat 7.3) and PostgreSQL 7.3devel. This version
> ! is no longer backwards portable to PostgreSQL 7.2.
>
> Release Notes:
> Version 0.5
> - dblink now supports use directly as a table function; this is the new
> preferred usage going forward
> --- 30,45 ----
> *
> */
>
> ! Version 0.6 (14 June, 2003):
> ! Completely removed previously deprecated functions. Added ability
> ! to create "named" persistent connections in addition to the single global
> ! "unnamed" persistent connection.
> ! Tested under Linux (Red Hat 9) and PostgreSQL 7.4devel.
>
> Release Notes:
> + Version 0.6
> + - functions deprecated in 0.5 have been removed
> + - added ability to create "named" persistent connections
> Version 0.5
> - dblink now supports use directly as a table function; this is the new
> preferred usage going forward
> ***************
> *** 87,121 ****
> connection
> ------------
> dblink_connect(text) RETURNS text
> ! - opens a connection that will persist for duration of current
> backend or until it is disconnected
> dblink_disconnect() RETURNS text
> ! - disconnects a persistent connection
>
> cursor
> ------------
> dblink_open(text,text) RETURNS text
> ! - opens a cursor using connection already opened with dblink_connect()
> ! that will persist for duration of current backend or until it is
> ! closed
> dblink_fetch(text, int) RETURNS setof record
> ! - fetches data from an already opened cursor
> dblink_close(text) RETURNS text
> ! - closes a cursor
>
> query
> ------------
> dblink(text,text) RETURNS setof record
> ! - returns a set of results from remote SELECT query
> ! (Note: comment out in dblink.sql to use deprecated version)
> dblink(text) RETURNS setof record
> ! - returns a set of results from remote SELECT query, using connection
> ! already opened with dblink_connect()
>
> execute
> ------------
> dblink_exec(text, text) RETURNS text
> ! - executes an INSERT/UPDATE/DELETE query remotely
> dblink_exec(text) RETURNS text
> - executes an INSERT/UPDATE/DELETE query remotely, using connection
> already opened with dblink_connect()
> --- 92,142 ----
> connection
> ------------
> dblink_connect(text) RETURNS text
> ! - opens an unnamed connection that will persist for duration of
> ! current backend or until it is disconnected
> ! dblink_connect(text,text) RETURNS text
> ! - opens a named connection that will persist for duration of current
> backend or until it is disconnected
> dblink_disconnect() RETURNS text
> ! - disconnects the unnamed persistent connection
> ! dblink_disconnect(text) RETURNS text
> ! - disconnects a named persistent connection
>
> cursor
> ------------
> dblink_open(text,text) RETURNS text
> ! - opens a cursor using unnamed connection already opened with
> ! dblink_connect() that will persist for duration of current backend
> ! or until it is closed
> ! dblink_open(text,text,text) RETURNS text
> ! - opens a cursor using a named connection already opened with
> ! dblink_connect() that will persist for duration of current backend
> ! or until it is closed
> dblink_fetch(text, int) RETURNS setof record
> ! - fetches data from an already opened cursor on the unnamed connection
> ! dblink_fetch(text, text, int) RETURNS setof record
> ! - fetches data from an already opened cursor on a named connection
> dblink_close(text) RETURNS text
> ! - closes a cursor on the unnamed connection
> ! dblink_close(text,text) RETURNS text
> ! - closes a cursor on a named connection
>
> query
> ------------
> dblink(text,text) RETURNS setof record
> ! - returns a set of results from remote SELECT query; the first argument
> ! is either a connection string, or the name of an already opened
> ! persistant connection
> dblink(text) RETURNS setof record
> ! - returns a set of results from remote SELECT query, using the unnamed
> ! connection already opened with dblink_connect()
>
> execute
> ------------
> dblink_exec(text, text) RETURNS text
> ! - executes an INSERT/UPDATE/DELETE query remotely; the first argument
> ! is either a connection string, or the name of an already opened
> ! persistant connection
> dblink_exec(text) RETURNS text
> - executes an INSERT/UPDATE/DELETE query remotely, using connection
> already opened with dblink_connect()
> ***************
> *** 135,153 ****
> dblink_build_sql_update(text,int2vector,int2,_text,_text) RETURNS text
> - builds an update statement using a local tuple, replacing the
> selection key field values with alternate supplied values
> -
> - Not installed by default
> - deprecated
> - ------------
> - dblink(text,text) RETURNS setof int
> - - *DEPRECATED* returns a resource id for results from remote query
> - (Note: must uncomment in dblink.sql to use)
> - dblink_tok(int,int) RETURNS text
> - - *DEPRECATED* extracts and returns individual field results; used
> - only in conjunction with the *DEPRECATED* form of dblink
> - (Note: must uncomment in dblink.sql to use)
> - dblink_last_oid(int) RETURNS oid
> - - *DEPRECATED* returns the last inserted oid
>
> Documentation:
>
> --- 156,161 ----
> Index: contrib/dblink/dblink.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/dblink.c,v
> retrieving revision 1.20
> diff -c -r1.20 dblink.c
> *** contrib/dblink/dblink.c 28 May 2003 16:03:55 -0000 1.20
> --- contrib/dblink/dblink.c 15 Jun 2003 04:17:52 -0000
> ***************
> *** 4,11 ****
> * Functions returning results from a remote database
> *
> * Joe Conway <mail@joeconway.com>
> *
> ! * Copyright (c) 2001, 2002 by PostgreSQL Global Development Group
> * ALL RIGHTS RESERVED;
> *
> * Permission to use, copy, modify, and distribute this software and its
> --- 4,14 ----
> * Functions returning results from a remote database
> *
> * Joe Conway <mail@joeconway.com>
> + * And contributors:
> + * Darko Prenosil <Darko.Prenosil@finteh.hr>
> + * Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
> *
> ! * Copyright (c) 2001, 2002, 2003 by PostgreSQL Global Development Group
> * ALL RIGHTS RESERVED;
> *
> * Permission to use, copy, modify, and distribute this software and its
> ***************
> *** 27,35 ****
> *
> */
> #include "postgres.h"
> -
> #include "libpq-fe.h"
> -
> #include "fmgr.h"
> #include "funcapi.h"
> #include "access/tupdesc.h"
> --- 30,36 ----
> ***************
> *** 51,63 ****
> #include "utils/array.h"
> #include "utils/lsyscache.h"
> #include "utils/syscache.h"
>
> #include "dblink.h"
>
> /*
> * Internal declarations
> */
> ! static dblink_results *init_dblink_results(MemoryContext fn_mcxt);
> static char **get_pkey_attnames(Oid relid, int16 *numatts);
> static char *get_sql_insert(Oid relid, int16 *pkattnums, int16 pknumatts, char **src_pkattvals, char
**tgt_pkattvals);
> static char *get_sql_delete(Oid relid, int16 *pkattnums, int16 pknumatts, char **tgt_pkattvals);
> --- 52,78 ----
> #include "utils/array.h"
> #include "utils/lsyscache.h"
> #include "utils/syscache.h"
> + #include "utils/palloc.h"
> + #include "utils/dynahash.h"
> + #include "utils/hsearch.h"
> + #include "utils/memutils.h"
>
> #include "dblink.h"
>
> + typedef struct remoteConn
> + {
> + PGconn *con; /* Hold the remote connection */
> + bool remoteTrFlag; /* Indicates whether or not a transaction
> + * on remote database is in progress*/
> + } remoteConn;
> +
> /*
> * Internal declarations
> */
> ! static remoteConn *getConnectionByName(const char *name);
> ! static HTAB *createConnHash(void);
> ! static bool createNewConnection(const char *name,remoteConn *con);
> ! static void deleteConnection(const char *name);
> static char **get_pkey_attnames(Oid relid, int16 *numatts);
> static char *get_sql_insert(Oid relid, int16 *pkattnums, int16 pknumatts, char **src_pkattvals, char
**tgt_pkattvals);
> static char *get_sql_delete(Oid relid, int16 *pkattnums, int16 pknumatts, char **tgt_pkattvals);
> ***************
> *** 67,83 ****
> static int16 get_attnum_pk_pos(int16 *pkattnums, int16 pknumatts, int16 key);
> static HeapTuple get_tuple_of_interest(Oid relid, int16 *pkattnums, int16 pknumatts, char **src_pkattvals);
> static Oid get_relid_from_relname(text *relname_text);
> - static dblink_results *get_res_ptr(int32 res_id_index);
> - static void append_res_ptr(dblink_results * results);
> - static void remove_res_ptr(dblink_results * results);
> static TupleDesc pgresultGetTupleDesc(PGresult *res);
> static char *generate_relation_name(Oid relid);
>
> /* Global */
> ! List *res_id = NIL;
> ! int res_id_index = 0;
> ! PGconn *persistent_conn = NULL;
>
> #define GET_TEXT(cstrp) DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(cstrp)))
> #define GET_STR(textp) DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(textp)))
> #define xpfree(var_) \
> --- 82,113 ----
> static int16 get_attnum_pk_pos(int16 *pkattnums, int16 pknumatts, int16 key);
> static HeapTuple get_tuple_of_interest(Oid relid, int16 *pkattnums, int16 pknumatts, char **src_pkattvals);
> static Oid get_relid_from_relname(text *relname_text);
> static TupleDesc pgresultGetTupleDesc(PGresult *res);
> static char *generate_relation_name(Oid relid);
>
> /* Global */
> ! List *res_id = NIL;
> ! int res_id_index = 0;
> ! PGconn *persistent_conn = NULL;
> ! static HTAB *remoteConnHash=NULL;
> !
> ! /*
> ! Following is list that holds multiple remote connections.
> ! Calling convention of each dblink function changes to accept
> ! connection name as the first parameter. The connection list is
> ! much like ecpg e.g. a mapping between a name and a PGconn object.
> ! */
> !
> ! typedef struct remoteConnHashEnt
> ! {
> ! char name[NAMEDATALEN];
> ! remoteConn *rcon;
> ! } remoteConnHashEnt;
> !
> ! /* initial number of connection hashes */
> ! #define NUMCONN 16
>
> + /* general utility */
> #define GET_TEXT(cstrp) DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(cstrp)))
> #define GET_STR(textp) DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(textp)))
> #define xpfree(var_) \
> ***************
> *** 88,93 ****
> --- 118,158 ----
> var_ = NULL; \
> } \
> } while (0)
> + #define DBLINK_RES_ERROR(p1, p2) \
> + do { \
> + msg = pstrdup(PQerrorMessage(conn)); \
> + if (res) \
> + PQclear(res); \
> + elog(ERROR, "%s: %s: %s", p1, p2, msg); \
> + } while (0)
> + #define DBLINK_CONN_NOT_AVAIL(p1) \
> + do { \
> + if(conname) \
> + elog(ERROR, "%s: connection %s not available", p1, conname); \
> + else \
> + elog(ERROR, "%s: connection not available", p1); \
> + } while (0)
> + #define DBLINK_GET_CONN(p1) \
> + do { \
> + char *conname_or_str = GET_STR(PG_GETARG_TEXT_P(0)); \
> + rcon = getConnectionByName(conname_or_str); \
> + if(rcon) \
> + { \
> + conn = rcon->con; \
> + freeconn = false; \
> + } \
> + else \
> + { \
> + connstr = conname_or_str; \
> + conn = PQconnectdb(connstr); \
> + if (PQstatus(conn) == CONNECTION_BAD) \
> + { \
> + msg = pstrdup(PQerrorMessage(conn)); \
> + PQfinish(conn); \
> + elog(ERROR, "%s: connection error: %s", p1, msg); \
> + } \
> + } \
> + } while (0)
>
>
> /*
> ***************
> *** 97,124 ****
> Datum
> dblink_connect(PG_FUNCTION_ARGS)
> {
> ! char *connstr = GET_STR(PG_GETARG_TEXT_P(0));
> char *msg;
> - text *result_text;
> MemoryContext oldcontext;
>
> ! if (persistent_conn != NULL)
> ! PQfinish(persistent_conn);
>
> oldcontext = MemoryContextSwitchTo(TopMemoryContext);
> ! persistent_conn = PQconnectdb(connstr);
> MemoryContextSwitchTo(oldcontext);
>
> ! if (PQstatus(persistent_conn) == CONNECTION_BAD)
> {
> ! msg = pstrdup(PQerrorMessage(persistent_conn));
> ! PQfinish(persistent_conn);
> ! persistent_conn = NULL;
> elog(ERROR, "dblink_connect: connection error: %s", msg);
> }
>
> ! result_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum("OK")));
> ! PG_RETURN_TEXT_P(result_text);
> }
>
> /*
> --- 162,213 ----
> Datum
> dblink_connect(PG_FUNCTION_ARGS)
> {
> ! char *connstr = NULL;
> ! char *connname = NULL;
> char *msg;
> MemoryContext oldcontext;
> + PGconn *conn = NULL;
> + remoteConn *rcon = NULL;
>
> ! if(PG_NARGS()==2)
> ! {
> ! connstr = GET_STR(PG_GETARG_TEXT_P(1));
> ! connname = GET_STR(PG_GETARG_TEXT_P(0));
> ! }
> ! else if(PG_NARGS()==1)
> ! connstr = GET_STR(PG_GETARG_TEXT_P(0));
>
> oldcontext = MemoryContextSwitchTo(TopMemoryContext);
> !
> ! if(connname)
> ! rcon=(remoteConn *) palloc(sizeof(remoteConn));
> ! conn = PQconnectdb(connstr);
> !
> MemoryContextSwitchTo(oldcontext);
>
> ! if (PQstatus(conn) == CONNECTION_BAD)
> {
> ! msg = pstrdup(PQerrorMessage(conn));
> ! PQfinish(conn);
> ! if(rcon)
> ! pfree(rcon);
> elog(ERROR, "dblink_connect: connection error: %s", msg);
> }
>
> ! if(connname)
> ! {
> ! rcon->con = conn;
> ! if(createNewConnection(connname, rcon) == false)
> ! {
> ! PQfinish(conn);
> ! pfree(rcon);
> ! elog(ERROR, "dblink_connect: cannot save named connection");
> ! }
> ! }
> ! else
> ! persistent_conn = conn;
> !
> ! PG_RETURN_TEXT_P(GET_TEXT("OK"));
> }
>
> /*
> ***************
> *** 128,142 ****
> Datum
> dblink_disconnect(PG_FUNCTION_ARGS)
> {
> ! text *result_text;
>
> ! if (persistent_conn != NULL)
> ! PQfinish(persistent_conn);
>
> ! persistent_conn = NULL;
>
> ! result_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum("OK")));
> ! PG_RETURN_TEXT_P(result_text);
> }
>
> /*
> --- 217,253 ----
> Datum
> dblink_disconnect(PG_FUNCTION_ARGS)
> {
> ! char *str = NULL;
> ! remoteConn *rcon = NULL;
> ! PGconn *conn = NULL;
> !
> ! if (PG_NARGS() ==1 )
> ! {
> ! str = GET_STR(PG_GETARG_TEXT_P(0));
> ! rcon = getConnectionByName(str);
> ! if (rcon)
> ! conn = rcon->con;
> ! }
> ! else
> ! conn = persistent_conn;
>
> ! if (!conn)
> ! {
> ! if (str)
> ! elog(ERROR,"dblink_disconnect: connection named \"%s\" not found",
> ! str);
> ! else
> ! elog(ERROR,"dblink_disconnect: connection not found");
> ! }
>
> ! PQfinish(conn);
> ! if (rcon)
> ! {
> ! deleteConnection(str);
> ! pfree(rcon);
> ! }
>
> ! PG_RETURN_TEXT_P(GET_TEXT("OK"));
> }
>
> /*
> ***************
> *** 149,175 ****
> char *msg;
> PGresult *res = NULL;
> PGconn *conn = NULL;
> ! text *result_text;
> ! char *curname = GET_STR(PG_GETARG_TEXT_P(0));
> ! char *sql = GET_STR(PG_GETARG_TEXT_P(1));
> StringInfo str = makeStringInfo();
>
> ! if (persistent_conn != NULL)
> conn = persistent_conn;
> ! else
> ! elog(ERROR, "dblink_open: no connection available");
>
> res = PQexec(conn, "BEGIN");
> if (PQresultStatus(res) != PGRES_COMMAND_OK)
> ! {
> ! msg = pstrdup(PQerrorMessage(conn));
> ! PQclear(res);
> !
> ! PQfinish(conn);
> ! persistent_conn = NULL;
>
> - elog(ERROR, "dblink_open: begin error: %s", msg);
> - }
> PQclear(res);
>
> appendStringInfo(str, "DECLARE %s CURSOR FOR %s", curname, sql);
> --- 260,294 ----
> char *msg;
> PGresult *res = NULL;
> PGconn *conn = NULL;
> ! char *curname = NULL;
> ! char *sql = NULL;
> ! char *conname = NULL;
> StringInfo str = makeStringInfo();
> + remoteConn *rcon = NULL;
>
> ! if(PG_NARGS() == 2)
> ! {
> ! curname = GET_STR(PG_GETARG_TEXT_P(0));
> ! sql = GET_STR(PG_GETARG_TEXT_P(1));
> conn = persistent_conn;
> ! }
> ! else if(PG_NARGS() == 3)
> ! {
> ! conname = GET_STR(PG_GETARG_TEXT_P(0));
> ! curname = GET_STR(PG_GETARG_TEXT_P(1));
> ! sql = GET_STR(PG_GETARG_TEXT_P(2));
> ! rcon = getConnectionByName(conname);
> ! if (rcon)
> ! conn = rcon->con;
> ! }
> !
> ! if (!conn)
> ! DBLINK_CONN_NOT_AVAIL("dblink_open");
>
> res = PQexec(conn, "BEGIN");
> if (PQresultStatus(res) != PGRES_COMMAND_OK)
> ! DBLINK_RES_ERROR("dblink_open", "begin error");
>
> PQclear(res);
>
> appendStringInfo(str, "DECLARE %s CURSOR FOR %s", curname, sql);
> ***************
> *** 177,195 ****
> if (!res ||
> (PQresultStatus(res) != PGRES_COMMAND_OK &&
> PQresultStatus(res) != PGRES_TUPLES_OK))
> ! {
> ! msg = pstrdup(PQerrorMessage(conn));
> !
> ! PQclear(res);
>
> ! PQfinish(conn);
> ! persistent_conn = NULL;
> !
> ! elog(ERROR, "dblink: sql error: %s", msg);
> ! }
>
> ! result_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum("OK")));
> ! PG_RETURN_TEXT_P(result_text);
> }
>
> /*
> --- 296,306 ----
> if (!res ||
> (PQresultStatus(res) != PGRES_COMMAND_OK &&
> PQresultStatus(res) != PGRES_TUPLES_OK))
> ! DBLINK_RES_ERROR("dblink_open", "sql error");
>
> ! PQclear(res);
>
> ! PG_RETURN_TEXT_P(GET_TEXT("OK"));
> }
>
> /*
> ***************
> *** 201,249 ****
> {
> PGconn *conn = NULL;
> PGresult *res = NULL;
> ! char *curname = GET_STR(PG_GETARG_TEXT_P(0));
> StringInfo str = makeStringInfo();
> - text *result_text;
> char *msg;
>
> ! if (persistent_conn != NULL)
> conn = persistent_conn;
> ! else
> ! elog(ERROR, "dblink_close: no connection available");
>
> appendStringInfo(str, "CLOSE %s", curname);
>
> /* close the cursor */
> res = PQexec(conn, str->data);
> if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
> ! {
> ! msg = pstrdup(PQerrorMessage(conn));
> ! PQclear(res);
> !
> ! PQfinish(persistent_conn);
> ! persistent_conn = NULL;
> !
> ! elog(ERROR, "dblink_close: sql error: %s", msg);
> ! }
>
> PQclear(res);
>
> /* commit the transaction */
> res = PQexec(conn, "COMMIT");
> if (PQresultStatus(res) != PGRES_COMMAND_OK)
> ! {
> ! msg = pstrdup(PQerrorMessage(conn));
> ! PQclear(res);
> !
> ! PQfinish(persistent_conn);
> ! persistent_conn = NULL;
>
> - elog(ERROR, "dblink_close: commit error: %s", msg);
> - }
> PQclear(res);
>
> ! result_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum("OK")));
> ! PG_RETURN_TEXT_P(result_text);
> }
>
> /*
> --- 312,357 ----
> {
> PGconn *conn = NULL;
> PGresult *res = NULL;
> ! char *curname = NULL;
> ! char *conname = NULL;
> StringInfo str = makeStringInfo();
> char *msg;
> + remoteConn *rcon = NULL;
>
> ! if (PG_NARGS() == 1)
> ! {
> ! curname = GET_STR(PG_GETARG_TEXT_P(0));
> conn = persistent_conn;
> ! }
> ! else if (PG_NARGS()==2)
> ! {
> ! conname = GET_STR(PG_GETARG_TEXT_P(0));
> ! curname = GET_STR(PG_GETARG_TEXT_P(1));
> ! rcon = getConnectionByName(conname);
> ! if(rcon)
> ! conn = rcon->con;
> ! }
> !
> ! if (!conn)
> ! DBLINK_CONN_NOT_AVAIL("dblink_close");
>
> appendStringInfo(str, "CLOSE %s", curname);
>
> /* close the cursor */
> res = PQexec(conn, str->data);
> if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
> ! DBLINK_RES_ERROR("dblink_close", "sql error");
>
> PQclear(res);
>
> /* commit the transaction */
> res = PQexec(conn, "COMMIT");
> if (PQresultStatus(res) != PGRES_COMMAND_OK)
> ! DBLINK_RES_ERROR("dblink_close", "commit error");
>
> PQclear(res);
>
> ! PG_RETURN_TEXT_P(GET_TEXT("OK"));
> }
>
> /*
> ***************
> *** 262,267 ****
> --- 370,377 ----
> char *msg;
> PGresult *res = NULL;
> MemoryContext oldcontext;
> + char *conname = NULL;
> + remoteConn *rcon=NULL;
>
> /* stuff done only on the first call of the function */
> if (SRF_IS_FIRSTCALL())
> ***************
> *** 271,278 ****
> Oid funcid = fcinfo->flinfo->fn_oid;
> PGconn *conn = NULL;
> StringInfo str = makeStringInfo();
> ! char *curname = GET_STR(PG_GETARG_TEXT_P(0));
> ! int howmany = PG_GETARG_INT32(1);
>
> /* create a function context for cross-call persistence */
> funcctx = SRF_FIRSTCALL_INIT();
> --- 381,408 ----
> Oid funcid = fcinfo->flinfo->fn_oid;
> PGconn *conn = NULL;
> StringInfo str = makeStringInfo();
> ! char *curname = NULL;
> ! int howmany = 0;
> !
> ! if (PG_NARGS() == 3)
> ! {
> ! conname = GET_STR(PG_GETARG_TEXT_P(0));
> ! curname = GET_STR(PG_GETARG_TEXT_P(1));
> ! howmany = PG_GETARG_INT32(2);
> !
> ! rcon = getConnectionByName(conname);
> ! if(rcon)
> ! conn = rcon->con;
> ! }
> ! else if (PG_NARGS() == 2)
> ! {
> ! curname = GET_STR(PG_GETARG_TEXT_P(0));
> ! howmany = PG_GETARG_INT32(1);
> ! conn = persistent_conn;
> ! }
> !
> ! if(!conn)
> ! DBLINK_CONN_NOT_AVAIL("dblink_fetch");
>
> /* create a function context for cross-call persistence */
> funcctx = SRF_FIRSTCALL_INIT();
> ***************
> *** 283,293 ****
> */
> oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
>
> - if (persistent_conn != NULL)
> - conn = persistent_conn;
> - else
> - elog(ERROR, "dblink_fetch: no connection available");
> -
> appendStringInfo(str, "FETCH %d FROM %s", howmany, curname);
>
> res = PQexec(conn, str->data);
> --- 413,418 ----
> ***************
> *** 295,313 ****
> (PQresultStatus(res) != PGRES_COMMAND_OK &&
> PQresultStatus(res) != PGRES_TUPLES_OK))
> {
> ! msg = pstrdup(PQerrorMessage(conn));
> ! PQclear(res);
> !
> ! PQfinish(persistent_conn);
> ! persistent_conn = NULL;
> !
> ! elog(ERROR, "dblink_fetch: sql error: %s", msg);
> }
> else if (PQresultStatus(res) == PGRES_COMMAND_OK)
> {
> /* cursor does not exist - closed already or bad name */
> PQclear(res);
> ! elog(ERROR, "dblink_fetch: cursor %s does not exist", curname);
> }
>
> funcctx->max_calls = PQntuples(res);
> --- 420,432 ----
> (PQresultStatus(res) != PGRES_COMMAND_OK &&
> PQresultStatus(res) != PGRES_TUPLES_OK))
> {
> ! DBLINK_RES_ERROR("dblink_fetch", "sql error");
> }
> else if (PQresultStatus(res) == PGRES_COMMAND_OK)
> {
> /* cursor does not exist - closed already or bad name */
> PQclear(res);
> ! elog(ERROR, "dblink_fetch: cursor not found: %s", curname);
> }
>
> funcctx->max_calls = PQntuples(res);
> ***************
> *** 382,389 ****
> SRF_RETURN_NEXT(funcctx, result);
> }
> else
> - /* do when there is no more left */
> {
> PQclear(res);
> SRF_RETURN_DONE(funcctx);
> }
> --- 501,508 ----
> SRF_RETURN_NEXT(funcctx, result);
> }
> else
> {
> + /* do when there is no more left */
> PQclear(res);
> SRF_RETURN_DONE(funcctx);
> }
> ***************
> *** 407,412 ****
> --- 526,532 ----
> bool is_sql_cmd = false;
> char *sql_cmd_status = NULL;
> MemoryContext oldcontext;
> + bool freeconn = true;
>
> /* stuff done only on the first call of the function */
> if (SRF_IS_FIRSTCALL())
> ***************
> *** 417,422 ****
> --- 537,544 ----
> PGconn *conn = NULL;
> char *connstr = NULL;
> char *sql = NULL;
> + char *conname = NULL;
> + remoteConn *rcon=NULL;
>
> /* create a function context for cross-call persistence */
> funcctx = SRF_FIRSTCALL_INIT();
> ***************
> *** 427,496 ****
> */
> oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
>
> ! if (fcinfo->nargs == 2)
> {
> ! connstr = GET_STR(PG_GETARG_TEXT_P(0));
> sql = GET_STR(PG_GETARG_TEXT_P(1));
> -
> - conn = PQconnectdb(connstr);
> - if (PQstatus(conn) == CONNECTION_BAD)
> - {
> - msg = pstrdup(PQerrorMessage(conn));
> - PQfinish(conn);
> - elog(ERROR, "dblink: connection error: %s", msg);
> - }
> }
> ! else if (fcinfo->nargs == 1)
> {
> sql = GET_STR(PG_GETARG_TEXT_P(0));
> -
> - if (persistent_conn != NULL)
> - conn = persistent_conn;
> - else
> - elog(ERROR, "dblink: no connection available");
> }
> else
> elog(ERROR, "dblink: wrong number of arguments");
>
> res = PQexec(conn, sql);
> if (!res || (PQresultStatus(res) != PGRES_COMMAND_OK && PQresultStatus(res) != PGRES_TUPLES_OK))
> {
> ! msg = pstrdup(PQerrorMessage(conn));
> ! PQclear(res);
> ! PQfinish(conn);
> ! if (fcinfo->nargs == 1)
> ! persistent_conn = NULL;
>
> ! elog(ERROR, "dblink: sql error: %s", msg);
> }
> else
> ! {
> ! if (PQresultStatus(res) == PGRES_COMMAND_OK)
> ! {
> ! is_sql_cmd = true;
>
> ! /* need a tuple descriptor representing one TEXT column */
> ! tupdesc = CreateTemplateTupleDesc(1, false);
> ! TupleDescInitEntry(tupdesc, (AttrNumber) 1, "status",
> ! TEXTOID, -1, 0, false);
> !
> ! /*
> ! * and save a copy of the command status string to return
> ! * as our result tuple
> ! */
> ! sql_cmd_status = PQcmdStatus(res);
> ! funcctx->max_calls = 1;
> ! }
> ! else
> ! funcctx->max_calls = PQntuples(res);
> !
> ! /* got results, keep track of them */
> ! funcctx->user_fctx = res;
>
> ! /* if needed, close the connection to the database and cleanup */
> ! if (fcinfo->nargs == 2)
> ! PQfinish(conn);
> ! }
>
> /* fast track when no results */
> if (funcctx->max_calls < 1)
> --- 549,599 ----
> */
> oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
>
> ! if (PG_NARGS() == 2)
> {
> ! DBLINK_GET_CONN("dblink");
> sql = GET_STR(PG_GETARG_TEXT_P(1));
> }
> ! else if (PG_NARGS() == 1)
> {
> + conn = persistent_conn;
> sql = GET_STR(PG_GETARG_TEXT_P(0));
> }
> else
> elog(ERROR, "dblink: wrong number of arguments");
>
> + if(!conn)
> + DBLINK_CONN_NOT_AVAIL("dblink_record");
> +
> res = PQexec(conn, sql);
> if (!res || (PQresultStatus(res) != PGRES_COMMAND_OK && PQresultStatus(res) != PGRES_TUPLES_OK))
> + DBLINK_RES_ERROR("dblink", "sql error");
> +
> + if (PQresultStatus(res) == PGRES_COMMAND_OK)
> {
> ! is_sql_cmd = true;
> !
> ! /* need a tuple descriptor representing one TEXT column */
> ! tupdesc = CreateTemplateTupleDesc(1, false);
> ! TupleDescInitEntry(tupdesc, (AttrNumber) 1, "status",
> ! TEXTOID, -1, 0, false);
>
> ! /*
> ! * and save a copy of the command status string to return
> ! * as our result tuple
> ! */
> ! sql_cmd_status = PQcmdStatus(res);
> ! funcctx->max_calls = 1;
> }
> else
> ! funcctx->max_calls = PQntuples(res);
>
> ! /* got results, keep track of them */
> ! funcctx->user_fctx = res;
>
> ! /* if needed, close the connection to the database and cleanup */
> ! if (freeconn && PG_NARGS() == 2)
> ! PQfinish(conn);
>
> /* fast track when no results */
> if (funcctx->max_calls < 1)
> ***************
> *** 571,578 ****
> SRF_RETURN_NEXT(funcctx, result);
> }
> else
> - /* do when there is no more left */
> {
> PQclear(res);
> SRF_RETURN_DONE(funcctx);
> }
> --- 674,681 ----
> SRF_RETURN_NEXT(funcctx, result);
> }
> else
> {
> + /* do when there is no more left */
> PQclear(res);
> SRF_RETURN_DONE(funcctx);
> }
> ***************
> *** 587,858 ****
> {
> char *msg;
> PGresult *res = NULL;
> ! char *sql_cmd_status = NULL;
> TupleDesc tupdesc = NULL;
> - text *result_text;
> PGconn *conn = NULL;
> char *connstr = NULL;
> char *sql = NULL;
>
> ! if (fcinfo->nargs == 2)
> {
> ! connstr = GET_STR(PG_GETARG_TEXT_P(0));
> sql = GET_STR(PG_GETARG_TEXT_P(1));
> -
> - conn = PQconnectdb(connstr);
> - if (PQstatus(conn) == CONNECTION_BAD)
> - {
> - msg = pstrdup(PQerrorMessage(conn));
> - PQfinish(conn);
> - elog(ERROR, "dblink_exec: connection error: %s", msg);
> - }
> }
> ! else if (fcinfo->nargs == 1)
> {
> sql = GET_STR(PG_GETARG_TEXT_P(0));
> -
> - if (persistent_conn != NULL)
> - conn = persistent_conn;
> - else
> - elog(ERROR, "dblink_exec: no connection available");
> }
> else
> elog(ERROR, "dblink_exec: wrong number of arguments");
>
>
> res = PQexec(conn, sql);
> ! if (!res || (PQresultStatus(res) != PGRES_COMMAND_OK && PQresultStatus(res) != PGRES_TUPLES_OK))
> {
> ! msg = pstrdup(PQerrorMessage(conn));
> ! PQclear(res);
> ! PQfinish(conn);
> ! if (fcinfo->nargs == 1)
> ! persistent_conn = NULL;
>
> ! elog(ERROR, "dblink_exec: sql error: %s", msg);
> }
> else
> ! {
> ! if (PQresultStatus(res) == PGRES_COMMAND_OK)
> ! {
> ! /* need a tuple descriptor representing one TEXT column */
> ! tupdesc = CreateTemplateTupleDesc(1, false);
> ! TupleDescInitEntry(tupdesc, (AttrNumber) 1, "status",
> ! TEXTOID, -1, 0, false);
>
> - /*
> - * and save a copy of the command status string to return as
> - * our result tuple
> - */
> - sql_cmd_status = PQcmdStatus(res);
> - }
> - else
> - elog(ERROR, "dblink_exec: queries returning results not allowed");
> - }
> PQclear(res);
>
> /* if needed, close the connection to the database and cleanup */
> ! if (fcinfo->nargs == 2)
> PQfinish(conn);
>
> ! result_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(sql_cmd_status)));
> ! PG_RETURN_TEXT_P(result_text);
> }
>
> - /*
> - * Note: this original version of dblink is DEPRECATED;
> - * it *will* be removed in favor of the new version on next release
> - */
> - PG_FUNCTION_INFO_V1(dblink);
> - Datum
> - dblink(PG_FUNCTION_ARGS)
> - {
> - PGconn *conn = NULL;
> - PGresult *res = NULL;
> - dblink_results *results;
> - char *optstr;
> - char *sqlstatement;
> - char *execstatement;
> - char *msg;
> - int ntuples = 0;
> - ReturnSetInfo *rsi;
> -
> - if (fcinfo->resultinfo == NULL || !IsA(fcinfo->resultinfo, ReturnSetInfo))
> - elog(ERROR, "dblink: function called in context that does not accept a set result");
> -
> - optstr = DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(PG_GETARG_TEXT_P(0))));
> - sqlstatement = DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(PG_GETARG_TEXT_P(1))));
> -
> - if (fcinfo->flinfo->fn_extra == NULL)
> - {
> -
> - conn = PQconnectdb(optstr);
> - if (PQstatus(conn) == CONNECTION_BAD)
> - {
> - msg = pstrdup(PQerrorMessage(conn));
> - PQfinish(conn);
> - elog(ERROR, "dblink: connection error: %s", msg);
> - }
> -
> - execstatement = (char *) palloc(strlen(sqlstatement) + 1);
> - if (execstatement != NULL)
> - {
> - strcpy(execstatement, sqlstatement);
> - strcat(execstatement, "\0");
> - }
> - else
> - elog(ERROR, "dblink: insufficient memory");
> -
> - res = PQexec(conn, execstatement);
> - if (!res || (PQresultStatus(res) != PGRES_COMMAND_OK && PQresultStatus(res) != PGRES_TUPLES_OK))
> - {
> - msg = pstrdup(PQerrorMessage(conn));
> - PQclear(res);
> - PQfinish(conn);
> - elog(ERROR, "dblink: sql error: %s", msg);
> - }
> - else
> - {
> - /*
> - * got results, start fetching them
> - */
> - ntuples = PQntuples(res);
> -
> - /*
> - * increment resource index
> - */
> - res_id_index++;
> -
> - results = init_dblink_results(fcinfo->flinfo->fn_mcxt);
> - results->tup_num = 0;
> - results->res_id_index = res_id_index;
> - results->res = res;
> -
> - /*
> - * Append node to res_id to hold pointer to results. Needed by
> - * dblink_tok to access the data
> - */
> - append_res_ptr(results);
> -
> - /*
> - * save pointer to results for the next function manager call
> - */
> - fcinfo->flinfo->fn_extra = (void *) results;
> -
> - /* close the connection to the database and cleanup */
> - PQfinish(conn);
> -
> - rsi = (ReturnSetInfo *) fcinfo->resultinfo;
> - rsi->isDone = ExprMultipleResult;
> -
> - PG_RETURN_INT32(res_id_index);
> - }
> - }
> - else
> - {
> - /*
> - * check for more results
> - */
> - results = fcinfo->flinfo->fn_extra;
> -
> - results->tup_num++;
> - res_id_index = results->res_id_index;
> - ntuples = PQntuples(results->res);
> -
> - if (results->tup_num < ntuples)
> - {
> - /*
> - * fetch them if available
> - */
> -
> - rsi = (ReturnSetInfo *) fcinfo->resultinfo;
> - rsi->isDone = ExprMultipleResult;
> -
> - PG_RETURN_INT32(res_id_index);
> - }
> - else
> - {
> - /*
> - * or if no more, clean things up
> - */
> - results = fcinfo->flinfo->fn_extra;
> -
> - remove_res_ptr(results);
> - PQclear(results->res);
> - pfree(results);
> - fcinfo->flinfo->fn_extra = NULL;
> -
> - rsi = (ReturnSetInfo *) fcinfo->resultinfo;
> - rsi->isDone = ExprEndResult;
> -
> - PG_RETURN_NULL();
> - }
> - }
> - PG_RETURN_NULL();
> - }
> -
> - /*
> - * Note: dblink_tok is DEPRECATED;
> - * it *will* be removed in favor of the new version on next release
> - *
> - * dblink_tok
> - * parse dblink output string
> - * return fldnum item (0 based)
> - * based on provided field separator
> - */
> - PG_FUNCTION_INFO_V1(dblink_tok);
> - Datum
> - dblink_tok(PG_FUNCTION_ARGS)
> - {
> - dblink_results *results;
> - int fldnum;
> - text *result_text;
> - char *result;
> - int nfields = 0;
> - int text_len = 0;
> -
> - results = get_res_ptr(PG_GETARG_INT32(0));
> - if (results == NULL)
> - {
> - if (res_id != NIL)
> - {
> - freeList(res_id);
> - res_id = NIL;
> - res_id_index = 0;
> - }
> -
> - elog(ERROR, "dblink_tok: function called with invalid resource id");
> - }
> -
> - fldnum = PG_GETARG_INT32(1);
> - if (fldnum < 0)
> - elog(ERROR, "dblink_tok: field number < 0 not permitted");
> -
> - nfields = PQnfields(results->res);
> - if (fldnum > (nfields - 1))
> - elog(ERROR, "dblink_tok: field number %d does not exist", fldnum);
> -
> - if (PQgetisnull(results->res, results->tup_num, fldnum) == 1)
> - PG_RETURN_NULL();
> - else
> - {
> - text_len = PQgetlength(results->res, results->tup_num, fldnum);
> -
> - result = (char *) palloc(text_len + 1);
> -
> - if (result != NULL)
> - {
> - strcpy(result, PQgetvalue(results->res, results->tup_num, fldnum));
> - strcat(result, "\0");
> - }
> - else
> - elog(ERROR, "dblink: insufficient memory");
> -
> - result_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(result)));
> -
> - PG_RETURN_TEXT_P(result_text);
> - }
> - }
>
> /*
> * dblink_get_pkey
> --- 690,751 ----
> {
> char *msg;
> PGresult *res = NULL;
> ! text *sql_cmd_status = NULL;
> TupleDesc tupdesc = NULL;
> PGconn *conn = NULL;
> char *connstr = NULL;
> char *sql = NULL;
> + char *conname = NULL;
> + remoteConn *rcon=NULL;
> + bool freeconn = true;
>
> ! if (PG_NARGS() == 2)
> {
> ! DBLINK_GET_CONN("dblink_exec");
> sql = GET_STR(PG_GETARG_TEXT_P(1));
> }
> ! else if (PG_NARGS() == 1)
> {
> + conn = persistent_conn;
> sql = GET_STR(PG_GETARG_TEXT_P(0));
> }
> else
> elog(ERROR, "dblink_exec: wrong number of arguments");
>
> + if(!conn)
> + DBLINK_CONN_NOT_AVAIL("dblink_exec");
>
> res = PQexec(conn, sql);
> ! if (!res ||
> ! (PQresultStatus(res) != PGRES_COMMAND_OK &&
> ! PQresultStatus(res) != PGRES_TUPLES_OK))
> ! DBLINK_RES_ERROR("dblink_exec", "sql error");
> !
> ! if (PQresultStatus(res) == PGRES_COMMAND_OK)
> {
> ! /* need a tuple descriptor representing one TEXT column */
> ! tupdesc = CreateTemplateTupleDesc(1, false);
> ! TupleDescInitEntry(tupdesc, (AttrNumber) 1, "status",
> ! TEXTOID, -1, 0, false);
>
> ! /*
> ! * and save a copy of the command status string to return as
> ! * our result tuple
> ! */
> ! sql_cmd_status = GET_TEXT(PQcmdStatus(res));
> }
> else
> ! elog(ERROR, "dblink_exec: queries returning results not allowed");
>
> PQclear(res);
>
> /* if needed, close the connection to the database and cleanup */
> ! if (freeconn && fcinfo->nargs == 2)
> PQfinish(conn);
>
> ! PG_RETURN_TEXT_P(sql_cmd_status);
> }
>
>
> /*
> * dblink_get_pkey
> ***************
> *** 927,933 ****
> funcctx->user_fctx = results;
> }
> else
> ! /* fast track when no results */
> SRF_RETURN_DONE(funcctx);
>
> MemoryContextSwitchTo(oldcontext);
> --- 820,826 ----
> funcctx->user_fctx = results;
> }
> else
> ! /* fast track when no results */
> SRF_RETURN_DONE(funcctx);
>
> MemoryContextSwitchTo(oldcontext);
> ***************
> *** 969,1005 ****
> SRF_RETURN_NEXT(funcctx, result);
> }
> else
> - /* do when there is no more left */
> - SRF_RETURN_DONE(funcctx);
> - }
> -
> - /*
> - * Note: dblink_last_oid is DEPRECATED;
> - * it *will* be removed on next release
> - *
> - * dblink_last_oid
> - * return last inserted oid
> - */
> - PG_FUNCTION_INFO_V1(dblink_last_oid);
> - Datum
> - dblink_last_oid(PG_FUNCTION_ARGS)
> - {
> - dblink_results *results;
> -
> - results = get_res_ptr(PG_GETARG_INT32(0));
> - if (results == NULL)
> {
> ! if (res_id != NIL)
> ! {
> ! freeList(res_id);
> ! res_id = NIL;
> ! res_id_index = 0;
> ! }
> !
> ! elog(ERROR, "dblink_tok: function called with invalid resource id");
> }
> -
> - PG_RETURN_OID(PQoidValue(results->res));
> }
>
>
> --- 862,871 ----
> SRF_RETURN_NEXT(funcctx, result);
> }
> else
> {
> ! /* do when there is no more left */
> ! SRF_RETURN_DONE(funcctx);
> }
> }
>
>
> ***************
> *** 1047,1053 ****
> int i;
> char *ptr;
> char *sql;
> - text *sql_text;
> int16 typlen;
> bool typbyval;
> char typalign;
> --- 913,918 ----
> ***************
> *** 1143,1156 ****
> sql = get_sql_insert(relid, pkattnums, pknumatts, src_pkattvals, tgt_pkattvals);
>
> /*
> - * Make it into TEXT for return to the client
> - */
> - sql_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(sql)));
> -
> - /*
> * And send it
> */
> ! PG_RETURN_TEXT_P(sql_text);
> }
>
>
> --- 1008,1016 ----
> sql = get_sql_insert(relid, pkattnums, pknumatts, src_pkattvals, tgt_pkattvals);
>
> /*
> * And send it
> */
> ! PG_RETURN_TEXT_P(GET_TEXT(sql));
> }
>
>
> ***************
> *** 1186,1192 ****
> int i;
> char *ptr;
> char *sql;
> - text *sql_text;
> int16 typlen;
> bool typbyval;
> char typalign;
> --- 1046,1051 ----
> ***************
> *** 1251,1264 ****
> sql = get_sql_delete(relid, pkattnums, pknumatts, tgt_pkattvals);
>
> /*
> - * Make it into TEXT for return to the client
> - */
> - sql_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(sql)));
> -
> - /*
> * And send it
> */
> ! PG_RETURN_TEXT_P(sql_text);
> }
>
>
> --- 1110,1118 ----
> sql = get_sql_delete(relid, pkattnums, pknumatts, tgt_pkattvals);
>
> /*
> * And send it
> */
> ! PG_RETURN_TEXT_P(GET_TEXT(sql));
> }
>
>
> ***************
> *** 1303,1309 ****
> int i;
> char *ptr;
> char *sql;
> - text *sql_text;
> int16 typlen;
> bool typbyval;
> char typalign;
> --- 1157,1162 ----
> ***************
> *** 1399,1412 ****
> sql = get_sql_update(relid, pkattnums, pknumatts, src_pkattvals, tgt_pkattvals);
>
> /*
> - * Make it into TEXT for return to the client
> - */
> - sql_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(sql)));
> -
> - /*
> * And send it
> */
> ! PG_RETURN_TEXT_P(sql_text);
> }
>
> /*
> --- 1252,1260 ----
> sql = get_sql_update(relid, pkattnums, pknumatts, src_pkattvals, tgt_pkattvals);
>
> /*
> * And send it
> */
> ! PG_RETURN_TEXT_P(GET_TEXT(sql));
> }
>
> /*
> ***************
> *** 1419,1428 ****
> Datum
> dblink_current_query(PG_FUNCTION_ARGS)
> {
> ! text *result_text;
> !
> ! result_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(debug_query_string)));
> ! PG_RETURN_TEXT_P(result_text);
> }
>
>
> --- 1267,1273 ----
> Datum
> dblink_current_query(PG_FUNCTION_ARGS)
> {
> ! PG_RETURN_TEXT_P(GET_TEXT(debug_query_string));
> }
>
>
> ***************
> *** 1432,1460 ****
>
>
> /*
> - * init_dblink_results
> - * - create an empty dblink_results data structure
> - */
> - static dblink_results *
> - init_dblink_results(MemoryContext fn_mcxt)
> - {
> - MemoryContext oldcontext;
> - dblink_results *retval;
> -
> - oldcontext = MemoryContextSwitchTo(fn_mcxt);
> -
> - retval = (dblink_results *) palloc0(sizeof(dblink_results));
> -
> - retval->tup_num = -1;
> - retval->res_id_index = -1;
> - retval->res = NULL;
> -
> - MemoryContextSwitchTo(oldcontext);
> -
> - return retval;
> - }
> -
> - /*
> * get_pkey_attnames
> *
> * Get the primary key attnames for the given relation.
> --- 1277,1282 ----
> ***************
> *** 1492,1498 ****
> /* we're only interested if it is the primary key */
> if (index->indisprimary == TRUE)
> {
> ! *numatts = index->indnatts;
> if (*numatts > 0)
> {
> result = (char **) palloc(*numatts * sizeof(char *));
> --- 1314,1323 ----
> /* we're only interested if it is the primary key */
> if (index->indisprimary == TRUE)
> {
> ! i = 0;
> ! while (index->indkey[i++] != 0)
> ! (*numatts)++;
> !
> if (*numatts > 0)
> {
> result = (char **) palloc(*numatts * sizeof(char *));
> ***************
> *** 1911,1962 ****
> return relid;
> }
>
> - static dblink_results *
> - get_res_ptr(int32 res_id_index)
> - {
> - List *ptr;
> -
> - /*
> - * short circuit empty list
> - */
> - if (res_id == NIL)
> - return NULL;
> -
> - /*
> - * OK, should be good to go
> - */
> - foreach(ptr, res_id)
> - {
> - dblink_results *this_res_id = (dblink_results *) lfirst(ptr);
> -
> - if (this_res_id->res_id_index == res_id_index)
> - return this_res_id;
> - }
> - return NULL;
> - }
> -
> - /*
> - * Add node to global List res_id
> - */
> - static void
> - append_res_ptr(dblink_results * results)
> - {
> - res_id = lappend(res_id, results);
> - }
> -
> - /*
> - * Remove node from global List
> - * using res_id_index
> - */
> - static void
> - remove_res_ptr(dblink_results * results)
> - {
> - res_id = lremove(results, res_id);
> -
> - if (res_id == NIL)
> - res_id_index = 0;
> - }
> -
> static TupleDesc
> pgresultGetTupleDesc(PGresult *res)
> {
> --- 1736,1741 ----
> ***************
> *** 2042,2045 ****
> --- 1821,1912 ----
> ReleaseSysCache(tp);
>
> return result;
> + }
> +
> +
> + static remoteConn *
> + getConnectionByName(const char *name)
> + {
> + remoteConnHashEnt *hentry;
> + char key[NAMEDATALEN];
> +
> + if(!remoteConnHash)
> + remoteConnHash=createConnHash();
> +
> + MemSet(key, 0, NAMEDATALEN);
> + snprintf(key, NAMEDATALEN - 1, "%s", name);
> + hentry = (remoteConnHashEnt*) hash_search(remoteConnHash,
> + key, HASH_FIND, NULL);
> +
> + if(hentry)
> + return(hentry->rcon);
> +
> + return(NULL);
> + }
> +
> + static HTAB *
> + createConnHash(void)
> + {
> + HASHCTL ctl;
> + HTAB *ptr;
> +
> + ctl.keysize = NAMEDATALEN;
> + ctl.entrysize = sizeof(remoteConnHashEnt);
> +
> + ptr=hash_create("Remote Con hash", NUMCONN, &ctl, HASH_ELEM);
> +
> + if(!ptr)
> + elog(ERROR,"Can not create connections hash table. Out of memory");
> +
> + return(ptr);
> + }
> +
> + static bool
> + createNewConnection(const char *name, remoteConn *con)
> + {
> + remoteConnHashEnt *hentry;
> + bool found;
> + char key[NAMEDATALEN];
> +
> + if(!remoteConnHash)
> + remoteConnHash=createConnHash();
> +
> + MemSet(key, 0, NAMEDATALEN);
> + snprintf(key, NAMEDATALEN - 1, "%s", name);
> + hentry = (remoteConnHashEnt *) hash_search(remoteConnHash, key,
> + HASH_ENTER, &found);
> +
> + if(!hentry)
> + elog(ERROR, "failed to create connection");
> +
> + if(found)
> + {
> + elog(NOTICE, "cannot use a connection name more than once");
> + return false;
> + }
> +
> + hentry->rcon = con;
> + strncpy(hentry->name, name, NAMEDATALEN - 1);
> +
> + return true;
> + }
> +
> + static void
> + deleteConnection(const char *name)
> + {
> + remoteConnHashEnt *hentry;
> + bool found;
> + char key[NAMEDATALEN];
> +
> + if(!remoteConnHash)
> + remoteConnHash=createConnHash();
> +
> + MemSet(key, 0, NAMEDATALEN);
> + snprintf(key, NAMEDATALEN - 1, "%s", name);
> +
> + hentry = (remoteConnHashEnt *) hash_search(remoteConnHash,
> + key, HASH_REMOVE, &found);
> +
> + if(!hentry)
> + elog(WARNING,"Trying to delete a connection that does not exist");
> }
> Index: contrib/dblink/dblink.h
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/dblink.h,v
> retrieving revision 1.9
> diff -c -r1.9 dblink.h
> *** contrib/dblink/dblink.h 21 Oct 2002 18:57:34 -0000 1.9
> --- contrib/dblink/dblink.h 15 Jun 2003 04:18:08 -0000
> ***************
> *** 4,11 ****
> * Functions returning results from a remote database
> *
> * Joe Conway <mail@joeconway.com>
> *
> ! * Copyright (c) 2001, 2002 by PostgreSQL Global Development Group
> * ALL RIGHTS RESERVED;
> *
> * Permission to use, copy, modify, and distribute this software and its
> --- 4,14 ----
> * Functions returning results from a remote database
> *
> * Joe Conway <mail@joeconway.com>
> + * And contributors:
> + * Darko Prenosil <Darko.Prenosil@finteh.hr>
> + * Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
> *
> ! * Copyright (c) 2001, 2002, 2003 by PostgreSQL Global Development Group
> * ALL RIGHTS RESERVED;
> *
> * Permission to use, copy, modify, and distribute this software and its
> ***************
> *** 31,65 ****
> #define DBLINK_H
>
> /*
> - * This struct holds the results of the remote query.
> - * Use fn_extra to hold a pointer to it across calls
> - */
> - typedef struct
> - {
> - /*
> - * last tuple number accessed
> - */
> - int tup_num;
> -
> - /*
> - * resource index number for this context
> - */
> - int res_id_index;
> -
> - /*
> - * the actual query results
> - */
> - PGresult *res;
> - } dblink_results;
> -
> - /*
> * External declarations
> */
> - /* deprecated */
> - extern Datum dblink(PG_FUNCTION_ARGS);
> - extern Datum dblink_tok(PG_FUNCTION_ARGS);
> -
> - /* supported */
> extern Datum dblink_connect(PG_FUNCTION_ARGS);
> extern Datum dblink_disconnect(PG_FUNCTION_ARGS);
> extern Datum dblink_open(PG_FUNCTION_ARGS);
> --- 34,41 ----
> ***************
> *** 68,74 ****
> extern Datum dblink_record(PG_FUNCTION_ARGS);
> extern Datum dblink_exec(PG_FUNCTION_ARGS);
> extern Datum dblink_get_pkey(PG_FUNCTION_ARGS);
> - extern Datum dblink_last_oid(PG_FUNCTION_ARGS);
> 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);
> --- 44,49 ----
> Index: contrib/dblink/dblink.sql.in
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/dblink.sql.in,v
> retrieving revision 1.7
> diff -c -r1.7 dblink.sql.in
> *** contrib/dblink/dblink.sql.in 18 Oct 2002 18:41:19 -0000 1.7
> --- contrib/dblink/dblink.sql.in 15 Jun 2003 03:59:54 -0000
> ***************
> *** 1,50 ****
> - --
> - -- Uncomment the following commented lines to use original DEPRECATED functions
> - --
> - --CREATE OR REPLACE FUNCTION dblink (text,text)
> - --RETURNS setof int
> - --AS 'MODULE_PATHNAME','dblink'
> - --LANGUAGE 'C' WITH (isstrict);
> - --CREATE OR REPLACE FUNCTION dblink_tok (int,int)
> - --RETURNS text
> - --AS 'MODULE_PATHNAME','dblink_tok'
> - --LANGUAGE 'C' WITH (isstrict);
> - --CREATE OR REPLACE FUNCTION dblink_last_oid (int)
> - --RETURNS oid
> - --AS 'MODULE_PATHNAME','dblink_last_oid'
> - --LANGUAGE 'C' WITH (isstrict);
> -
> CREATE OR REPLACE FUNCTION dblink_connect (text)
> RETURNS text
> AS 'MODULE_PATHNAME','dblink_connect'
> LANGUAGE 'C' WITH (isstrict);
>
> CREATE OR REPLACE FUNCTION dblink_disconnect ()
> RETURNS text
> AS 'MODULE_PATHNAME','dblink_disconnect'
> LANGUAGE 'C' WITH (isstrict);
>
> CREATE OR REPLACE FUNCTION dblink_open (text,text)
> RETURNS text
> AS 'MODULE_PATHNAME','dblink_open'
> LANGUAGE 'C' WITH (isstrict);
>
> CREATE OR REPLACE FUNCTION dblink_fetch (text,int)
> RETURNS setof record
> AS 'MODULE_PATHNAME','dblink_fetch'
> LANGUAGE 'C' WITH (isstrict);
>
> CREATE OR REPLACE FUNCTION dblink_close (text)
> RETURNS text
> AS 'MODULE_PATHNAME','dblink_close'
> LANGUAGE 'C' WITH (isstrict);
>
> ! -- Note: if this is not a first time install of dblink, uncomment the
> ! -- following DROP which prepares the database for the new, non-deprecated
> ! -- version.
> ! --DROP FUNCTION dblink (text,text);
>
> - -- Comment out the following 3 lines if the DEPRECATED functions are used.
> CREATE OR REPLACE FUNCTION dblink (text,text)
> RETURNS setof record
> AS 'MODULE_PATHNAME','dblink_record'
> --- 1,53 ----
> CREATE OR REPLACE FUNCTION dblink_connect (text)
> RETURNS text
> AS 'MODULE_PATHNAME','dblink_connect'
> LANGUAGE 'C' WITH (isstrict);
>
> + CREATE OR REPLACE FUNCTION dblink_connect (text, text)
> + RETURNS text
> + AS 'MODULE_PATHNAME','dblink_connect'
> + LANGUAGE 'C' WITH (isstrict);
> +
> CREATE OR REPLACE FUNCTION dblink_disconnect ()
> RETURNS text
> AS 'MODULE_PATHNAME','dblink_disconnect'
> LANGUAGE 'C' WITH (isstrict);
>
> + CREATE OR REPLACE FUNCTION dblink_disconnect (text)
> + RETURNS text
> + AS 'MODULE_PATHNAME','dblink_disconnect'
> + LANGUAGE 'C' WITH (isstrict);
> +
> CREATE OR REPLACE FUNCTION dblink_open (text,text)
> RETURNS text
> AS 'MODULE_PATHNAME','dblink_open'
> LANGUAGE 'C' WITH (isstrict);
>
> + CREATE OR REPLACE FUNCTION dblink_open (text,text,text)
> + RETURNS text
> + AS 'MODULE_PATHNAME','dblink_open'
> + LANGUAGE 'C' WITH (isstrict);
> +
> CREATE OR REPLACE FUNCTION dblink_fetch (text,int)
> RETURNS setof record
> AS 'MODULE_PATHNAME','dblink_fetch'
> LANGUAGE 'C' WITH (isstrict);
>
> + CREATE OR REPLACE FUNCTION dblink_fetch (text,text,int)
> + RETURNS setof record
> + AS 'MODULE_PATHNAME','dblink_fetch'
> + LANGUAGE 'C' WITH (isstrict);
> +
> CREATE OR REPLACE FUNCTION dblink_close (text)
> RETURNS text
> AS 'MODULE_PATHNAME','dblink_close'
> LANGUAGE 'C' WITH (isstrict);
>
> ! CREATE OR REPLACE FUNCTION dblink_close (text,text)
> ! RETURNS text
> ! AS 'MODULE_PATHNAME','dblink_close'
> ! LANGUAGE 'C' WITH (isstrict);
>
> CREATE OR REPLACE FUNCTION dblink (text,text)
> RETURNS setof record
> AS 'MODULE_PATHNAME','dblink_record'
> Index: contrib/dblink/doc/connection
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/doc/connection,v
> retrieving revision 1.1
> diff -c -r1.1 connection
> *** contrib/dblink/doc/connection 2 Sep 2002 06:32:41 -0000 1.1
> --- contrib/dblink/doc/connection 15 Jun 2003 05:26:53 -0000
> ***************
> *** 6,26 ****
> Synopsis
>
> dblink_connect(text connstr)
>
> Inputs
>
> connstr
>
> standard libpq format connection string,
> e.g. "hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd"
>
> Outputs
>
> Returns status = "OK"
>
> Example usage
>
> ! test=# select dblink_connect('dbname=template1');
> dblink_connect
> ----------------
> OK
> --- 6,40 ----
> Synopsis
>
> dblink_connect(text connstr)
> + dblink_connect(text connname, text connstr)
>
> Inputs
>
> + connname
> + if 2 arguments are given, the first is used as a name for a persistent
> + connection
> +
> connstr
>
> standard libpq format connection string,
> e.g. "hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd"
>
> + if only one argument is given, the connection is unnamed; only one unnamed
> + connection can exist at a time
> +
> Outputs
>
> Returns status = "OK"
>
> Example usage
>
> ! select dblink_connect('dbname=template1');
> ! dblink_connect
> ! ----------------
> ! OK
> ! (1 row)
> !
> ! select dblink_connect('myconn','dbname=template1');
> dblink_connect
> ----------------
> OK
> ***************
> *** 29,43 ****
> ==================================================================
> Name
>
> ! dblink_disconnect -- Closes the persistent connection to a remote database
>
> Synopsis
>
> dblink_disconnect()
>
> Inputs
>
> ! none
>
> Outputs
>
> --- 43,60 ----
> ==================================================================
> Name
>
> ! dblink_disconnect -- Closes a persistent connection to a remote database
>
> Synopsis
>
> dblink_disconnect()
> + dblink_disconnect(text connname)
>
> Inputs
>
> ! connname
> ! if an argument is given, it is used as a name for a persistent
> ! connection to close; otherwiase the unnamed connection is closed
>
> Outputs
>
> ***************
> *** 51,53 ****
> --- 68,75 ----
> OK
> (1 row)
>
> + select dblink_disconnect('myconn');
> + dblink_disconnect
> + -------------------
> + OK
> + (1 row)
> Index: contrib/dblink/doc/cursor
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/doc/cursor,v
> retrieving revision 1.1
> diff -c -r1.1 cursor
> *** contrib/dblink/doc/cursor 2 Sep 2002 06:32:41 -0000 1.1
> --- contrib/dblink/doc/cursor 15 Jun 2003 05:42:15 -0000
> ***************
> *** 6,14 ****
> --- 6,19 ----
> Synopsis
>
> dblink_open(text cursorname, text sql)
> + dblink_open(text connname, text cursorname, text sql)
>
> Inputs
>
> + connname
> + if three arguments are present, the first is taken as the specific
> + connection name to use; otherwise the unnamed connection is assumed
> +
> cursorname
>
> a reference name for the cursor
> ***************
> *** 52,60 ****
> --- 57,70 ----
> Synopsis
>
> dblink_fetch(text cursorname, int32 howmany)
> + dblink_fetch(text connname, text cursorname, int32 howmany)
>
> Inputs
>
> + connname
> + if three arguments are present, the first is taken as the specific
> + connection name to use; otherwise the unnamed connection is assumed
> +
> cursorname
>
> The reference name for the cursor
> ***************
> *** 123,131 ****
> --- 133,146 ----
> Synopsis
>
> dblink_close(text cursorname)
> + dblink_close(text connname, text cursorname)
>
> Inputs
>
> + connname
> + if two arguments are present, the first is taken as the specific
> + connection name to use; otherwise the unnamed connection is assumed
> +
> cursorname
>
> a reference name for the cursor
> ***************
> *** 135,141 ****
> Returns status = "OK"
>
> Note
> ! dblink_connect(text connstr) must be executed first.
>
> Example usage
>
> --- 150,157 ----
> Returns status = "OK"
>
> Note
> ! dblink_connect(text connstr) or dblink_connect(text connname, text connstr)
> ! must be executed first.
>
> Example usage
>
> ***************
> *** 157,159 ****
> --- 173,192 ----
> OK
> (1 row)
>
> + select dblink_connect('myconn','dbname=regression');
> + dblink_connect
> + ----------------
> + OK
> + (1 row)
> +
> + select dblink_open('myconn','foo','select proname, prosrc from pg_proc');
> + dblink_open
> + -------------
> + OK
> + (1 row)
> +
> + select dblink_close('myconn','foo');
> + dblink_close
> + --------------
> + OK
> + (1 row)
> Index: contrib/dblink/doc/deprecated
> ===================================================================
> RCS file: contrib/dblink/doc/deprecated
> diff -N contrib/dblink/doc/deprecated
> *** contrib/dblink/doc/deprecated 2 Sep 2002 06:32:41 -0000 1.1
> --- /dev/null 1 Jan 1970 00:00:00 -0000
> ***************
> *** 1,105 ****
> - ==================================================================
> - Name
> -
> - *DEPRECATED* use new dblink syntax
> - dblink -- Returns a resource id for a data set from a remote database
> -
> - Synopsis
> -
> - dblink(text connstr, text sql)
> -
> - Inputs
> -
> - connstr
> -
> - standard libpq format connection srting,
> - e.g. "hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd"
> -
> - sql
> -
> - sql statement that you wish to execute on the remote host
> - e.g. "select * from pg_class"
> -
> - Outputs
> -
> - Returns setof int (res_id)
> -
> - Example usage
> -
> - select dblink('hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd'
> - ,'select f1, f2 from mytable');
> -
> - ==================================================================
> -
> - Name
> -
> - *DEPRECATED* use new dblink syntax
> - dblink_tok -- Returns individual select field results from a dblink remote query
> -
> - Synopsis
> -
> - dblink_tok(int res_id, int fnumber)
> -
> - Inputs
> -
> - res_id
> -
> - a resource id returned by a call to dblink()
> -
> - fnumber
> -
> - the ordinal position (zero based) of the field to be returned from the dblink result set
> -
> - Outputs
> -
> - Returns text
> -
> - Example usage
> -
> - select dblink_tok(t1.dblink_p,0) as f1, dblink_tok(t1.dblink_p,1) as f2
> - from (select dblink('hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd'
> - ,'select f1, f2 from mytable') as dblink_p) as t1;
> -
> -
> - ==================================================================
> - *DEPRECATED* use new dblink syntax
> - A more convenient way to use dblink may be to create a view:
> -
> - create view myremotetable as
> - select dblink_tok(t1.dblink_p,0) as f1, dblink_tok(t1.dblink_p,1) as f2
> - from (select dblink('hostaddr=127.0.0.1 port=5432 dbname=template1 user=postgres password=postgres'
> - ,'select proname, prosrc from pg_proc') as dblink_p) as t1;
> -
> - Then you can simply write:
> -
> - select f1, f2 from myremotetable where f1 like 'bytea%';
> -
> - ==================================================================
> - Name
> - *DEPRECATED* use new dblink_exec syntax
> - dblink_last_oid -- Returns last inserted oid
> -
> - Synopsis
> -
> - dblink_last_oid(int res_id) RETURNS oid
> -
> - Inputs
> -
> - res_id
> -
> - any resource id returned by dblink function;
> -
> - Outputs
> -
> - Returns oid of last inserted tuple
> -
> - Example usage
> -
> - test=# select dblink_last_oid(dblink('hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd'
> - ,'insert into mytable (f1, f2) values (1,2)'));
> -
> - dblink_last_oid
> - ----------------
> - 16553
> - (1 row)
> -
> --- 0 ----
> Index: contrib/dblink/doc/execute
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/doc/execute,v
> retrieving revision 1.1
> diff -c -r1.1 execute
> *** contrib/dblink/doc/execute 2 Sep 2002 06:32:41 -0000 1.1
> --- contrib/dblink/doc/execute 15 Jun 2003 05:41:10 -0000
> ***************
> *** 6,27 ****
> Synopsis
>
> dblink_exec(text connstr, text sql)
> ! - or -
> dblink_exec(text sql)
>
> Inputs
>
> connstr
>
> ! standard libpq format connection string,
> ! e.g. "hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd"
> ! If the second form is used, then the dblink_connect(text connstr) must be
> ! executed first.
>
> sql
>
> sql statement that you wish to execute on the remote host, e.g.:
> -
> insert into foo values(0,'a','{"a0","b0","c0"}');
>
> Outputs
> --- 6,28 ----
> Synopsis
>
> dblink_exec(text connstr, text sql)
> ! dblink_exec(text connname, text sql)
> dblink_exec(text sql)
>
> Inputs
>
> + connname
> connstr
> + If two arguments are present, the first is first assumed to be a specific
> + connection name to use. If the name is not found, the argument is then
> + assumed to be a valid connection string, of standard libpq format,
> + e.g.: "hostaddr=127.0.0.1 dbname=mydb user=postgres password=mypasswd"
>
> ! If only one argument is used, then the unnamed connection is used.
>
> sql
>
> sql statement that you wish to execute on the remote host, e.g.:
> insert into foo values(0,'a','{"a0","b0","c0"}');
>
> Outputs
> ***************
> *** 36,49 ****
>
> Example usage
>
> ! test=# select dblink_connect('dbname=dblink_test_slave');
> dblink_connect
> ----------------
> OK
> (1 row)
>
> ! test=# select dblink_exec('insert into foo values(21,''z'',''{"a0","b0","c0"}'');');
> dblink_exec
> -----------------
> INSERT 943366 1
> (1 row)
> --- 37,62 ----
>
> Example usage
>
> ! select dblink_connect('dbname=dblink_test_slave');
> dblink_connect
> ----------------
> OK
> (1 row)
>
> ! select dblink_exec('insert into foo values(21,''z'',''{"a0","b0","c0"}'');');
> dblink_exec
> -----------------
> INSERT 943366 1
> + (1 row)
> +
> + select dblink_connect('myconn','dbname=regression');
> + dblink_connect
> + ----------------
> + OK
> + (1 row)
> +
> + select dblink_exec('myconn','insert into foo values(21,''z'',''{"a0","b0","c0"}'');');
> + dblink_exec
> + ------------------
> + INSERT 6432584 1
> (1 row)
> Index: contrib/dblink/doc/query
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/doc/query,v
> retrieving revision 1.1
> diff -c -r1.1 query
> *** contrib/dblink/doc/query 2 Sep 2002 06:32:41 -0000 1.1
> --- contrib/dblink/doc/query 15 Jun 2003 05:39:31 -0000
> ***************
> *** 6,22 ****
> Synopsis
>
> dblink(text connstr, text sql)
> ! - or -
> dblink(text sql)
>
> Inputs
>
> connstr
>
> ! standard libpq format connection string,
> ! e.g. "hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd"
> ! If the second form is used, then the dblink_connect(text connstr) must be
> ! executed first.
>
> sql
>
> --- 6,24 ----
> Synopsis
>
> dblink(text connstr, text sql)
> ! dblink(text connname, text sql)
> dblink(text sql)
>
> Inputs
>
> + connname
> connstr
> + If two arguments are present, the first is first assumed to be a specific
> + connection name to use. If the name is not found, the argument is then
> + assumed to be a valid connection string, of standard libpq format,
> + e.g.: "hostaddr=127.0.0.1 dbname=mydb user=postgres password=mypasswd"
>
> ! If only one argument is used, then the unnamed connection is used.
>
> sql
>
> ***************
> *** 29,35 ****
>
> Example usage
>
> ! test=# select * from dblink('dbname=template1','select proname, prosrc from pg_proc')
> as t1(proname name, prosrc text) where proname like 'bytea%';
> proname | prosrc
> ------------+------------
> --- 31,37 ----
>
> Example usage
>
> ! select * from dblink('dbname=template1','select proname, prosrc from pg_proc')
> as t1(proname name, prosrc text) where proname like 'bytea%';
> proname | prosrc
> ------------+------------
> ***************
> *** 47,59 ****
> byteaout | byteaout
> (12 rows)
>
> ! test=# select dblink_connect('dbname=template1');
> dblink_connect
> ----------------
> OK
> (1 row)
>
> ! test=# select * from dblink('select proname, prosrc from pg_proc')
> as t1(proname name, prosrc text) where proname like 'bytea%';
> proname | prosrc
> ------------+------------
> --- 49,61 ----
> byteaout | byteaout
> (12 rows)
>
> ! select dblink_connect('dbname=template1');
> dblink_connect
> ----------------
> OK
> (1 row)
>
> ! select * from dblink('select proname, prosrc from pg_proc')
> as t1(proname name, prosrc text) where proname like 'bytea%';
> proname | prosrc
> ------------+------------
> ***************
> *** 70,75 ****
> --- 72,104 ----
> byteain | byteain
> byteaout | byteaout
> (12 rows)
> +
> + select dblink_connect('myconn','dbname=regression');
> + dblink_connect
> + ----------------
> + OK
> + (1 row)
> +
> + select * from dblink('myconn','select proname, prosrc from pg_proc')
> + as t1(proname name, prosrc text) where proname like 'bytea%';
> + proname | prosrc
> + ------------+------------
> + bytearecv | bytearecv
> + byteasend | byteasend
> + byteale | byteale
> + byteagt | byteagt
> + byteage | byteage
> + byteane | byteane
> + byteacmp | byteacmp
> + bytealike | bytealike
> + byteanlike | byteanlike
> + byteacat | byteacat
> + byteaeq | byteaeq
> + bytealt | bytealt
> + byteain | byteain
> + byteaout | byteaout
> + (14 rows)
> +
>
> ==================================================================
> A more convenient way to use dblink may be to create a view:
> Index: contrib/dblink/expected/dblink.out
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/expected/dblink.out,v
> retrieving revision 1.8
> diff -c -r1.8 dblink.out
> *** contrib/dblink/expected/dblink.out 14 May 2003 03:25:55 -0000 1.8
> --- contrib/dblink/expected/dblink.out 15 Jun 2003 04:11:11 -0000
> ***************
> *** 106,116 ****
> 9 | j | {a9,b9,c9}
> (2 rows)
>
> ! -- should generate "no connection available" error
> SELECT *
> FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
> WHERE t.a > 7;
> ! ERROR: dblink: no connection available
> -- create a persistent connection
> SELECT dblink_connect('dbname=regression');
> dblink_connect
> --- 106,116 ----
> 9 | j | {a9,b9,c9}
> (2 rows)
>
> ! -- should generate "connection not available" error
> SELECT *
> FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
> WHERE t.a > 7;
> ! ERROR: dblink_record: connection not available
> -- create a persistent connection
> SELECT dblink_connect('dbname=regression');
> dblink_connect
> ***************
> *** 172,181 ****
> OK
> (1 row)
>
> ! -- should generate "cursor rmt_foo_cursor does not exist" error
> SELECT *
> FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
> ! ERROR: dblink_fetch: cursor rmt_foo_cursor does not exist
> -- close the persistent connection
> SELECT dblink_disconnect();
> dblink_disconnect
> --- 172,181 ----
> OK
> (1 row)
>
> ! -- should generate "cursor not found: rmt_foo_cursor" error
> SELECT *
> FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
> ! ERROR: dblink_fetch: cursor not found: rmt_foo_cursor
> -- close the persistent connection
> SELECT dblink_disconnect();
> dblink_disconnect
> ***************
> *** 183,193 ****
> OK
> (1 row)
>
> ! -- should generate "no connection available" error
> SELECT *
> FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
> WHERE t.a > 7;
> ! ERROR: dblink: no connection available
> -- put more data into our slave table, first using arbitrary connection syntax
> -- but truncate the actual return value so we can use diff to check for success
> SELECT substr(dblink_exec('dbname=regression','INSERT INTO foo VALUES(10,''k'',''{"a10","b10","c10"}'')'),1,6);
> --- 183,194 ----
> OK
> (1 row)
>
> ! -- should generate "no connection to the server" error
> SELECT *
> FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
> WHERE t.a > 7;
> ! ERROR: dblink: sql error: no connection to the server
> !
> -- put more data into our slave table, first using arbitrary connection syntax
> -- but truncate the actual return value so we can use diff to check for success
> SELECT substr(dblink_exec('dbname=regression','INSERT INTO foo VALUES(10,''k'',''{"a10","b10","c10"}'')'),1,6);
> ***************
> *** 268,270 ****
> --- 269,466 ----
> OK
> (1 row)
>
> + --
> + -- tests for the new named persistent connection syntax
> + --
> + -- should generate "missing "=" after "myconn" in connection info string" error
> + SELECT *
> + FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
> + WHERE t.a > 7;
> + ERROR: dblink: connection error: missing "=" after "myconn" in connection info string
> +
> + -- create a named persistent connection
> + SELECT dblink_connect('myconn','dbname=regression');
> + dblink_connect
> + ----------------
> + OK
> + (1 row)
> +
> + -- use the named persistent connection
> + SELECT *
> + FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
> + WHERE t.a > 7;
> + a | b | c
> + ----+---+---------------
> + 8 | i | {a8,b8,c8}
> + 9 | j | {a9,b9,c9}
> + 10 | k | {a10,b10,c10}
> + (3 rows)
> +
> + -- create a second named persistent connection
> + -- should error with "cannot save named connection"
> + SELECT dblink_connect('myconn','dbname=regression');
> + NOTICE: cannot use a connection name more than once
> + ERROR: dblink_connect: cannot save named connection
> + -- create a second named persistent connection with a new name
> + SELECT dblink_connect('myconn2','dbname=regression');
> + dblink_connect
> + ----------------
> + OK
> + (1 row)
> +
> + -- use the second named persistent connection
> + SELECT *
> + FROM dblink('myconn2','SELECT * FROM foo') AS t(a int, b text, c text[])
> + WHERE t.a > 7;
> + a | b | c
> + ----+---+---------------
> + 8 | i | {a8,b8,c8}
> + 9 | j | {a9,b9,c9}
> + 10 | k | {a10,b10,c10}
> + (3 rows)
> +
> + -- close the second named persistent connection
> + SELECT dblink_disconnect('myconn2');
> + dblink_disconnect
> + -------------------
> + OK
> + (1 row)
> +
> + -- open a cursor
> + SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foo');
> + dblink_open
> + -------------
> + OK
> + (1 row)
> +
> + -- fetch some data
> + SELECT *
> + FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
> + a | b | c
> + ---+---+------------
> + 0 | a | {a0,b0,c0}
> + 1 | b | {a1,b1,c1}
> + 2 | c | {a2,b2,c2}
> + 3 | d | {a3,b3,c3}
> + (4 rows)
> +
> + SELECT *
> + FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
> + a | b | c
> + ---+---+------------
> + 4 | e | {a4,b4,c4}
> + 5 | f | {a5,b5,c5}
> + 6 | g | {a6,b6,c6}
> + 7 | h | {a7,b7,c7}
> + (4 rows)
> +
> + -- this one only finds three rows left
> + SELECT *
> + FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
> + a | b | c
> + ----+---+---------------
> + 8 | i | {a8,b8,c8}
> + 9 | j | {a9,b9,c9}
> + 10 | k | {a10,b10,c10}
> + (3 rows)
> +
> + -- close the cursor
> + SELECT dblink_close('myconn','rmt_foo_cursor');
> + dblink_close
> + --------------
> + OK
> + (1 row)
> +
> + -- should generate "cursor not found: rmt_foo_cursor" error
> + SELECT *
> + FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
> + ERROR: dblink_fetch: cursor not found: rmt_foo_cursor
> + -- close the named persistent connection
> + SELECT dblink_disconnect('myconn');
> + dblink_disconnect
> + -------------------
> + OK
> + (1 row)
> +
> + -- should generate "missing "=" after "myconn" in connection info string" error
> + SELECT *
> + FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
> + WHERE t.a > 7;
> + ERROR: dblink: connection error: missing "=" after "myconn" in connection info string
> +
> + -- create a named persistent connection
> + SELECT dblink_connect('myconn','dbname=regression');
> + dblink_connect
> + ----------------
> + OK
> + (1 row)
> +
> + -- put more data into our slave table, using named persistent connection syntax
> + -- but truncate the actual return value so we can use diff to check for success
> + SELECT substr(dblink_exec('myconn','INSERT INTO foo VALUES(11,''l'',''{"a11","b11","c11"}'')'),1,6);
> + substr
> + --------
> + INSERT
> + (1 row)
> +
> + -- let's see it
> + SELECT *
> + FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]);
> + a | b | c
> + ----+---+---------------
> + 0 | a | {a0,b0,c0}
> + 1 | b | {a1,b1,c1}
> + 2 | c | {a2,b2,c2}
> + 3 | d | {a3,b3,c3}
> + 4 | e | {a4,b4,c4}
> + 5 | f | {a5,b5,c5}
> + 6 | g | {a6,b6,c6}
> + 7 | h | {a7,b7,c7}
> + 8 | i | {a8,b8,c8}
> + 9 | j | {a9,b9,c9}
> + 10 | k | {a10,b10,c10}
> + 11 | l | {a11,b11,c11}
> + (12 rows)
> +
> + -- change some data
> + SELECT dblink_exec('myconn','UPDATE foo SET f3[2] = ''b99'' WHERE f1 = 11');
> + dblink_exec
> + -------------
> + UPDATE 1
> + (1 row)
> +
> + -- let's see it
> + SELECT *
> + FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
> + WHERE a = 11;
> + a | b | c
> + ----+---+---------------
> + 11 | l | {a11,b99,c11}
> + (1 row)
> +
> + -- delete some data
> + SELECT dblink_exec('myconn','DELETE FROM foo WHERE f1 = 11');
> + dblink_exec
> + -------------
> + DELETE 1
> + (1 row)
> +
> + -- let's see it
> + SELECT *
> + FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
> + WHERE a = 11;
> + a | b | c
> + ---+---+---
> + (0 rows)
> +
> + -- close the named persistent connection
> + SELECT dblink_disconnect('myconn');
> + dblink_disconnect
> + -------------------
> + OK
> + (1 row)
> +
> + -- close the named persistent connection again
> + -- should get "connection named "myconn" not found" error
> + SELECT dblink_disconnect('myconn');
> + ERROR: dblink_disconnect: connection named "myconn" not found
> Index: contrib/dblink/sql/dblink.sql
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/sql/dblink.sql,v
> retrieving revision 1.8
> diff -c -r1.8 dblink.sql
> *** contrib/dblink/sql/dblink.sql 14 May 2003 03:25:55 -0000 1.8
> --- contrib/dblink/sql/dblink.sql 15 Jun 2003 04:10:59 -0000
> ***************
> *** 68,74 ****
> FROM dblink('dbname=regression','SELECT * FROM foo') AS t(a int, b text, c text[])
> WHERE t.a > 7;
>
> ! -- should generate "no connection available" error
> SELECT *
> FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
> WHERE t.a > 7;
> --- 68,74 ----
> FROM dblink('dbname=regression','SELECT * FROM foo') AS t(a int, b text, c text[])
> WHERE t.a > 7;
>
> ! -- should generate "connection not available" error
> SELECT *
> FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
> WHERE t.a > 7;
> ***************
> *** 98,111 ****
> -- close the cursor
> SELECT dblink_close('rmt_foo_cursor');
>
> ! -- should generate "cursor rmt_foo_cursor does not exist" error
> SELECT *
> FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
>
> -- close the persistent connection
> SELECT dblink_disconnect();
>
> ! -- should generate "no connection available" error
> SELECT *
> FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
> WHERE t.a > 7;
> --- 98,111 ----
> -- close the cursor
> SELECT dblink_close('rmt_foo_cursor');
>
> ! -- should generate "cursor not found: rmt_foo_cursor" error
> SELECT *
> FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
>
> -- close the persistent connection
> SELECT dblink_disconnect();
>
> ! -- should generate "no connection to the server" error
> SELECT *
> FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
> WHERE t.a > 7;
> ***************
> *** 143,145 ****
> --- 143,240 ----
>
> -- close the persistent connection
> SELECT dblink_disconnect();
> +
> + --
> + -- tests for the new named persistent connection syntax
> + --
> +
> + -- should generate "missing "=" after "myconn" in connection info string" error
> + SELECT *
> + FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
> + WHERE t.a > 7;
> +
> + -- create a named persistent connection
> + SELECT dblink_connect('myconn','dbname=regression');
> +
> + -- use the named persistent connection
> + SELECT *
> + FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
> + WHERE t.a > 7;
> +
> + -- create a second named persistent connection
> + -- should error with "cannot save named connection"
> + SELECT dblink_connect('myconn','dbname=regression');
> +
> + -- create a second named persistent connection with a new name
> + SELECT dblink_connect('myconn2','dbname=regression');
> +
> + -- use the second named persistent connection
> + SELECT *
> + FROM dblink('myconn2','SELECT * FROM foo') AS t(a int, b text, c text[])
> + WHERE t.a > 7;
> +
> + -- close the second named persistent connection
> + SELECT dblink_disconnect('myconn2');
> +
> + -- open a cursor
> + SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foo');
> +
> + -- fetch some data
> + SELECT *
> + FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
> +
> + SELECT *
> + FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
> +
> + -- this one only finds three rows left
> + SELECT *
> + FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
> +
> + -- close the cursor
> + SELECT dblink_close('myconn','rmt_foo_cursor');
> +
> + -- should generate "cursor not found: rmt_foo_cursor" error
> + SELECT *
> + FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
> +
> + -- close the named persistent connection
> + SELECT dblink_disconnect('myconn');
> +
> + -- should generate "missing "=" after "myconn" in connection info string" error
> + SELECT *
> + FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
> + WHERE t.a > 7;
> +
> + -- create a named persistent connection
> + SELECT dblink_connect('myconn','dbname=regression');
> +
> + -- put more data into our slave table, using named persistent connection syntax
> + -- but truncate the actual return value so we can use diff to check for success
> + SELECT substr(dblink_exec('myconn','INSERT INTO foo VALUES(11,''l'',''{"a11","b11","c11"}'')'),1,6);
> +
> + -- let's see it
> + SELECT *
> + FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]);
> +
> + -- change some data
> + SELECT dblink_exec('myconn','UPDATE foo SET f3[2] = ''b99'' WHERE f1 = 11');
> +
> + -- let's see it
> + SELECT *
> + FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
> + WHERE a = 11;
> +
> + -- delete some data
> + SELECT dblink_exec('myconn','DELETE FROM foo WHERE f1 = 11');
> +
> + -- let's see it
> + SELECT *
> + FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
> + WHERE a = 11;
> +
> + -- close the named persistent connection
> + SELECT dblink_disconnect('myconn');
> +
> + -- close the named persistent connection again
> + -- should get "connection named "myconn" not found" error
> + SELECT dblink_disconnect('myconn');
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073