Error: "remote query result rowtype does not match the specified FROM clause rowtype," on remote function call
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!
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
rowtypeQuestion:
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
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 <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 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 rowtypeQuestion:
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
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 function callCAUTION: 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 rowtypeQuestion:
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
On 3/25/19 3:26 PM, Adrian Klaver wrote:
On 3/25/19 3:20 PM, Antonio Gomez wrote:
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?:
Sorry was not thinking. postgresql_fdw works on tables/views, it would
not help in your situation.
https://www.postgresql.org/docs/11/postgres-fdw.html
Thanks for your response!
--
Adrian Klaver
adrian.klaver@aklaver.com