Re: dblink: add polymorphic functions.

On Thu, Feb 19, 2015 at 4:06 PM, Corey Huinker <corey(dot)huinker(at)gmail(dot)com> wrote:
> 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)

sorry for the late reply. I'm a little concerned about the state of
overloading here. If I'm not mistaken, you may have introduced a
pretty serious backwards compatibility issue. Having the two
signatures (text, anyelement) and (text, boolean) will now fail
anytime (unknown, unknown) is passed, and that's a pretty common
invocation. If I'm right, quickly scanning the function list, I don't
think there's an easy solution to this issue other than adding an
alternately named call.

merlin