Re: dblink: add polymorphic functions.

On Wed, Jul 29, 2015 at 10:48 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Corey Huinker <corey(dot)huinker(at)gmail(dot)com> writes:
> > On Wed, Jul 29, 2015 at 3:48 AM, Heikki Linnakangas <hlinnaka(at)iki(dot)fi>
> wrote:
> >> Let's pursue the "CAST(srf() AS row_rtype)" syntax that Joe suggested
> >> upthread (
> >> http://www.postgresql.org/message-id/559A9643.9070409@joeconway.com).
> For
> >> some reason, the discussion went on around the details of the submitted
> >> patch after that, even though everyone seemed to prefer the CAST()
> syntax.
>
> > I'm all for adding that syntax, but it wouldn't be useful for my purposes
> > unless row_rtype could be a variable, and my understanding is that it
> can't.
>
> Not sure why inserting a variable name is so much better than inserting a
> type name?
>
> regards, tom lane
>

Apologies in advance if I'm going over things you already know. Just trying
to package up the problem statement into something easily digestible.

In a polymorphic function, I don't know the return type. It's whatever type
was specified on the function call.

Say I've written a function with a function like
outer_polymorphic_function(p_rowtype anyelement,p1 ,p2,p3, ...) returns
setof anyelement

And inside that function is a series (probably a handful, but potentially
thousands) of async dblink calls, and their corresponding calls to
dblink_get_result(), which currently return setof record, each of which
needs to be casted to whatever anyelement happens to be given this
execution.

Currently, I have to look up p_rowtype in pg_attribute and pg_class, render
the column specs as valid SQL, and compose the query as a string

fetch_values_query := 'select * from dblink_get_result($1) as t ( ' ||
'c1 type, c2 othertype, ... ' || ')';

and then execute that dynamically like so:

return query execute fetch_values_query using l_connection_name;

It would be nice if I didn't have to resort to dynamic SQL do to this.

If the CAST() function is implemented, but does not allow to cast as a
variable, then I'm in the same boat:

fetch_values_query := 'select * from CAST(dblink_get_result($1) as ' ||
pg_typeof(p_rowtype) || ')';

Admittedly, that's a bit cleaner, but I'm still executing that dynamic SQL
once per connection I made, and there could be a lot of them.

If there were dblink() functions that returned polymorphic results, it
would be a non issue:

dblink_send_query('conn1','select * from
thing_i_know_is_shaped_like_my_rowtype')
...
return query select * from dblink_get_result_any(p_rowtype,'conn1');

I'm all for the expanded capabilities of CAST(), but I have a specific need
for polymorphic dblink() functions.