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 |
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 |