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 * ! * 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 + * And contributors: + * Darko Prenosil + * Shridhar Daithankar * ! * 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 * ! * 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 + * And contributors: + * Darko Prenosil + * Shridhar Daithankar * ! * 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 * ! * 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 + * And contributors: + * Darko Prenosil + * Shridhar Daithankar * ! * 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');