Re: [GENERAL] dblink: rollback transaction - Mailing list pgsql-patches
| From | Joe Conway |
|---|---|
| Subject | Re: [GENERAL] dblink: rollback transaction |
| Date | |
| Msg-id | 40481A2C.1010806@joeconway.com Whole thread Raw |
| In response to | Re: [GENERAL] dblink: rollback transaction (Joe Conway <mail@joeconway.com>) |
| List | pgsql-patches |
Joe Conway wrote:
> Tom Lane wrote:
>> Joe Conway <mail@joeconway.com> writes:
>>> I like the idea in general, but maybe instead there should be a new
>>> overloaded version of the existing function names that accepts an
>>> additional bool argument. Without the argument, behavior would be as
>>> it is now; with it, you could specify the old or new behavior.
>>
>> Um, maybe I'm confused about the context, but aren't we talking about C
>> function names here? No overloading is possible in C ...
>
> I was thinking in terms of overloaded SQL function names. For example,
> in addition to dblink_exec(text) and dblink_exec(text,text) we create
> dblink_exec(text,bool) and dblink_exec(text,text,bool).
>
> Currently both SQL versions of dblink_exec are implemented by a single C
> level function. But yes, we'd need another C level function to support
> the new SQL functions because there would be no way to distinguish the 2
> two-argument versions otherwise. (Actually, now I'm wondering if we
> could use a single C function for all four SQL versions -- between
> PG_NARGS() and get_fn_expr_argtype() we should be able to figure out how
> we were called, shouldn't we?)
The attached implements the new overloaded SQL functions as discussed
above (i.e. start with existing argument combinations, add a new bool
argument to each). I ended up with a single C function (by making use of
number and type of the arguments) for each overloaded SQL function name.
I'll commit in a day or two if there are no objections.
Thanks,
Joe
Index: contrib/dblink/README.dblink
===================================================================
RCS file: /cvsroot/pgsql-server/contrib/dblink/README.dblink,v
retrieving revision 1.9
diff -c -r1.9 README.dblink
*** contrib/dblink/README.dblink 4 Aug 2003 23:59:37 -0000 1.9
--- contrib/dblink/README.dblink 5 Mar 2004 05:55:45 -0000
***************
*** 30,42 ****
*
*/
- 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
--- 30,40 ----
*
*/
Release Notes:
+ Version 0.7 (as of 25 Feb, 2004)
+ - Added new version of dblink, dblink_exec, dblink_open, dblink_close,
+ and, dblink_fetch -- allows ERROR on remote side of connection to
+ throw NOTICE locally instead of ERROR
Version 0.6
- functions deprecated in 0.5 have been removed
- added ability to create "named" persistent connections
***************
*** 85,91 ****
You can use dblink.sql to create the functions in your database of choice, e.g.
! psql -U postgres template1 < dblink.sql
installs following functions into database template1:
--- 83,89 ----
You can use dblink.sql to create the functions in your database of choice, e.g.
! psql template1 < dblink.sql
installs following functions into database template1:
***************
*** 104,143 ****
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()
--- 102,141 ----
cursor
------------
! dblink_open(text,text [, bool fail_on_error]) 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 [, bool fail_on_error]) 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 [, bool fail_on_error]) RETURNS setof record
- fetches data from an already opened cursor on the unnamed connection
! dblink_fetch(text, text, int [, bool fail_on_error]) RETURNS setof record
- fetches data from an already opened cursor on a named connection
! dblink_close(text [, bool fail_on_error]) RETURNS text
- closes a cursor on the unnamed connection
! dblink_close(text,text [, bool fail_on_error]) RETURNS text
- closes a cursor on a named connection
query
------------
! dblink(text,text [, bool fail_on_error]) 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 [, bool fail_on_error]) 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 [, bool fail_on_error]) 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 [, bool fail_on_error]) RETURNS text
- executes an INSERT/UPDATE/DELETE query remotely, using connection
already opened with dblink_connect()
***************
*** 169,175 ****
doc/query
doc/execute
doc/misc
- doc/deprecated
==================================================================
-- Joe Conway
--- 167,172 ----
Index: contrib/dblink/dblink.c
===================================================================
RCS file: /cvsroot/pgsql-server/contrib/dblink/dblink.c,v
retrieving revision 1.30
diff -c -r1.30 dblink.c
*** contrib/dblink/dblink.c 24 Feb 2004 06:07:18 -0000 1.30
--- contrib/dblink/dblink.c 5 Mar 2004 05:55:46 -0000
***************
*** 134,139 ****
--- 134,149 ----
errmsg("%s", p2), \
errdetail("%s", msg))); \
} while (0)
+ #define DBLINK_RES_ERROR_AS_NOTICE(p2) \
+ do { \
+ msg = pstrdup(PQerrorMessage(conn)); \
+ if (res) \
+ PQclear(res); \
+ ereport(NOTICE, \
+ (errcode(ERRCODE_SYNTAX_ERROR), \
+ errmsg("%s", p2), \
+ errdetail("%s", msg))); \
+ } while (0)
#define DBLINK_CONN_NOT_AVAIL \
do { \
if(conname) \
***************
*** 152,158 ****
if(rcon) \
{ \
conn = rcon->con; \
- freeconn = false; \
} \
else \
{ \
--- 162,167 ----
***************
*** 167,172 ****
--- 176,182 ----
errmsg("could not establish connection"), \
errdetail("%s", msg))); \
} \
+ freeconn = true; \
} \
} while (0)
***************
*** 276,293 ****
--- 286,327 ----
char *conname = NULL;
StringInfo str = makeStringInfo();
remoteConn *rcon = NULL;
+ bool fail = true; /* default to backward compatible behavior */
if (PG_NARGS() == 2)
{
+ /* text,text */
curname = GET_STR(PG_GETARG_TEXT_P(0));
sql = GET_STR(PG_GETARG_TEXT_P(1));
conn = persistent_conn;
}
else if (PG_NARGS() == 3)
{
+ /* might be text,text,text or text,text,bool */
+ if (get_fn_expr_argtype(fcinfo->flinfo, 2) == BOOLOID)
+ {
+ curname = GET_STR(PG_GETARG_TEXT_P(0));
+ sql = GET_STR(PG_GETARG_TEXT_P(1));
+ fail = PG_GETARG_BOOL(2);
+ conn = persistent_conn;
+ }
+ else
+ {
+ 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;
+ }
+ else if (PG_NARGS() == 4)
+ {
+ /* text,text,text,bool */
conname = GET_STR(PG_GETARG_TEXT_P(0));
curname = GET_STR(PG_GETARG_TEXT_P(1));
sql = GET_STR(PG_GETARG_TEXT_P(2));
+ fail = PG_GETARG_BOOL(3);
rcon = getConnectionByName(conname);
if (rcon)
conn = rcon->con;
***************
*** 304,316 ****
appendStringInfo(str, "DECLARE %s CURSOR FOR %s", curname, sql);
res = PQexec(conn, str->data);
! if (!res ||
! (PQresultStatus(res) != PGRES_COMMAND_OK &&
! PQresultStatus(res) != PGRES_TUPLES_OK))
! DBLINK_RES_ERROR("sql error");
PQclear(res);
-
PG_RETURN_TEXT_P(GET_TEXT("OK"));
}
--- 338,356 ----
appendStringInfo(str, "DECLARE %s CURSOR FOR %s", curname, sql);
res = PQexec(conn, str->data);
! if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
! {
! if (fail)
! DBLINK_RES_ERROR("sql error");
! else
! {
! DBLINK_RES_ERROR_AS_NOTICE("sql error");
! PQclear(res);
! PG_RETURN_TEXT_P(GET_TEXT("ERROR"));
! }
! }
PQclear(res);
PG_RETURN_TEXT_P(GET_TEXT("OK"));
}
***************
*** 328,343 ****
--- 368,405 ----
StringInfo str = makeStringInfo();
char *msg;
remoteConn *rcon = NULL;
+ bool fail = true; /* default to backward compatible behavior */
if (PG_NARGS() == 1)
{
+ /* text */
curname = GET_STR(PG_GETARG_TEXT_P(0));
conn = persistent_conn;
}
else if (PG_NARGS() == 2)
{
+ /* might be text,text or text,bool */
+ if (get_fn_expr_argtype(fcinfo->flinfo, 1) == BOOLOID)
+ {
+ curname = GET_STR(PG_GETARG_TEXT_P(0));
+ fail = PG_GETARG_BOOL(1);
+ conn = persistent_conn;
+ }
+ else
+ {
+ 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 (PG_NARGS() == 3)
+ {
+ /* text,text,bool */
conname = GET_STR(PG_GETARG_TEXT_P(0));
curname = GET_STR(PG_GETARG_TEXT_P(1));
+ fail = PG_GETARG_BOOL(2);
rcon = getConnectionByName(conname);
if (rcon)
conn = rcon->con;
***************
*** 351,357 ****
/* close the cursor */
res = PQexec(conn, str->data);
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
! DBLINK_RES_ERROR("sql error");
PQclear(res);
--- 413,428 ----
/* close the cursor */
res = PQexec(conn, str->data);
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
! {
! if (fail)
! DBLINK_RES_ERROR("sql error");
! else
! {
! DBLINK_RES_ERROR_AS_NOTICE("sql error");
! PQclear(res);
! PG_RETURN_TEXT_P(GET_TEXT("ERROR"));
! }
! }
PQclear(res);
***************
*** 395,413 ****
char *curname = NULL;
int howmany = 0;
ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
! 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;
--- 466,509 ----
char *curname = NULL;
int howmany = 0;
ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ bool fail = true; /* default to backward compatible */
! if (PG_NARGS() == 4)
{
+ /* text,text,int,bool */
conname = GET_STR(PG_GETARG_TEXT_P(0));
curname = GET_STR(PG_GETARG_TEXT_P(1));
howmany = PG_GETARG_INT32(2);
+ fail = PG_GETARG_BOOL(3);
rcon = getConnectionByName(conname);
if (rcon)
conn = rcon->con;
}
+ else if (PG_NARGS() == 3)
+ {
+ /* text,text,int or text,int,bool */
+ if (get_fn_expr_argtype(fcinfo->flinfo, 2) == BOOLOID)
+ {
+ curname = GET_STR(PG_GETARG_TEXT_P(0));
+ howmany = PG_GETARG_INT32(1);
+ fail = PG_GETARG_BOOL(2);
+ conn = persistent_conn;
+ }
+ else
+ {
+ 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)
{
+ /* text,int */
curname = GET_STR(PG_GETARG_TEXT_P(0));
howmany = PG_GETARG_INT32(1);
conn = persistent_conn;
***************
*** 431,437 ****
if (!res ||
(PQresultStatus(res) != PGRES_COMMAND_OK &&
PQresultStatus(res) != PGRES_TUPLES_OK))
! DBLINK_RES_ERROR("sql error");
else if (PQresultStatus(res) == PGRES_COMMAND_OK)
{
/* cursor does not exist - closed already or bad name */
--- 527,543 ----
if (!res ||
(PQresultStatus(res) != PGRES_COMMAND_OK &&
PQresultStatus(res) != PGRES_TUPLES_OK))
! {
! if (fail)
! DBLINK_RES_ERROR("sql error");
! else
! {
! if (res)
! PQclear(res);
! DBLINK_RES_ERROR_AS_NOTICE("sql error");
! SRF_RETURN_DONE(funcctx);
! }
! }
else if (PQresultStatus(res) == PGRES_COMMAND_OK)
{
/* cursor does not exist - closed already or bad name */
***************
*** 448,454 ****
--- 554,564 ----
/* fast track when no results */
if (funcctx->max_calls < 1)
+ {
+ if (res)
+ PQclear(res);
SRF_RETURN_DONE(funcctx);
+ }
/* check typtype to see if we have a predetermined return type */
functypeid = get_func_rettype(funcid);
***************
*** 546,552 ****
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())
--- 656,662 ----
bool is_sql_cmd = false;
char *sql_cmd_status = NULL;
MemoryContext oldcontext;
! bool freeconn = false;
/* stuff done only on the first call of the function */
if (SRF_IS_FIRSTCALL())
***************
*** 560,565 ****
--- 670,676 ----
char *conname = NULL;
remoteConn *rcon = NULL;
ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ bool fail = true; /* default to backward compatible */
/* create a function context for cross-call persistence */
funcctx = SRF_FIRSTCALL_INIT();
***************
*** 570,582 ****
*/
oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
! if (PG_NARGS() == 2)
{
DBLINK_GET_CONN;
sql = GET_STR(PG_GETARG_TEXT_P(1));
}
else if (PG_NARGS() == 1)
{
conn = persistent_conn;
sql = GET_STR(PG_GETARG_TEXT_P(0));
}
--- 681,711 ----
*/
oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
! if (PG_NARGS() == 3)
{
+ /* text,text,bool */
DBLINK_GET_CONN;
sql = GET_STR(PG_GETARG_TEXT_P(1));
+ fail = PG_GETARG_BOOL(2);
+ }
+ else if (PG_NARGS() == 2)
+ {
+ /* text,text or text,bool */
+ if (get_fn_expr_argtype(fcinfo->flinfo, 1) == BOOLOID)
+ {
+ conn = persistent_conn;
+ sql = GET_STR(PG_GETARG_TEXT_P(0));
+ fail = PG_GETARG_BOOL(1);
+ }
+ else
+ {
+ DBLINK_GET_CONN;
+ sql = GET_STR(PG_GETARG_TEXT_P(1));
+ }
}
else if (PG_NARGS() == 1)
{
+ /* text */
conn = persistent_conn;
sql = GET_STR(PG_GETARG_TEXT_P(0));
}
***************
*** 588,595 ****
DBLINK_CONN_NOT_AVAIL;
res = PQexec(conn, sql);
! if (!res || (PQresultStatus(res) != PGRES_COMMAND_OK && PQresultStatus(res) != PGRES_TUPLES_OK))
! DBLINK_RES_ERROR("sql error");
if (PQresultStatus(res) == PGRES_COMMAND_OK)
{
--- 717,738 ----
DBLINK_CONN_NOT_AVAIL;
res = PQexec(conn, sql);
! if (!res ||
! (PQresultStatus(res) != PGRES_COMMAND_OK &&
! PQresultStatus(res) != PGRES_TUPLES_OK))
! {
! if (fail)
! DBLINK_RES_ERROR("sql error");
! else
! {
! if (res)
! PQclear(res);
! if (freeconn)
! PQfinish(conn);
! DBLINK_RES_ERROR_AS_NOTICE("sql error");
! SRF_RETURN_DONE(funcctx);
! }
! }
if (PQresultStatus(res) == PGRES_COMMAND_OK)
{
***************
*** 614,625 ****
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)
SRF_RETURN_DONE(funcctx);
/* check typtype to see if we have a predetermined return type */
functypeid = get_func_rettype(funcid);
--- 757,772 ----
funcctx->user_fctx = res;
/* if needed, close the connection to the database and cleanup */
! if (freeconn)
PQfinish(conn);
/* fast track when no results */
if (funcctx->max_calls < 1)
+ {
+ if (res)
+ PQclear(res);
SRF_RETURN_DONE(funcctx);
+ }
/* check typtype to see if we have a predetermined return type */
functypeid = get_func_rettype(funcid);
***************
*** 727,741 ****
char *sql = NULL;
char *conname = NULL;
remoteConn *rcon = NULL;
! bool freeconn = true;
! if (PG_NARGS() == 2)
{
DBLINK_GET_CONN;
sql = GET_STR(PG_GETARG_TEXT_P(1));
}
else if (PG_NARGS() == 1)
{
conn = persistent_conn;
sql = GET_STR(PG_GETARG_TEXT_P(0));
}
--- 874,907 ----
char *sql = NULL;
char *conname = NULL;
remoteConn *rcon = NULL;
! bool freeconn = false;
! bool fail = true; /* default to backward compatible behavior */
! if (PG_NARGS() == 3)
{
+ /* must be text,text,bool */
DBLINK_GET_CONN;
sql = GET_STR(PG_GETARG_TEXT_P(1));
+ fail = PG_GETARG_BOOL(2);
+ }
+ else if (PG_NARGS() == 2)
+ {
+ /* might be text,text or text,bool */
+ if (get_fn_expr_argtype(fcinfo->flinfo, 1) == BOOLOID)
+ {
+ conn = persistent_conn;
+ sql = GET_STR(PG_GETARG_TEXT_P(0));
+ fail = PG_GETARG_BOOL(1);
+ }
+ else
+ {
+ DBLINK_GET_CONN;
+ sql = GET_STR(PG_GETARG_TEXT_P(1));
+ }
}
else if (PG_NARGS() == 1)
{
+ /* must be single text argument */
conn = persistent_conn;
sql = GET_STR(PG_GETARG_TEXT_P(0));
}
***************
*** 750,758 ****
if (!res ||
(PQresultStatus(res) != PGRES_COMMAND_OK &&
PQresultStatus(res) != PGRES_TUPLES_OK))
! DBLINK_RES_ERROR("sql error");
! if (PQresultStatus(res) == PGRES_COMMAND_OK)
{
/* need a tuple descriptor representing one TEXT column */
tupdesc = CreateTemplateTupleDesc(1, false);
--- 916,940 ----
if (!res ||
(PQresultStatus(res) != PGRES_COMMAND_OK &&
PQresultStatus(res) != PGRES_TUPLES_OK))
! {
! if (fail)
! DBLINK_RES_ERROR("sql error");
! else
! DBLINK_RES_ERROR_AS_NOTICE("sql error");
!
! /* 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("ERROR");
!
! }
! else if (PQresultStatus(res) == PGRES_COMMAND_OK)
{
/* need a tuple descriptor representing one TEXT column */
tupdesc = CreateTemplateTupleDesc(1, false);
***************
*** 773,779 ****
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);
--- 955,961 ----
PQclear(res);
/* if needed, close the connection to the database and cleanup */
! if (freeconn)
PQfinish(conn);
PG_RETURN_TEXT_P(sql_cmd_status);
Index: contrib/dblink/dblink.sql.in
===================================================================
RCS file: /cvsroot/pgsql-server/contrib/dblink/dblink.sql.in,v
retrieving revision 1.8
diff -c -r1.8 dblink.sql.in
*** contrib/dblink/dblink.sql.in 25 Jun 2003 01:10:15 -0000 1.8
--- contrib/dblink/dblink.sql.in 5 Mar 2004 05:55:46 -0000
***************
*** 1,94 ****
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'
! LANGUAGE 'C' WITH (isstrict);
CREATE OR REPLACE FUNCTION dblink (text)
RETURNS setof record
AS 'MODULE_PATHNAME','dblink_record'
! LANGUAGE 'C' WITH (isstrict);
CREATE OR REPLACE FUNCTION dblink_exec (text,text)
RETURNS text
AS 'MODULE_PATHNAME','dblink_exec'
! LANGUAGE 'C' WITH (isstrict);
CREATE OR REPLACE FUNCTION dblink_exec (text)
RETURNS text
AS 'MODULE_PATHNAME','dblink_exec'
! LANGUAGE 'c' WITH (isstrict);
CREATE TYPE dblink_pkey_results AS (position int4, colname text);
CREATE OR REPLACE FUNCTION dblink_get_pkey (text)
RETURNS setof dblink_pkey_results
AS 'MODULE_PATHNAME','dblink_get_pkey'
! LANGUAGE 'c' WITH (isstrict);
CREATE OR REPLACE FUNCTION dblink_build_sql_insert (text, int2vector, int4, _text, _text)
RETURNS text
AS 'MODULE_PATHNAME','dblink_build_sql_insert'
! LANGUAGE 'C' WITH (isstrict);
CREATE OR REPLACE FUNCTION dblink_build_sql_delete (text, int2vector, int4, _text)
RETURNS text
AS 'MODULE_PATHNAME','dblink_build_sql_delete'
! LANGUAGE 'C' WITH (isstrict);
CREATE OR REPLACE FUNCTION dblink_build_sql_update (text, int2vector, int4, _text, _text)
RETURNS text
AS 'MODULE_PATHNAME','dblink_build_sql_update'
! LANGUAGE 'C' WITH (isstrict);
CREATE OR REPLACE FUNCTION dblink_current_query ()
RETURNS text
--- 1,144 ----
CREATE OR REPLACE FUNCTION dblink_connect (text)
RETURNS text
AS 'MODULE_PATHNAME','dblink_connect'
! LANGUAGE 'C' STRICT;
CREATE OR REPLACE FUNCTION dblink_connect (text, text)
RETURNS text
AS 'MODULE_PATHNAME','dblink_connect'
! LANGUAGE 'C' STRICT;
CREATE OR REPLACE FUNCTION dblink_disconnect ()
RETURNS text
AS 'MODULE_PATHNAME','dblink_disconnect'
! LANGUAGE 'C' STRICT;
CREATE OR REPLACE FUNCTION dblink_disconnect (text)
RETURNS text
AS 'MODULE_PATHNAME','dblink_disconnect'
! LANGUAGE 'C' STRICT;
CREATE OR REPLACE FUNCTION dblink_open (text,text)
RETURNS text
AS 'MODULE_PATHNAME','dblink_open'
! LANGUAGE 'C' STRICT;
!
! CREATE OR REPLACE FUNCTION dblink_open (text,text,bool)
! RETURNS text
! AS 'MODULE_PATHNAME','dblink_open'
! LANGUAGE 'C' STRICT;
CREATE OR REPLACE FUNCTION dblink_open (text,text,text)
RETURNS text
AS 'MODULE_PATHNAME','dblink_open'
! LANGUAGE 'C' STRICT;
!
! CREATE OR REPLACE FUNCTION dblink_open (text,text,text,bool)
! RETURNS text
! AS 'MODULE_PATHNAME','dblink_open'
! LANGUAGE 'C' STRICT;
CREATE OR REPLACE FUNCTION dblink_fetch (text,int)
RETURNS setof record
AS 'MODULE_PATHNAME','dblink_fetch'
! LANGUAGE 'C' STRICT;
!
! CREATE OR REPLACE FUNCTION dblink_fetch (text,int,bool)
! RETURNS setof record
! AS 'MODULE_PATHNAME','dblink_fetch'
! LANGUAGE 'C' STRICT;
CREATE OR REPLACE FUNCTION dblink_fetch (text,text,int)
RETURNS setof record
AS 'MODULE_PATHNAME','dblink_fetch'
! LANGUAGE 'C' STRICT;
!
! CREATE OR REPLACE FUNCTION dblink_fetch (text,text,int,bool)
! RETURNS setof record
! AS 'MODULE_PATHNAME','dblink_fetch'
! LANGUAGE 'C' STRICT;
CREATE OR REPLACE FUNCTION dblink_close (text)
RETURNS text
AS 'MODULE_PATHNAME','dblink_close'
! LANGUAGE 'C' STRICT;
!
! CREATE OR REPLACE FUNCTION dblink_close (text,bool)
! RETURNS text
! AS 'MODULE_PATHNAME','dblink_close'
! LANGUAGE 'C' STRICT;
CREATE OR REPLACE FUNCTION dblink_close (text,text)
RETURNS text
AS 'MODULE_PATHNAME','dblink_close'
! LANGUAGE 'C' STRICT;
!
! CREATE OR REPLACE FUNCTION dblink_close (text,text,bool)
! RETURNS text
! AS 'MODULE_PATHNAME','dblink_close'
! LANGUAGE 'C' STRICT;
CREATE OR REPLACE FUNCTION dblink (text,text)
RETURNS setof record
AS 'MODULE_PATHNAME','dblink_record'
! LANGUAGE 'C' STRICT;
!
! CREATE OR REPLACE FUNCTION dblink (text,text,bool)
! RETURNS setof record
! AS 'MODULE_PATHNAME','dblink_record'
! LANGUAGE 'C' STRICT;
CREATE OR REPLACE FUNCTION dblink (text)
RETURNS setof record
AS 'MODULE_PATHNAME','dblink_record'
! LANGUAGE 'C' STRICT;
!
! CREATE OR REPLACE FUNCTION dblink (text,bool)
! RETURNS setof record
! AS 'MODULE_PATHNAME','dblink_record'
! LANGUAGE 'C' STRICT;
CREATE OR REPLACE FUNCTION dblink_exec (text,text)
RETURNS text
AS 'MODULE_PATHNAME','dblink_exec'
! LANGUAGE 'C' STRICT;
!
! CREATE OR REPLACE FUNCTION dblink_exec (text,text,bool)
! RETURNS text
! AS 'MODULE_PATHNAME','dblink_exec'
! LANGUAGE 'C' STRICT;
CREATE OR REPLACE FUNCTION dblink_exec (text)
RETURNS text
AS 'MODULE_PATHNAME','dblink_exec'
! LANGUAGE 'c' STRICT;
!
! CREATE OR REPLACE FUNCTION dblink_exec (text,bool)
! RETURNS text
! AS 'MODULE_PATHNAME','dblink_exec'
! LANGUAGE 'c' STRICT;
CREATE TYPE dblink_pkey_results AS (position int4, colname text);
CREATE OR REPLACE FUNCTION dblink_get_pkey (text)
RETURNS setof dblink_pkey_results
AS 'MODULE_PATHNAME','dblink_get_pkey'
! LANGUAGE 'c' STRICT;
CREATE OR REPLACE FUNCTION dblink_build_sql_insert (text, int2vector, int4, _text, _text)
RETURNS text
AS 'MODULE_PATHNAME','dblink_build_sql_insert'
! LANGUAGE 'C' STRICT;
CREATE OR REPLACE FUNCTION dblink_build_sql_delete (text, int2vector, int4, _text)
RETURNS text
AS 'MODULE_PATHNAME','dblink_build_sql_delete'
! LANGUAGE 'C' STRICT;
CREATE OR REPLACE FUNCTION dblink_build_sql_update (text, int2vector, int4, _text, _text)
RETURNS text
AS 'MODULE_PATHNAME','dblink_build_sql_update'
! LANGUAGE 'C' STRICT;
CREATE OR REPLACE FUNCTION dblink_current_query ()
RETURNS text
Index: contrib/dblink/doc/cursor
===================================================================
RCS file: /cvsroot/pgsql-server/contrib/dblink/doc/cursor,v
retrieving revision 1.2
diff -c -r1.2 cursor
*** contrib/dblink/doc/cursor 25 Jun 2003 01:10:15 -0000 1.2
--- contrib/dblink/doc/cursor 5 Mar 2004 05:55:46 -0000
***************
*** 5,12 ****
Synopsis
! dblink_open(text cursorname, text sql)
! dblink_open(text connname, text cursorname, text sql)
Inputs
--- 5,12 ----
Synopsis
! dblink_open(text cursorname, text sql [, bool fail_on_error])
! dblink_open(text connname, text cursorname, text sql [, bool fail_on_error])
Inputs
***************
*** 23,28 ****
--- 23,35 ----
sql statement that you wish to execute on the remote host
e.g. "select * from pg_class"
+ fail_on_error
+
+ If true (default when not present) then an ERROR thrown on the remote side
+ of the connection causes an ERROR to also be thrown locally. If false, the
+ remote ERROR is locally treated as a NOTICE, and the return value is set
+ to 'ERROR'.
+
Outputs
Returns status = "OK"
***************
*** 56,63 ****
Synopsis
! dblink_fetch(text cursorname, int32 howmany)
! dblink_fetch(text connname, text cursorname, int32 howmany)
Inputs
--- 63,70 ----
Synopsis
! dblink_fetch(text cursorname, int32 howmany [, bool fail_on_error])
! dblink_fetch(text connname, text cursorname, int32 howmany [, bool fail_on_error])
Inputs
***************
*** 75,80 ****
--- 82,93 ----
starting at the current cursor position, moving forward. Once the cursor
has positioned to the end, no more rows are produced.
+ fail_on_error
+
+ If true (default when not present) then an ERROR thrown on the remote side
+ of the connection causes an ERROR to also be thrown locally. If false, the
+ remote ERROR is locally treated as a NOTICE, and no rows are returned.
+
Outputs
Returns setof record
***************
*** 132,139 ****
Synopsis
! dblink_close(text cursorname)
! dblink_close(text connname, text cursorname)
Inputs
--- 145,152 ----
Synopsis
! dblink_close(text cursorname [, bool fail_on_error])
! dblink_close(text connname, text cursorname [, bool fail_on_error])
Inputs
***************
*** 144,149 ****
--- 157,169 ----
cursorname
a reference name for the cursor
+
+ fail_on_error
+
+ If true (default when not present) then an ERROR thrown on the remote side
+ of the connection causes an ERROR to also be thrown locally. If false, the
+ remote ERROR is locally treated as a NOTICE, and the return value is set
+ to 'ERROR'.
Outputs
Index: contrib/dblink/doc/execute
===================================================================
RCS file: /cvsroot/pgsql-server/contrib/dblink/doc/execute,v
retrieving revision 1.2
diff -c -r1.2 execute
*** contrib/dblink/doc/execute 25 Jun 2003 01:10:15 -0000 1.2
--- contrib/dblink/doc/execute 5 Mar 2004 05:55:46 -0000
***************
*** 5,18 ****
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,
--- 5,19 ----
Synopsis
! dblink_exec(text connstr, text sql [, bool fail_on_error])
! dblink_exec(text connname, text sql [, bool fail_on_error])
! dblink_exec(text sql [, bool fail_on_error])
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,
***************
*** 25,33 ****
sql statement that you wish to execute on the remote host, e.g.:
insert into foo values(0,'a','{"a0","b0","c0"}');
Outputs
! Returns status of the command
Notes
1) dblink_open starts an explicit transaction. If, after using dblink_open,
--- 26,41 ----
sql statement that you wish to execute on the remote host, e.g.:
insert into foo values(0,'a','{"a0","b0","c0"}');
+ fail_on_error
+
+ If true (default when not present) then an ERROR thrown on the remote side
+ of the connection causes an ERROR to also be thrown locally. If false, the
+ remote ERROR is locally treated as a NOTICE, and the return value is set
+ to 'ERROR'.
+
Outputs
! Returns status of the command, or 'ERROR' if the command failed.
Notes
1) dblink_open starts an explicit transaction. If, after using dblink_open,
***************
*** 59,62 ****
--- 67,79 ----
dblink_exec
------------------
INSERT 6432584 1
+ (1 row)
+
+ select dblink_exec('myconn','insert into pg_class values (''foo'')',false);
+ NOTICE: sql error
+ DETAIL: ERROR: null value in column "relnamespace" violates not-null constraint
+
+ dblink_exec
+ -------------
+ ERROR
(1 row)
Index: contrib/dblink/doc/query
===================================================================
RCS file: /cvsroot/pgsql-server/contrib/dblink/doc/query,v
retrieving revision 1.2
diff -c -r1.2 query
*** contrib/dblink/doc/query 25 Jun 2003 01:10:15 -0000 1.2
--- contrib/dblink/doc/query 5 Mar 2004 05:55:46 -0000
***************
*** 5,13 ****
Synopsis
! dblink(text connstr, text sql)
! dblink(text connname, text sql)
! dblink(text sql)
Inputs
--- 5,13 ----
Synopsis
! dblink(text connstr, text sql [, bool fail_on_error])
! dblink(text connname, text sql [, bool fail_on_error])
! dblink(text sql [, bool fail_on_error])
Inputs
***************
*** 24,29 ****
--- 24,35 ----
sql statement that you wish to execute on the remote host
e.g. "select * from pg_class"
+
+ fail_on_error
+
+ If true (default when not present) then an ERROR thrown on the remote side
+ of the connection causes an ERROR to also be thrown locally. If false, the
+ remote ERROR is locally treated as a NOTICE, and no rows are returned.
Outputs
Index: contrib/dblink/expected/dblink.out
===================================================================
RCS file: /cvsroot/pgsql-server/contrib/dblink/expected/dblink.out,v
retrieving revision 1.12
diff -c -r1.12 dblink.out
*** contrib/dblink/expected/dblink.out 28 Nov 2003 05:03:01 -0000 1.12
--- contrib/dblink/expected/dblink.out 5 Mar 2004 05:55:46 -0000
***************
*** 128,133 ****
--- 128,150 ----
9 | j | {a9,b9,c9}
(2 rows)
+ -- open a cursor with bad SQL and fail_on_error set to false
+ SELECT dblink_open('rmt_foo_cursor','SELECT * FROM foobar',false);
+ NOTICE: sql error
+ DETAIL: ERROR: relation "foobar" does not exist
+
+ dblink_open
+ -------------
+ ERROR
+ (1 row)
+
+ -- reset remote transaction state
+ SELECT dblink_exec('ABORT');
+ dblink_exec
+ -------------
+ ROLLBACK
+ (1 row)
+
-- open a cursor
SELECT dblink_open('rmt_foo_cursor','SELECT * FROM foo');
dblink_open
***************
*** 135,140 ****
--- 152,171 ----
OK
(1 row)
+ -- close the cursor
+ SELECT dblink_close('rmt_foo_cursor',false);
+ dblink_close
+ --------------
+ OK
+ (1 row)
+
+ -- open the cursor again
+ SELECT dblink_open('rmt_foo_cursor','SELECT * FROM foo');
+ dblink_open
+ -------------
+ OK
+ (1 row)
+
-- fetch some data
SELECT *
FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
***************
*** 165,175 ****
9 | j | {a9,b9,c9}
(2 rows)
! -- close the cursor
! SELECT dblink_close('rmt_foo_cursor');
dblink_close
--------------
! OK
(1 row)
-- should generate 'cursor "rmt_foo_cursor" not found' error
--- 196,233 ----
9 | j | {a9,b9,c9}
(2 rows)
! -- intentionally botch a fetch
! SELECT *
! FROM dblink_fetch('rmt_foobar_cursor',4,false) AS t(a int, b text, c text[]);
! NOTICE: sql error
! DETAIL: ERROR: cursor "rmt_foobar_cursor" does not exist
!
! a | b | c
! ---+---+---
! (0 rows)
!
! -- reset remote transaction state
! SELECT dblink_exec('ABORT');
! dblink_exec
! -------------
! ROLLBACK
! (1 row)
!
! -- close the wrong cursor
! SELECT dblink_close('rmt_foobar_cursor',false);
! NOTICE: sql error
! DETAIL: ERROR: cursor "rmt_foobar_cursor" does not exist
!
dblink_close
--------------
! ERROR
! (1 row)
!
! -- reset remote transaction state
! SELECT dblink_exec('ABORT');
! dblink_exec
! -------------
! ROLLBACK
(1 row)
-- should generate 'cursor "rmt_foo_cursor" not found' error
***************
*** 178,183 ****
--- 236,251 ----
ERROR: sql error
DETAIL: ERROR: cursor "rmt_foo_cursor" does not exist
+ -- this time, 'cursor "rmt_foo_cursor" not found' as a notice
+ SELECT *
+ FROM dblink_fetch('rmt_foo_cursor',4,false) AS t(a int, b text, c text[]);
+ NOTICE: sql error
+ DETAIL: ERROR: cursor "rmt_foo_cursor" does not exist
+
+ a | b | c
+ ---+---+---
+ (0 rows)
+
-- close the persistent connection
SELECT dblink_disconnect();
dblink_disconnect
***************
*** 232,237 ****
--- 300,322 ----
11 | l | {a11,b11,c11}
(12 rows)
+ -- bad remote select
+ SELECT *
+ FROM dblink('SELECT * FROM foobar',false) AS t(a int, b text, c text[]);
+ NOTICE: sql error
+ DETAIL: ERROR: relation "foobar" does not exist
+
+ a | b | c
+ ---+---+---
+ (0 rows)
+
+ -- reset remote transaction state
+ SELECT dblink_exec('ABORT');
+ dblink_exec
+ -------------
+ ROLLBACK
+ (1 row)
+
-- change some data
SELECT dblink_exec('UPDATE foo SET f3[2] = ''b99'' WHERE f1 = 11');
dblink_exec
***************
*** 248,253 ****
--- 333,355 ----
11 | l | {a11,b99,c11}
(1 row)
+ -- botch a change to some other data
+ SELECT dblink_exec('UPDATE foobar SET f3[2] = ''b99'' WHERE f1 = 11',false);
+ NOTICE: sql error
+ DETAIL: ERROR: relation "foobar" does not exist
+
+ dblink_exec
+ -------------
+ ERROR
+ (1 row)
+
+ -- reset remote transaction state
+ SELECT dblink_exec('ABORT');
+ dblink_exec
+ -------------
+ ROLLBACK
+ (1 row)
+
-- delete some data
SELECT dblink_exec('DELETE FROM foo WHERE f1 = 11');
dblink_exec
***************
*** 298,303 ****
--- 400,423 ----
10 | k | {a10,b10,c10}
(3 rows)
+ -- use the named persistent connection, but get it wrong
+ SELECT *
+ FROM dblink('myconn','SELECT * FROM foobar',false) AS t(a int, b text, c text[])
+ WHERE t.a > 7;
+ NOTICE: sql error
+ DETAIL: ERROR: relation "foobar" does not exist
+
+ a | b | c
+ ---+---+---
+ (0 rows)
+
+ -- reset remote transaction state
+ SELECT dblink_exec('myconn','ABORT');
+ dblink_exec
+ -------------
+ ROLLBACK
+ (1 row)
+
-- create a second named persistent connection
-- should error with "duplicate connection name"
SELECT dblink_connect('myconn','dbname=regression');
***************
*** 327,332 ****
--- 447,469 ----
OK
(1 row)
+ -- open a cursor incorrectly
+ SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foobar',false);
+ NOTICE: sql error
+ DETAIL: ERROR: relation "foobar" does not exist
+
+ dblink_open
+ -------------
+ ERROR
+ (1 row)
+
+ -- reset remote transaction state
+ SELECT dblink_exec('myconn','ABORT');
+ dblink_exec
+ -------------
+ ROLLBACK
+ (1 row)
+
-- open a cursor
SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foo');
dblink_open
***************
*** 365,375 ****
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 "rmt_foo_cursor" not found' error
--- 502,522 ----
10 | k | {a10,b10,c10}
(3 rows)
! -- fetch some data incorrectly
! SELECT *
! FROM dblink_fetch('myconn','rmt_foobar_cursor',4,false) AS t(a int, b text, c text[]);
! NOTICE: sql error
! DETAIL: ERROR: cursor "rmt_foobar_cursor" does not exist
!
! a | b | c
! ---+---+---
! (0 rows)
!
! -- reset remote transaction state
! SELECT dblink_exec('myconn','ABORT');
! dblink_exec
! -------------
! ROLLBACK
(1 row)
-- should generate 'cursor "rmt_foo_cursor" not found' error
Index: contrib/dblink/sql/dblink.sql
===================================================================
RCS file: /cvsroot/pgsql-server/contrib/dblink/sql/dblink.sql,v
retrieving revision 1.11
diff -c -r1.11 dblink.sql
*** contrib/dblink/sql/dblink.sql 28 Nov 2003 05:03:02 -0000 1.11
--- contrib/dblink/sql/dblink.sql 5 Mar 2004 05:55:46 -0000
***************
*** 81,89 ****
--- 81,101 ----
FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
WHERE t.a > 7;
+ -- open a cursor with bad SQL and fail_on_error set to false
+ SELECT dblink_open('rmt_foo_cursor','SELECT * FROM foobar',false);
+
+ -- reset remote transaction state
+ SELECT dblink_exec('ABORT');
+
-- open a cursor
SELECT dblink_open('rmt_foo_cursor','SELECT * FROM foo');
+ -- close the cursor
+ SELECT dblink_close('rmt_foo_cursor',false);
+
+ -- open the cursor again
+ SELECT dblink_open('rmt_foo_cursor','SELECT * FROM foo');
+
-- fetch some data
SELECT *
FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
***************
*** 95,107 ****
SELECT *
FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
! -- close the cursor
! SELECT dblink_close('rmt_foo_cursor');
-- should generate 'cursor "rmt_foo_cursor" not found' error
SELECT *
FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
-- close the persistent connection
SELECT dblink_disconnect();
--- 107,133 ----
SELECT *
FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
! -- intentionally botch a fetch
! SELECT *
! FROM dblink_fetch('rmt_foobar_cursor',4,false) AS t(a int, b text, c text[]);
!
! -- reset remote transaction state
! SELECT dblink_exec('ABORT');
!
! -- close the wrong cursor
! SELECT dblink_close('rmt_foobar_cursor',false);
!
! -- reset remote transaction state
! SELECT dblink_exec('ABORT');
-- should generate 'cursor "rmt_foo_cursor" not found' error
SELECT *
FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
+ -- this time, 'cursor "rmt_foo_cursor" not found' as a notice
+ SELECT *
+ FROM dblink_fetch('rmt_foo_cursor',4,false) AS t(a int, b text, c text[]);
+
-- close the persistent connection
SELECT dblink_disconnect();
***************
*** 125,130 ****
--- 151,163 ----
SELECT *
FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[]);
+ -- bad remote select
+ SELECT *
+ FROM dblink('SELECT * FROM foobar',false) AS t(a int, b text, c text[]);
+
+ -- reset remote transaction state
+ SELECT dblink_exec('ABORT');
+
-- change some data
SELECT dblink_exec('UPDATE foo SET f3[2] = ''b99'' WHERE f1 = 11');
***************
*** 133,138 ****
--- 166,177 ----
FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
WHERE a = 11;
+ -- botch a change to some other data
+ SELECT dblink_exec('UPDATE foobar SET f3[2] = ''b99'' WHERE f1 = 11',false);
+
+ -- reset remote transaction state
+ SELECT dblink_exec('ABORT');
+
-- delete some data
SELECT dblink_exec('DELETE FROM foo WHERE f1 = 11');
***************
*** 161,166 ****
--- 200,213 ----
FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
WHERE t.a > 7;
+ -- use the named persistent connection, but get it wrong
+ SELECT *
+ FROM dblink('myconn','SELECT * FROM foobar',false) AS t(a int, b text, c text[])
+ WHERE t.a > 7;
+
+ -- reset remote transaction state
+ SELECT dblink_exec('myconn','ABORT');
+
-- create a second named persistent connection
-- should error with "duplicate connection name"
SELECT dblink_connect('myconn','dbname=regression');
***************
*** 176,181 ****
--- 223,234 ----
-- close the second named persistent connection
SELECT dblink_disconnect('myconn2');
+ -- open a cursor incorrectly
+ SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foobar',false);
+
+ -- reset remote transaction state
+ SELECT dblink_exec('myconn','ABORT');
+
-- open a cursor
SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foo');
***************
*** 190,197 ****
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 "rmt_foo_cursor" not found' error
SELECT *
--- 243,254 ----
SELECT *
FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
! -- fetch some data incorrectly
! SELECT *
! FROM dblink_fetch('myconn','rmt_foobar_cursor',4,false) AS t(a int, b text, c text[]);
!
! -- reset remote transaction state
! SELECT dblink_exec('myconn','ABORT');
-- should generate 'cursor "rmt_foo_cursor" not found' error
SELECT *
pgsql-patches by date: