In the course of writing a small side project which hopefully will make its
way onto pgxn soon, I was writing functions that had a polymorphic result
set.
create function foo( p_row_type anyelement, p_param1 ...) returns setof
anyelement
Inside that function would be multiple calls to dblink() in both
synchronous and async modes. It is a requirement of the function that each
query return a result set conforming to the structure passed into
p_row_type, but there was no way for me to express that.
Unfortunately, there's no way to say
select * from dblink_get_result('connname') as <polymorphic record type>;
Instead, I had to generate the query as a string like this.
with x as (
select a.attname || ' ' || pg_catalog.format_type(a.atttypid,
a.atttypmod) as sql_text
from pg_catalog.pg_attribute a
where a.attrelid = pg_typeof(p_row_type)::text::regclass
and a.attisdropped is false
and a.attnum > 0
order by a.attnum )
select format('select * from dblink_get_result($1) as
t(%s)',string_agg(x.sql_text,','))
from x;
Moreover, I'm now executing this string dynamically, incurring reparsing
and replanning each time (and if all goes well, this would be executed many
times). Granted, I could avoid that by rewriting the stored procedure in C
and using prepared statements (not available in PL/PGSQL), but it seemed a
shame that dblink couldn't itself handle this polymorphism.
So with a little help, we were able to come up with polymorphic set
returning dblink functions.
Below is the results of the patch applied to a stock 9.4 installation.
[local]:ubuntu@dblink_test# create extension dblink;
CREATE EXTENSION
Time: 12.778 ms
[local]:ubuntu@dblink_test# \df dblink
List of functions
Schema | Name | Result data type | Argument data types |
Type
--------+--------+------------------+---------------------------------+--------
public | dblink | SETOF record | text |
normal
public | dblink | SETOF anyelement | text, anyelement |
normal
public | dblink | SETOF record | text, boolean |
normal
public | dblink | SETOF anyelement | text, boolean, anyelement |
normal
public | dblink | SETOF record | text, text |
normal
public | dblink | SETOF anyelement | text, text, anyelement |
normal
public | dblink | SETOF record | text, text, boolean |
normal
public | dblink | SETOF anyelement | text, text, boolean, anyelement |
normal
(8 rows)
[local]:ubuntu@dblink_test# *select * from
dblink('dbname=dblink_test','select * from pg_tables order by tablename
limit 2',null::pg_tables);*
schemaname | tablename | tableowner | tablespace | hasindexes |
hasrules | hastriggers
------------+--------------+------------+------------+------------+----------+-------------
pg_catalog | pg_aggregate | postgres | | t | f
| f
pg_catalog | pg_am | postgres | | t | f
| f
(2 rows)
Time: 6.813 ms
Obviously, this is a trivial case, but it shows that the polymorphic
function works as expected, and the code that uses it will be a lot more
straightforward.
Proposed patch attached.
diff --git a/contrib/dblink/dblink--1.1.sql b/contrib/dblink/dblink--1.1.sql
index 8733553..bf5ddaa 100644
--- a/contrib/dblink/dblink--1.1.sql
+++ b/contrib/dblink/dblink--1.1.sql
@@ -121,6 +121,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 +208,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 9fe750e..eb7f5f9 100644
--- a/contrib/dblink/dblink.c
+++ b/contrib/dblink/dblink.c
@@ -680,27 +680,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 +756,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)
{
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
