Re: dblink: add polymorphic functions.

On Mon, Jul 6, 2015 at 9:52 AM, Joe Conway <mail@joeconway.com> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On 07/06/2015 07:37 AM, Merlin Moncure wrote:
>> yup, and at least one case now fails where previously it ran
>> through: postgres=# select * from dblink('a', 'b', 'c'); ERROR:
>> function dblink(unknown, unknown, unknown) is not unique
>
> Hmm, that is an issue, possibly a fatal one.
>
> When Cory first mentioned this to me over a year ago we discussed some
> other, arguably better and more generic solutions. One was to build
> support for:
>
>   SELECT * FROM srf() AS TYPE OF(foo);
>
> The second idea I think is actually SQL standard if I recall correctly:
>
>   SELECT * FROM CAST(srf() AS foo) x;
>
> Currently this works:
>
> 8<--------------------
> select *
> from cast (row(11,'l','{a11,b11,c11}') as foo);
>  f1 | f2 |      f3
> - ----+----+---------------
>  11 | l  | {a11,b11,c11}
> (1 row)
> 8<--------------------
>
> But this fails:
>
> 8<--------------------
> select *
> from cast
> (dblink('dbname=contrib_regression','select * from foo') as foo);
> ERROR:  cannot cast type record to foo
> 8<--------------------
>
> Comments in the source have this to say:
>
> 8<--------------------
> /*
>  * coerce_record_to_complex
>  *              Coerce a RECORD to a specific composite type.
>  *
>  * Currently we only support this for inputs that are RowExprs or
>  * whole-row Vars.
>  */
> 8<--------------------
>
> That explains why the first example works while the second does not.
> I'm not sure how hard it would be to fix that, but it appears that
> that is where we should focus.

Yeah.  FWIW, here's my 0.02$:  I use dblink all the time, for all
kinds of reasons, vastly preferring to have control over the query
string (vs. FDW type solutions).  I have two basic gripes with it.  #1
is that remote queries are not cancelled over all call sites when
cancelled locally (off-topic for this thread) and #2 is that the SRF
record describing mechanics are not abstractable because of using
syntax to describe the record.  Corey's proposal, overloading issues
aside, appears to neatly deal with this problem because anyelement can
be passed down through a wrapping API.

IOW, I'd like to do:
CREATE FUNCTION remote_call(...) RETURNS ... AS
$$
  SELECT dblink(...) AS r(...)
$$ language sql;

...which can't be done (discounting dynamic sql acrobatics) because of
the syntax based expression of the 'r' record.  So I like Corey's
idea...I just think the functions need to be named differently (maybe
to 'dblink_any', and dblink_get_result_any'?).   TBH, to do better
than that you'd need SQL level support for handling the return type in
the vein of NEW/OLD.  For fun, let's call it 'OUT'...then you could:

SELECT * FROM remote_call(...) RETURNS SETOF foo;

Inside remote_call, you'd see something like:

SELECT dblink(...) AS OUT;

As to the proposed syntax, I would vote to support the SQL standard
variant if it could be handled during parse.  I don't see what AS TYPE
OF really buys you because FWICT it does not support wrapping.

merlin