Re: dblink: add polymorphic functions. - Mailing list pgsql-hackers
| From | Corey Huinker |
|---|---|
| Subject | Re: dblink: add polymorphic functions. |
| Date | |
| Msg-id | CADkLM=fxO3vnt+PQZb1GbDmJdXZixjXZ3OTdroFyqNcYDNk8uw@mail.gmail.com Whole thread Raw |
| In response to | Re: dblink: add polymorphic functions. (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: dblink: add polymorphic functions.
Re: dblink: add polymorphic functions. |
| List | pgsql-hackers |
On Wed, Jul 29, 2015 at 10:48 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Corey Huinker <corey.huinker@gmail.com> writes:
> On Wed, Jul 29, 2015 at 3:48 AM, Heikki Linnakangas <hlinnaka@iki.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.
pgsql-hackers by date: