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

[Antonio Gomez]

This is my remote function:
CREATE OR REPLACE FUNCTION public._test1()
RETURNS record
LANGUAGE plpgsql
AS $function$
DECLARE
rec record;
BEGIN
  select 1,2 into rec;
  return rec;
END $function$;

This is my local function call:
SELECT x.a, x.b
FROM dblink('conn_str', 'select public._test1();')
as x(a int ,b int);

This is the error thrown:
ERROR: remote query result rowtype does not match the specified FROM clause 
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!

RE: Error: "remote query result rowtype does not match the specified FROM clause rowtype," on remote function call

[Antonio Gomez]

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.

Thanks for your response!

--T.

-Original Message-
From: Adrian Klaver  
Sent: Monday, March 25, 2019 6:14 PM
To: Antonio Gomez ; pgsql-general@lists.postgresql.org
Subject: Re: Error: "remote query result rowtype does not match the specified 
FROM clause rowtype," on remote function call

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.kla...@aklaver.com