-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 02/22/2015 10:26 PM, Corey Huinker wrote:
> Changes in this patch: - added polymorphic versions of
> dblink_fetch() - upped dblink version # to 1.2 because of new
> functions - migration 1.1 -> 1.2 - DocBook changes for dblink(),
> dblink_get_result(), dblink_fetch()
The previous patch was missing dblink--1.1--1.2.sql and
dblink--1.2.sql. I have added them, so it should apply cleanly against
git master, but not done any actual review yet.
- --
Joe Conway
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.22 (GNU/Linux)
iQIcBAEBAgAGBQJVmYTEAAoJEDfy90M199hl4tcQAJVIDiJn/ogFlxPSOxIQ1XRF
hYllqLTCALIyfDWsq5oizVrs3uFF5TpqMrFxpfpLhKbeGgGpnaOhP5rISw3DD1NA
P73MDVbP0/+Q2pwAk174+teXxqFBK3gQi4wgtaq0bC4aTC+LlphYImDbb6ExfrRR
CFlEV4MoC3vFsOKRjGalcv/iaM7HIZSn8lilmynCFx96BDwTgrmZu5vSk17a5MsO
oOc1s+1eIiZ7JpUGcYHwCmunC2Aed8OtcLjCFu3BTKTJEq1xhkbvHPFZvLBI6CtD
CI74SIHdtTg56Rm8lsJFnkg8SM9QW8kEKP/eJedS/ft5d3dFdOwYfORh+2qwmsCo
JOvtriUEVs835HGTatuh47dscwgCt0d6SA0a7rp4UxmoQTmohyt5fN8LW2fJpHd8
bj7du7SUv6n3GwxZT+PBSALkvD011shNl0/AxehOoqXHmL86G+c3qc0vHEF5ulId
jeq/ECCX509Ef+DmjFoCtnotI/oXB74JmgOqy3RtvuuZ4hgsRzzT9kyKWmcpCHQ8
ZsmwmRY5xrjClzf7dsvh8LjyM1nOAoRNQhHLlj9I8lWKupChaWaY1PH0qzAdYm9p
a1kKTV4sfuNDqV57OUk/u33lvHVx1HDyvdCNRPHz+7cyw8Zka8jAGXvzq8vZp+bV
Grl29j/8fGaErff1CNZl
=Bh5j
-----END PGP SIGNATURE-----
diff --git a/contrib/dblink/Makefile b/contrib/dblink/Makefile
index b8d5157..f31b9f7 100644
*** a/contrib/dblink/Makefile
--- b/contrib/dblink/Makefile
*************** PG_CPPFLAGS = -I$(libpq_srcdir)
*** 6,12 ****
SHLIB_LINK = $(libpq)
EXTENSION = dblink
! DATA = dblink--1.1.sql dblink--1.0--1.1.sql dblink--unpackaged--1.0.sql
PGFILEDESC = "dblink - connect to other PostgreSQL databases"
REGRESS = paths dblink
--- 6,13 ----
SHLIB_LINK = $(libpq)
EXTENSION = dblink
! DATA = dblink--1.1.sql dblink--1.0--1.1.sql dblink--unpackaged--1.0.sql \
! dblink--1.2.sql dblink--1.1--1.2.sql
PGFILEDESC = "dblink - connect to other PostgreSQL databases"
REGRESS = paths dblink
diff --git a/contrib/dblink/dblink--1.1--1.2.sql b/contrib/dblink/dblink--1.1--1.2.sql
index ...128611d .
*** a/contrib/dblink/dblink--1.1--1.2.sql
--- b/contrib/dblink/dblink--1.1--1.2.sql
***************
*** 0 ****
--- 1,46 ----
+ /* contrib/dblink/dblink--1.1--1.2.sql */
+
+ -- complain if script is sourced in psql, rather than via ALTER EXTENSION
+ \echo Use "ALTER EXTENSION dblink UPDATE TO '1.2'" to load this file. \quit
+
+ CREATE FUNCTION dblink (text, text, anyelement)
+ RETURNS setof anyelement
+ AS 'MODULE_PATHNAME','dblink_record'
+ LANGUAGE C;
+
+ CREATE FUNCTION dblink (text, text, boolean, anyelement)
+ RETURNS setof anyelement
+ AS 'MODULE_PATHNAME','dblink_record'
+ LANGUAGE C;
+
+ CREATE FUNCTION dblink (text, anyelement)
+ RETURNS setof anyelement
+ AS 'MODULE_PATHNAME','dblink_record'
+ LANGUAGE C;
+
+ CREATE FUNCTION dblink (text, boolean, anyelement)
+ RETURNS setof anyelement
+ AS 'MODULE_PATHNAME','dblink_record'
+ LANGUAGE C;
+
+ CREATE FUNCTION dblink_get_result(text, anyelement)
+ RETURNS SETOF anyelement
+ AS 'MODULE_PATHNAME', 'dblink_get_result'
+ LANGUAGE C;
+
+ CREATE FUNCTION dblink_get_result(text, bool, anyelement)
+ RETURNS SETOF anyelement
+ AS 'MODULE_PATHNAME', 'dblink_get_result'
+ LANGUAGE C;
+
+ CREATE FUNCTION dblink_fetch (text, int, anyelement)
+ RETURNS setof anyelement
+ AS 'MODULE_PATHNAME','dblink_fetch'
+ LANGUAGE C;
+
+ CREATE FUNCTION dblink_fetch (text, int, boolean, anyelement)
+ RETURNS setof anyelement
+ AS 'MODULE_PATHNAME','dblink_fetch'
+ LANGUAGE C;
+
+
diff --git a/contrib/dblink/dblink--1.2.sql b/contrib/dblink/dblink--1.2.sql
index ...1d84df2 .
*** a/contrib/dblink/dblink--1.2.sql
--- b/contrib/dblink/dblink--1.2.sql
***************
*** 0 ****
--- 1,275 ----
+ /* contrib/dblink/dblink--1.2.sql */
+
+ -- complain if script is sourced in psql, rather than via CREATE EXTENSION
+ \echo Use "CREATE EXTENSION dblink" to load this file. \quit
+
+ -- dblink_connect now restricts non-superusers to password
+ -- authenticated connections
+ CREATE FUNCTION dblink_connect (text)
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_connect'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION dblink_connect (text, text)
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_connect'
+ LANGUAGE C STRICT;
+
+ -- dblink_connect_u allows non-superusers to use
+ -- non-password authenticated connections, but initially
+ -- privileges are revoked from public
+ CREATE FUNCTION dblink_connect_u (text)
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_connect'
+ LANGUAGE C STRICT SECURITY DEFINER;
+
+ CREATE FUNCTION dblink_connect_u (text, text)
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_connect'
+ LANGUAGE C STRICT SECURITY DEFINER;
+
+ REVOKE ALL ON FUNCTION dblink_connect_u (text) FROM public;
+ REVOKE ALL ON FUNCTION dblink_connect_u (text, text) FROM public;
+
+ CREATE FUNCTION dblink_disconnect ()
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_disconnect'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION dblink_disconnect (text)
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_disconnect'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION dblink_open (text, text)
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_open'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION dblink_open (text, text, boolean)
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_open'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION dblink_open (text, text, text)
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_open'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION dblink_open (text, text, text, boolean)
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_open'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION dblink_fetch (text, int)
+ RETURNS setof record
+ AS 'MODULE_PATHNAME','dblink_fetch'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION dblink_fetch (text, int, boolean)
+ RETURNS setof record
+ AS 'MODULE_PATHNAME','dblink_fetch'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION dblink_fetch (text, int, anyelement)
+ RETURNS setof anyelement
+ AS 'MODULE_PATHNAME','dblink_fetch'
+ LANGUAGE C;
+
+ CREATE FUNCTION dblink_fetch (text, int, boolean, anyelement)
+ RETURNS setof anyelement
+ AS 'MODULE_PATHNAME','dblink_fetch'
+ LANGUAGE C;
+
+ CREATE FUNCTION dblink_fetch (text, text, int)
+ RETURNS setof record
+ AS 'MODULE_PATHNAME','dblink_fetch'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION dblink_fetch (text, text, int, boolean)
+ RETURNS setof record
+ AS 'MODULE_PATHNAME','dblink_fetch'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION dblink_close (text)
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_close'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION dblink_close (text, boolean)
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_close'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION dblink_close (text, text)
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_close'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION dblink_close (text, text, boolean)
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_close'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION dblink (text, text)
+ RETURNS setof record
+ AS 'MODULE_PATHNAME','dblink_record'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION dblink (text, text, boolean)
+ RETURNS setof record
+ AS 'MODULE_PATHNAME','dblink_record'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION dblink (text)
+ RETURNS setof record
+ AS 'MODULE_PATHNAME','dblink_record'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION dblink (text, boolean)
+ RETURNS setof record
+ AS 'MODULE_PATHNAME','dblink_record'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION dblink (text, text, anyelement)
+ RETURNS setof anyelement
+ AS 'MODULE_PATHNAME','dblink_record'
+ LANGUAGE C;
+
+ CREATE FUNCTION dblink (text, text, boolean, anyelement)
+ RETURNS setof anyelement
+ AS 'MODULE_PATHNAME','dblink_record'
+ LANGUAGE C;
+
+ CREATE FUNCTION dblink (text, anyelement)
+ RETURNS setof anyelement
+ AS 'MODULE_PATHNAME','dblink_record'
+ LANGUAGE C;
+
+ CREATE FUNCTION dblink (text, boolean, anyelement)
+ RETURNS setof anyelement
+ AS 'MODULE_PATHNAME','dblink_record'
+ LANGUAGE C;
+
+ CREATE FUNCTION dblink_exec (text, text)
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_exec'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION dblink_exec (text, text, boolean)
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_exec'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION dblink_exec (text)
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_exec'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION dblink_exec (text,boolean)
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_exec'
+ LANGUAGE C STRICT;
+
+ CREATE TYPE dblink_pkey_results AS (position int, colname text);
+
+ CREATE FUNCTION dblink_get_pkey (text)
+ RETURNS setof dblink_pkey_results
+ AS 'MODULE_PATHNAME','dblink_get_pkey'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION dblink_build_sql_insert (text, int2vector, int, _text, _text)
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_build_sql_insert'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION dblink_build_sql_delete (text, int2vector, int, _text)
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_build_sql_delete'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION dblink_build_sql_update (text, int2vector, int, _text, _text)
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_build_sql_update'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION dblink_current_query ()
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_current_query'
+ LANGUAGE C;
+
+ CREATE FUNCTION dblink_send_query(text, text)
+ RETURNS int4
+ AS 'MODULE_PATHNAME', 'dblink_send_query'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION dblink_is_busy(text)
+ RETURNS int4
+ AS 'MODULE_PATHNAME', 'dblink_is_busy'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION dblink_get_result(text)
+ RETURNS SETOF record
+ AS 'MODULE_PATHNAME', 'dblink_get_result'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION dblink_get_result(text, bool)
+ RETURNS SETOF record
+ AS 'MODULE_PATHNAME', 'dblink_get_result'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION dblink_get_result(text, anyelement)
+ RETURNS SETOF anyelement
+ AS 'MODULE_PATHNAME', 'dblink_get_result'
+ LANGUAGE C;
+
+ CREATE FUNCTION dblink_get_result(text, bool, anyelement)
+ RETURNS SETOF anyelement
+ AS 'MODULE_PATHNAME', 'dblink_get_result'
+ LANGUAGE C;
+
+ CREATE FUNCTION dblink_get_connections()
+ RETURNS text[]
+ AS 'MODULE_PATHNAME', 'dblink_get_connections'
+ LANGUAGE C;
+
+ CREATE FUNCTION dblink_cancel_query(text)
+ RETURNS text
+ AS 'MODULE_PATHNAME', 'dblink_cancel_query'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION dblink_error_message(text)
+ RETURNS text
+ AS 'MODULE_PATHNAME', 'dblink_error_message'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION dblink_get_notify(
+ OUT notify_name TEXT,
+ OUT be_pid INT4,
+ OUT extra TEXT
+ )
+ RETURNS setof record
+ AS 'MODULE_PATHNAME', 'dblink_get_notify'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION dblink_get_notify(
+ conname TEXT,
+ OUT notify_name TEXT,
+ OUT be_pid INT4,
+ OUT extra TEXT
+ )
+ RETURNS setof record
+ AS 'MODULE_PATHNAME', 'dblink_get_notify'
+ LANGUAGE C STRICT;
+
+ /* New stuff in 1.1 begins here */
+
+ CREATE FUNCTION dblink_fdw_validator(
+ options text[],
+ catalog oid
+ )
+ RETURNS void
+ AS 'MODULE_PATHNAME', 'dblink_fdw_validator'
+ LANGUAGE C STRICT;
+
+ CREATE FOREIGN DATA WRAPPER dblink_fdw VALIDATOR dblink_fdw_validator;
diff --git a/contrib/dblink/dblink.c b/contrib/dblink/dblink.c
index c5892d3..09c6d35 100644
*** a/contrib/dblink/dblink.c
--- b/contrib/dblink/dblink.c
*************** dblink_fetch(PG_FUNCTION_ARGS)
*** 537,588 ****
char *curname = NULL;
int howmany = 0;
bool fail = true; /* default to backward compatible */
prepTuplestoreResult(fcinfo);
DBLINK_INIT;
! if (PG_NARGS() == 4)
{
! /* text,text,int,bool */
conname = text_to_cstring(PG_GETARG_TEXT_PP(0));
curname = text_to_cstring(PG_GETARG_TEXT_PP(1));
howmany = PG_GETARG_INT32(2);
! fail = PG_GETARG_BOOL(3);
!
rconn = getConnectionByName(conname);
if (rconn)
conn = rconn->conn;
}
! else if (PG_NARGS() == 3)
! {
! /* text,text,int or text,int,bool */
! if (get_fn_expr_argtype(fcinfo->flinfo, 2) == BOOLOID)
! {
! curname = text_to_cstring(PG_GETARG_TEXT_PP(0));
! howmany = PG_GETARG_INT32(1);
! fail = PG_GETARG_BOOL(2);
! conn = pconn->conn;
! }
! else
! {
! conname = text_to_cstring(PG_GETARG_TEXT_PP(0));
! curname = text_to_cstring(PG_GETARG_TEXT_PP(1));
! howmany = PG_GETARG_INT32(2);
!
! rconn = getConnectionByName(conname);
! if (rconn)
! conn = rconn->conn;
! }
! }
! else if (PG_NARGS() == 2)
{
! /* text,int */
curname = text_to_cstring(PG_GETARG_TEXT_PP(0));
howmany = PG_GETARG_INT32(1);
conn = pconn->conn;
}
if (!conn)
DBLINK_CONN_NOT_AVAIL;
--- 537,575 ----
char *curname = NULL;
int howmany = 0;
bool fail = true; /* default to backward compatible */
+ int first_optarg;
prepTuplestoreResult(fcinfo);
DBLINK_INIT;
! if (get_fn_expr_argtype(fcinfo->flinfo,1) == TEXTOID)
{
! /* text,text,int,[bool],[anytype] */
conname = text_to_cstring(PG_GETARG_TEXT_PP(0));
curname = text_to_cstring(PG_GETARG_TEXT_PP(1));
howmany = PG_GETARG_INT32(2);
! first_optarg = 3;
rconn = getConnectionByName(conname);
if (rconn)
conn = rconn->conn;
}
! else
{
! /* text,int,[bool],[anytype] */
curname = text_to_cstring(PG_GETARG_TEXT_PP(0));
howmany = PG_GETARG_INT32(1);
+ first_optarg = 2;
conn = pconn->conn;
}
+ if ((first_optarg < PG_NARGS()) &&
+ (get_fn_expr_argtype(fcinfo->flinfo, first_optarg) == BOOLOID))
+ {
+ fail = PG_GETARG_BOOL(first_optarg);
+ }
+ /* ignore the last arg (if any) as it just provides a return rowtype */
+
if (!conn)
DBLINK_CONN_NOT_AVAIL;
*************** dblink_record_internal(FunctionCallInfo
*** 680,706 ****
if (!is_async)
{
! if (PG_NARGS() == 3)
{
! /* text,text,bool */
DBLINK_GET_CONN;
sql = text_to_cstring(PG_GETARG_TEXT_PP(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 = pconn->conn;
sql = text_to_cstring(PG_GETARG_TEXT_PP(0));
fail = PG_GETARG_BOOL(1);
}
! else
{
DBLINK_GET_CONN;
sql = text_to_cstring(PG_GETARG_TEXT_PP(1));
}
}
else if (PG_NARGS() == 1)
{
--- 667,734 ----
if (!is_async)
{
! if (PG_NARGS() == 4)
{
! /* text,text,bool,anyelement */
! if (PG_ARGISNULL(0) || PG_ARGISNULL(1) || PG_ARGISNULL(2))
! PG_RETURN_NULL();
!
DBLINK_GET_CONN;
sql = text_to_cstring(PG_GETARG_TEXT_PP(1));
fail = PG_GETARG_BOOL(2);
+ /* ignore fourth arg as it just provides a return rowtype */
+ }
+ else if (PG_NARGS() == 3)
+ {
+ /* text,text,bool or text,text,anyelement or text,bool,anyelement */
+ if (get_fn_expr_argtype(fcinfo->flinfo, 2) == BOOLOID)
+ {
+ DBLINK_GET_CONN;
+ sql = text_to_cstring(PG_GETARG_TEXT_PP(1));
+ fail = PG_GETARG_BOOL(2);
+ }
+ else
+ {
+ if (PG_ARGISNULL(0) || PG_ARGISNULL(1))
+ PG_RETURN_NULL();
+
+ if (get_fn_expr_argtype(fcinfo->flinfo, 1) == BOOLOID)
+ {
+ conn = pconn->conn;
+ sql = text_to_cstring(PG_GETARG_TEXT_PP(0));
+ fail = PG_GETARG_BOOL(1);
+ }
+ else
+ {
+ DBLINK_GET_CONN;
+ sql = text_to_cstring(PG_GETARG_TEXT_PP(1));
+ }
+ /* ignore third arg as it just provides a return rowtype */
+ }
}
else if (PG_NARGS() == 2)
{
! /* text,text or text,bool or text,anyelement */
if (get_fn_expr_argtype(fcinfo->flinfo, 1) == BOOLOID)
{
conn = pconn->conn;
sql = text_to_cstring(PG_GETARG_TEXT_PP(0));
fail = PG_GETARG_BOOL(1);
}
! else if (get_fn_expr_argtype(fcinfo->flinfo, 1) == TEXTOID)
{
DBLINK_GET_CONN;
sql = text_to_cstring(PG_GETARG_TEXT_PP(1));
}
+ else
+ {
+ if (PG_ARGISNULL(0))
+ PG_RETURN_NULL();
+
+ conn = pconn->conn;
+ sql = text_to_cstring(PG_GETARG_TEXT_PP(0));
+ /* ignore second arg as it just provides a return rowtype */
+ }
}
else if (PG_NARGS() == 1)
{
*************** dblink_record_internal(FunctionCallInfo
*** 715,725 ****
else /* is_async */
{
/* get async result */
! if (PG_NARGS() == 2)
{
! /* text,bool */
DBLINK_GET_NAMED_CONN;
fail = PG_GETARG_BOOL(1);
}
else if (PG_NARGS() == 1)
{
--- 743,774 ----
else /* is_async */
{
/* get async result */
! if (PG_NARGS() == 3)
{
! /* text,bool,anyelement */
! if (PG_ARGISNULL(0) || PG_ARGISNULL(1))
! PG_RETURN_NULL();
!
DBLINK_GET_NAMED_CONN;
fail = PG_GETARG_BOOL(1);
+ /* ignore third arg as it just provides a return rowtype */
+ }
+ else if (PG_NARGS() == 2)
+ {
+ /* text,bool or text,anyelement */
+ if (get_fn_expr_argtype(fcinfo->flinfo, 1) == BOOLOID)
+ {
+ DBLINK_GET_NAMED_CONN;
+ fail = PG_GETARG_BOOL(1);
+ }
+ else
+ {
+ if (PG_ARGISNULL(0))
+ PG_RETURN_NULL();
+
+ DBLINK_GET_NAMED_CONN;
+ /* ignore second arg as it just provides a return rowtype */
+ }
}
else if (PG_NARGS() == 1)
{
diff --git a/contrib/dblink/dblink.control b/contrib/dblink/dblink.control
index 39f439a..bdd17d2 100644
*** a/contrib/dblink/dblink.control
--- b/contrib/dblink/dblink.control
***************
*** 1,5 ****
# dblink extension
comment = 'connect to other PostgreSQL databases from within a database'
! default_version = '1.1'
module_pathname = '$libdir/dblink'
relocatable = true
--- 1,5 ----
# dblink extension
comment = 'connect to other PostgreSQL databases from within a database'
! default_version = '1.2'
module_pathname = '$libdir/dblink'
relocatable = true
diff --git a/contrib/dblink/expected/dblink.out b/contrib/dblink/expected/dblink.out
index a49b562..290bb5b 100644
*** a/contrib/dblink/expected/dblink.out
--- b/contrib/dblink/expected/dblink.out
*************** WHERE t.a > 7;
*** 101,106 ****
--- 101,116 ----
9 | j | {a9,b9,c9}
(2 rows)
+ -- dblink with anyelement
+ SELECT *
+ FROM dblink('dbname=contrib_regression','SELECT * FROM foo',null::foo) as t
+ WHERE t.f1 > 7;
+ f1 | f2 | f3
+ ----+----+------------
+ 8 | i | {a8,b8,c8}
+ 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[])
*************** FROM dblink_fetch('rmt_foo_cursor',4) AS
*** 198,211 ****
3 | d | {a3,b3,c3}
(4 rows)
SELECT *
! FROM dblink_fetch('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 two rows left
--- 208,222 ----
3 | d | {a3,b3,c3}
(4 rows)
+ -- fetch using anyelement, which will change the column names
SELECT *
! FROM dblink_fetch('rmt_foo_cursor',4,null::foo) AS t;
! f1 | f2 | f3
! ----+----+------------
! 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 two rows left
*************** UNION
*** 726,731 ****
--- 737,791 ----
UNION
(SELECT * from dblink_get_result('dtest3') as t3(f1 int, f2 text, f3 text[]))
ORDER by f1;
+ -- clear off the connections for the next query;
+ SELECT * from dblink_get_result('dtest1') as t1(f1 int, f2 text, f3 text[])
+ UNION
+ SELECT * from dblink_get_result('dtest2') as t2(f1 int, f2 text, f3 text[])
+ UNION
+ SELECT * from dblink_get_result('dtest3') as t3(f1 int, f2 text, f3 text[]);
+ f1 | f2 | f3
+ ----+----+----
+ (0 rows)
+
+ SELECT * from
+ dblink_send_query('dtest1', 'select * from foo where f1 < 3') as t1;
+ t1
+ ----
+ 1
+ (1 row)
+
+ SELECT * from
+ dblink_send_query('dtest2', 'select * from foo where f1 > 2 and f1 < 7') as t1;
+ t1
+ ----
+ 1
+ (1 row)
+
+ SELECT * from
+ dblink_send_query('dtest3', 'select * from foo where f1 > 6') as t1;
+ t1
+ ----
+ 1
+ (1 row)
+
+ CREATE TEMPORARY TABLE result_anyelement AS
+ (SELECT * from dblink_get_result('dtest1',null::foo))
+ UNION
+ (SELECT * from dblink_get_result('dtest2',null::foo))
+ UNION
+ (SELECT * from dblink_get_result('dtest3',null::foo))
+ ORDER by f1;
+ -- result and result_anyelement should be identical
+ SELECT * FROM result EXCEPT SELECT * FROM result_anyelement;
+ f1 | f2 | f3
+ ----+----+----
+ (0 rows)
+
+ SELECT * FROM result_anyelement EXCEPT SELECT * FROM result;
+ f1 | f2 | f3
+ ----+----+----
+ (0 rows)
+
-- dblink_get_connections returns an array with elements in a machine-dependent
-- ordering, so we must resort to unnesting and sorting for a stable result
create function unnest(anyarray) returns setof anyelement
diff --git a/contrib/dblink/sql/dblink.sql b/contrib/dblink/sql/dblink.sql
index ea78cc2..9324b97 100644
*** a/contrib/dblink/sql/dblink.sql
--- b/contrib/dblink/sql/dblink.sql
*************** SELECT *
*** 64,69 ****
--- 64,74 ----
FROM dblink(connection_parameters(),'SELECT * FROM foo') AS t(a int, b text, c text[])
WHERE t.a > 7;
+ -- dblink with anyelement
+ SELECT *
+ FROM dblink('dbname=contrib_regression','SELECT * FROM foo',null::foo) as t
+ WHERE t.f1 > 7;
+
-- should generate "connection not available" error
SELECT *
FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
*************** SELECT dblink_open('rmt_foo_cursor','SEL
*** 124,131 ****
SELECT *
FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
SELECT *
! FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
-- this one only finds two rows left
SELECT *
--- 129,137 ----
SELECT *
FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
+ -- fetch using anyelement, which will change the column names
SELECT *
! FROM dblink_fetch('rmt_foo_cursor',4,null::foo) AS t;
-- this one only finds two rows left
SELECT *
*************** UNION
*** 365,370 ****
--- 371,402 ----
(SELECT * from dblink_get_result('dtest3') as t3(f1 int, f2 text, f3 text[]))
ORDER by f1;
+ -- clear off the connections for the next query;
+ SELECT * from dblink_get_result('dtest1') as t1(f1 int, f2 text, f3 text[])
+ UNION
+ SELECT * from dblink_get_result('dtest2') as t2(f1 int, f2 text, f3 text[])
+ UNION
+ SELECT * from dblink_get_result('dtest3') as t3(f1 int, f2 text, f3 text[]);
+
+ SELECT * from
+ dblink_send_query('dtest1', 'select * from foo where f1 < 3') as t1;
+ SELECT * from
+ dblink_send_query('dtest2', 'select * from foo where f1 > 2 and f1 < 7') as t1;
+ SELECT * from
+ dblink_send_query('dtest3', 'select * from foo where f1 > 6') as t1;
+
+ CREATE TEMPORARY TABLE result_anyelement AS
+ (SELECT * from dblink_get_result('dtest1',null::foo))
+ UNION
+ (SELECT * from dblink_get_result('dtest2',null::foo))
+ UNION
+ (SELECT * from dblink_get_result('dtest3',null::foo))
+ ORDER by f1;
+
+ -- result and result_anyelement should be identical
+ SELECT * FROM result EXCEPT SELECT * FROM result_anyelement;
+ SELECT * FROM result_anyelement EXCEPT SELECT * FROM result;
+
-- dblink_get_connections returns an array with elements in a machine-dependent
-- ordering, so we must resort to unnesting and sorting for a stable result
create function unnest(anyarray) returns setof anyelement
diff --git a/doc/src/sgml/dblink.sgml b/doc/src/sgml/dblink.sgml
index b07ac48..4c472ab 100644
*** a/doc/src/sgml/dblink.sgml
--- b/doc/src/sgml/dblink.sgml
*************** SELECT dblink_disconnect('myconn');
*** 334,339 ****
--- 334,342 ----
dblink(text connname, text sql [, bool fail_on_error]) returns setof record
dblink(text connstr, text sql [, bool fail_on_error]) returns setof record
dblink(text sql [, bool fail_on_error]) returns setof record
+ dblink(text connname, text sql [, bool fail_on_error], anyelement rowtype) returns setof rowtype
+ dblink(text connstr, text sql [, bool fail_on_error], anyelement rowtype) returns setof rowtype
+ dblink(text sql [, bool fail_on_error], anyelement rowtype) returns setof rowtype
</synopsis>
</refsynopsisdiv>
*************** dblink(text sql [, bool fail_on_error])
*** 399,404 ****
--- 402,420 ----
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><parameter>rowtype</parameter></term>
+ <listitem>
+ <para>
+ The structure of the result set to be returned from the SQL query.
+ This is typically a null value casted as an existing composite type
+ or table type. The type must exist on the local server, but need
+ not exist on the remote server.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</refsect1>
*************** dblink(text sql [, bool fail_on_error])
*** 406,416 ****
<title>Return Value</title>
<para>
The function returns the row(s) produced by the query. Since
! <function>dblink</> can be used with any query, it is declared
! to return <type>record</>, rather than specifying any particular
! set of columns. This means that you must specify the expected
! set of columns in the calling query — otherwise
<productname>PostgreSQL</> would not know what to expect.
Here is an example:
--- 422,433 ----
<title>Return Value</title>
<para>
+
The function returns the row(s) produced by the query. Since
! <function>dblink</> can be used with any query, the structure
! of the set of columns must be declared either with the
! <parameter>rowtype</> parameter, or the set of columns must
! be specified in the calling query — otherwise
<productname>PostgreSQL</> would not know what to expect.
Here is an example:
*************** SELECT *
*** 435,440 ****
--- 452,468 ----
does not insist on exact type matches either. It will succeed
so long as the returned data strings are valid input for the
column type declared in the <literal>FROM</> clause.
+
+ <programlisting>
+ CREATE TYPE subset_of_pg_proc AS (proname name, prosrc text);
+ SELECT *
+ FROM dblink('dbname=mydb', 'select proname, prosrc from pg_proc',
+ null::subset_of_pg_proc)
+ WHERE proname LIKE 'bytea%';
+ </programlisting>
+
+ Alternately, one can specify a <parameter>rowtype</> which has the
+ same effect as if a typed <quote>alias</> had been used.
</para>
</refsect1>
*************** SELECT * FROM dblink('myconn', 'select p
*** 528,533 ****
--- 556,570 ----
byteain | byteain
byteaout | byteaout
(14 rows)
+
+ SELECT schemaname, tablename, hasindexes
+ FROM dblink('dbname=dblink_test','select * from pg_tables',
+ null::pg_tables)
+ WHERE schemaname = 'pg_catalog' and tablename = 'pg_type';
+ schemaname | tablename | hasindexes
+ ------------+-----------+------------
+ pg_catalog | pg_type | t
+ (1 row)
</screen>
</refsect1>
</refentry>
*************** SELECT dblink_open('foo', 'select pronam
*** 812,817 ****
--- 849,858 ----
<synopsis>
dblink_fetch(text cursorname, int howmany [, bool fail_on_error]) returns setof record
dblink_fetch(text connname, text cursorname, int howmany [, bool fail_on_error]) returns setof record
+ dblink_fetch(text cursorname, int howmany, anyelement rowtype) returns setof anyelement
+ dblink_fetch(text cursorname, int howmany, bool fail_on_error, anyelement rowtype) returns setof anyelement
+ dblink_fetch(text connname, text cursorname, int howmany, anyelement rowtype) returns setof anyelement
+ dblink_fetch(text connname, text cursorname, int howmany, bool fail_on_error, anyelement rowtype) returns setof anyelement
</synopsis>
</refsynopsisdiv>
*************** dblink_fetch(text connname, text cursorn
*** 869,874 ****
--- 910,926 ----
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><parameter>rowtype</parameter></term>
+ <listitem>
+ <para>
+ Specifies the structure of the result set to be returned.
+ </para>
+ </listitem>
+ </varlistentry>
+
+
</variablelist>
</refsect1>
*************** dblink_fetch(text connname, text cursorn
*** 877,884 ****
<para>
The function returns the row(s) fetched from the cursor. To use this
! function, you will need to specify the expected set of columns,
! as previously discussed for <function>dblink</>.
</para>
</refsect1>
--- 929,936 ----
<para>
The function returns the row(s) fetched from the cursor. To use this
! function, you will need to specify the expected set of columns, or specify
! a <parameter>rowtype</> as previously discussed for <function>dblink</>.
</para>
</refsect1>
*************** SELECT * FROM dblink_get_notify();
*** 1410,1415 ****
--- 1462,1469 ----
<refsynopsisdiv>
<synopsis>
dblink_get_result(text connname [, bool fail_on_error]) returns setof record
+ dblink_get_result(text connname, anyelement rowtype) returns setof anyelement
+ dblink_get_result(text connname, bool fail_on_error, anyelement rowtype) returns setof anyelement
</synopsis>
</refsynopsisdiv>
*************** dblink_get_result(text connname [, bool
*** 1448,1453 ****
--- 1502,1519 ----
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><parameter>rowtype</parameter></term>
+ <listitem>
+ <para>
+ Specifies the expected column types and names expected from the
+ query.
+ </para>
+ </listitem>
+ </varlistentry>
+
+
</variablelist>
</refsect1>
*************** dblink_get_result(text connname [, bool
*** 1457,1464 ****
<para>
For an async query (that is, a SQL statement returning rows),
the function returns the row(s) produced by the query. To use this
! function, you will need to specify the expected set of columns,
! as previously discussed for <function>dblink</>.
</para>
<para>
--- 1523,1531 ----
<para>
For an async query (that is, a SQL statement returning rows),
the function returns the row(s) produced by the query. To use this
! function, you will need to specify the expected set of columns
! or a <parameter>rowtype</>, as previously discussed for
! <function>dblink</>.
</para>
<para>
*************** contrib_regression=# SELECT * FROM dblin
*** 1552,1557 ****
--- 1619,1645 ----
f1 | f2 | f3
----+----+----
(0 rows)
+
+ dblink_test=# SELECT dblink_connect('dtest1rowtype','dbname=dblink_test');
+ dblink_connect
+ ----------------
+ OK
+ (1 row)
+
+ dblink_test=# CREATE TYPE a_composite_type AS ( x int, y text, z float );
+ CREATE TYPE
+ dblink_test=# SELECT * FROM dblink_send_query('dtest1rowtype',
+ dblink_test-# format('SELECT %s, %L, %s',1,'two',3.0));
+ dblink_send_query
+ -------------------
+ 1
+ (1 row)
+
+ dblink_test=# SELECT * FROM dblink_get_result('dtest1rowtype',null::a_composite_type);
+ x | y | z
+ ---+-----+---
+ 1 | two | 3
+ (1 row)
</screen>
</refsect1>
</refentry>
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
