On Mon, Jul 6, 2015 at 4:25 AM, Joe Conway <m...@joeconway.com> 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 (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers