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()
On Sun, Feb 22, 2015 at 11:38 PM, Corey Huinker <[email protected]>
wrote:
> "nevermind". Found it.
>
> On Sun, Feb 22, 2015 at 11:18 PM, Corey Huinker <[email protected]>
> wrote:
>
>> Yes, that was it, I discovered it myself and should have posted a
>> "nevermind".
>>
>> Now I'm slogging through figuring out where to find elog() messages from
>> the temporary server. It's slow, but it's progress.
>>
>> On Sun, Feb 22, 2015 at 10:39 PM, Michael Paquier <
>> [email protected]> wrote:
>>
>>> On Mon, Feb 23, 2015 at 12:03 PM, Corey Huinker <[email protected]>
>>> wrote:
>>> > + ERROR: could not stat file
>>> >
>>> "/home/ubuntu/src/postgres/contrib/dblink/tmp_check/install/usr/local/pgsql/share/extension/dblink--1.2.sql":
>>> > No such file or directory
>>>
>>> Didn't you forget to add dblink--1.2.sql to DATA in contrib/dblink
>>> Makefile? That would explain why this file has not been included in
>>> the temporary installation deployed by pg_regress.
>>> --
>>> Michael
>>>
>>
>>
>
diff --git a/contrib/dblink/Makefile b/contrib/dblink/Makefile
index e833b92..a00466e 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.1.sql dblink--1.0--1.1.sql dblink--unpackaged--1.0.sql \
+ dblink--1.2.sql dblink--1.1--1.2.sql
REGRESS = paths dblink
REGRESS_OPTS = --dlpath=$(top_builddir)/src/test/regress \
diff --git a/contrib/dblink/dblink--1.1--1.2.sql
b/contrib/dblink/dblink--1.1--1.2.sql
index e5a0900..128611d 100644
--- a/contrib/dblink/dblink--1.1--1.2.sql
+++ b/contrib/dblink/dblink--1.1--1.2.sql
@@ -32,3 +32,15 @@ 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 bf5ddaa..9d31e2e 100644
--- a/contrib/dblink/dblink--1.2.sql
+++ b/contrib/dblink/dblink--1.2.sql
@@ -71,6 +71,19 @@ 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'
diff --git a/contrib/dblink/dblink.c b/contrib/dblink/dblink.c
index 009b877..fde750c 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 87eb142..799ba95 100644
--- a/contrib/dblink/expected/dblink.out
+++ b/contrib/dblink/expected/dblink.out
@@ -98,6 +98,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[])
@@ -195,14 +205,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
@@ -723,6 +734,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 5305d5a..a29ac44 100644
--- a/contrib/dblink/sql/dblink.sql
+++ b/contrib/dblink/sql/dblink.sql
@@ -60,6 +60,11 @@ SELECT *
FROM dblink('dbname=contrib_regression','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[])
@@ -120,8 +125,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 *
@@ -361,6 +367,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
