Обсуждение: Error: "remote query result rowtype does not match the specified FROMclause 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 > 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
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 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
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
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