Re: dblink: add polymorphic functions.

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

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