On Mon, Jul 6, 2015 at 4:25 AM, Joe Conway <[email protected]> wrote:
> -----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.
This extension format is still incorrect, as there is no need for
dblink--1.1.sql as on the latest version of an extension needs to be
supported, and Makefile needs to be updated in consequence. Please
find attached an updated patch.
+-- fetch using anyelement, which will change the column names
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}
+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)
Why isn't this test case not left alone? It looks like a regression to
me to not test it.
Regards,
--
Michael
diff --git a/contrib/dblink/Makefile b/contrib/dblink/Makefile
index b8d5157..5189758 100644
--- a/contrib/dblink/Makefile
+++ b/contrib/dblink/Makefile
@@ -6,7 +6,8 @@ PG_CPPFLAGS = -I$(libpq_srcdir)
SHLIB_LINK = $(libpq)
EXTENSION = dblink
-DATA = dblink--1.1.sql dblink--1.0--1.1.sql dblink--unpackaged--1.0.sql
+DATA = dblink--1.2.sql dblink--1.1--1.2.sql dblink--1.0--1.1.sql \
+ dblink--unpackaged--1.0.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
new file mode 100644
index 0000000..a82e507
--- /dev/null
+++ b/contrib/dblink/dblink--1.1--1.2.sql
@@ -0,0 +1,44 @@
+/* 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.1.sql b/contrib/dblink/dblink--1.2.sql
similarity index 84%
rename from contrib/dblink/dblink--1.1.sql
rename to contrib/dblink/dblink--1.2.sql
index 8733553..1d84df2 100644
--- a/contrib/dblink/dblink--1.1.sql
+++ b/contrib/dblink/dblink--1.2.sql
@@ -1,4 +1,4 @@
-/* contrib/dblink/dblink--1.1.sql */
+/* 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
@@ -71,6 +71,16 @@ 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'
@@ -121,6 +131,26 @@ 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'
@@ -188,6 +218,16 @@ 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'
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
@@ -537,52 +537,39 @@ dblink_fetch(PG_FUNCTION_ARGS)
char *curname = NULL;
int howmany = 0;
bool fail = true; /* default to backward compatible */
+ int first_optarg;
prepTuplestoreResult(fcinfo);
DBLINK_INIT;
- if (PG_NARGS() == 4)
+ if (get_fn_expr_argtype(fcinfo->flinfo,1) == TEXTOID)
{
- /* text,text,int,bool */
+ /* 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);
- fail = PG_GETARG_BOOL(3);
-
+ first_optarg = 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)
+ else
{
- /* text,int */
+ /* 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;
@@ -680,27 +667,68 @@ dblink_record_internal(FunctionCallInfo fcinfo, bool is_async)
if (!is_async)
{
- if (PG_NARGS() == 3)
+ if (PG_NARGS() == 4)
{
- /* text,text,bool */
+ /* 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 */
+ /* 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
+ 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)
{
@@ -715,11 +743,32 @@ dblink_record_internal(FunctionCallInfo fcinfo, bool is_async)
else /* is_async */
{
/* get async result */
- if (PG_NARGS() == 2)
+ if (PG_NARGS() == 3)
{
- /* text,bool */
+ /* 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 +1,5 @@
# dblink extension
comment = 'connect to other PostgreSQL databases from within a database'
-default_version = '1.1'
+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
@@ -101,6 +101,16 @@ WHERE t.a > 7;
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[])
@@ -198,14 +208,15 @@ FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
3 | d | {a3,b3,c3}
(4 rows)
+-- fetch using anyelement, which will change the column names
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}
+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
@@ -726,6 +737,55 @@ UNION
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
@@ -64,6 +64,11 @@ SELECT *
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[])
@@ -124,8 +129,9 @@ SELECT dblink_open('rmt_foo_cursor','SELECT * FROM foo');
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) AS t(a int, b text, c text[]);
+FROM dblink_fetch('rmt_foo_cursor',4,null::foo) AS t;
-- this one only finds two rows left
SELECT *
@@ -365,6 +371,32 @@ 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[]);
+
+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
@@ -334,6 +334,9 @@ SELECT dblink_disconnect('myconn');
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>
@@ -399,6 +402,19 @@ dblink(text sql [, bool fail_on_error]) returns setof record
</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>
@@ -406,11 +422,12 @@ dblink(text sql [, bool fail_on_error]) returns setof record
<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
+ <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:
@@ -435,6 +452,17 @@ SELECT *
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>
@@ -528,6 +556,15 @@ SELECT * FROM dblink('myconn', 'select proname, prosrc from pg_proc')
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>
@@ -812,6 +849,10 @@ SELECT dblink_open('foo', 'select proname, prosrc from pg_proc');
<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>
@@ -869,6 +910,17 @@ dblink_fetch(text connname, text cursorname, int howmany [, bool fail_on_error])
</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>
@@ -877,8 +929,8 @@ dblink_fetch(text connname, text cursorname, int howmany [, bool fail_on_error])
<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</>.
+ function, you will need to specify the expected set of columns, or specify
+ a <parameter>rowtype</> as previously discussed for <function>dblink</>.
</para>
</refsect1>
@@ -1410,6 +1462,8 @@ SELECT * FROM dblink_get_notify();
<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>
@@ -1448,6 +1502,18 @@ dblink_get_result(text connname [, bool fail_on_error]) returns setof record
</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>
@@ -1457,8 +1523,9 @@ dblink_get_result(text connname [, bool fail_on_error]) returns setof record
<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</>.
+ function, you will need to specify the expected set of columns
+ or a <parameter>rowtype</>, as previously discussed for
+ <function>dblink</>.
</para>
<para>
@@ -1552,6 +1619,27 @@ contrib_regression=# SELECT * FROM dblink_get_result('dtest1') AS t1(f1 int, f2
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
