Thread: DBLink patch

DBLink patch

From
"Shridhar Daithankar"
Date:
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');


Re: DBLink patch

From
Bruce Momjian
Date:
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

Re: DBLink patch

From
Bruce Momjian
Date:
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