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 (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers