dblink: add polymorphic functions.

From: Corey Huinker <corey(dot)huinker(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: dblink: add polymorphic functions.
Date: 2015-02-19 22:06:31
Message-ID: CADkLM=d9AEZYQ2TpzOJQwBb42nV49YQy6b6S=z4q9svJiQLB-g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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(at)dblink_test# create extension dblink;
CREATE EXTENSION
Time: 12.778 ms
[local]:ubuntu(at)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(at)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.

Attachment Content-Type Size
dblink-anyelement.diff text/plain 4.4 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Gilberto Castillo 2015-02-19 22:15:17 Re: FDW for Oracle
Previous Message Gilberto Castillo 2015-02-19 22:06:10 FDW for Oracle