Re: Error: "remote query result rowtype does not match the specifiedFROM clause rowtype," on remote function call

On 3/25/19 3:20 PM, Antonio Gomez wrote:
> I figured it out, you have to specify the type twice, once inside the remote call and once outside.
> 
> Like this:
> 
>     SELECT * FROM dblink('conn_str', 'select public._test1() as x(a int ,b int)') as x(a int ,b int);
> 
> Not optimal and not readily discernible, but there it is.

Actually it sort of is, once I started working on a test:

select * from public._test1();
ERROR:  a column definition list is required for functions returning 
"record"
LINE 1: select * from public._test1();

Have you looked at postresql_fdw?:

https://www.postgresql.org/docs/11/postgres-fdw.html

> 
> Thanks for your response!
> 
> --T.
> 
> -----Original Message-----
> From: Adrian Klaver <adrian.klaver@aklaver.com>
> Sent: Monday, March 25, 2019 6:14 PM
> To: Antonio Gomez <AGomez@EBSCO.COM>; pgsql-general@lists.postgresql.org
> Subject: Re: Error: "remote query result rowtype does not match the specified FROM clause rowtype," on remote
functioncall
 
> 
> CAUTION: External E-mail
> 
> 
> On 3/25/19 1:08 PM, Antonio Gomez wrote:
>> This is my *remote* function:
>>
>> CREATEORREPLACE FUNCTIONpublic._test1()
>>
>> RETURNS record
>>
>> LANGUAGE plpgsql
>>
>> AS$function$
>>
>> DECLARE
>>
>> rec record;
>>
>> BEGIN
>>
>> select1,2intorec;
>>
>> returnrec;
>>
>> END$function$;
>>
>> This is my *local* function call:
>>
>> SELECTx.a, x.b
>>
>> FROMdblink('conn_str', 'select public._test1();')
>>
>> asx(a int ,b int);
> 
> Do not have time to spin a test up at the moment, but what happens if you do?"
> 
> SELECT *
> 
> FROM dblink('conn_str', 'select public._test1();')
> 
> as x(a int ,b int);
> 
>>
>> This is the error thrown:
>>
>> ERROR: remote query result rowtype does notmatchthe specified
>> FROMclause rowtype
>>
>> Question:
>>
>> Since DBLINK requires that I define a schema to place the function's
>> return items, how do I make the call to recognize a record type being
>> returned by the function.
>>
>> Thanks!
>>
> 
> 
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com