Thread: dblink: add polymorphic functions.

dblink: add polymorphic functions.

From

Corey Huinker

Date:

20 February 2015, 01:06:58

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.

Thread: dblink: add polymorphic functions.

Attachment

Attachment

Attachment

Attachment

Attachment