PostgreSQL – Resolving Remote Query Result Rowtype Mismatch Error

dblinkfunctionspostgresqlpostgresql-9.6remote

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

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?

Best Answer

To understand what's going on, first check what is being done on the remote:

SELECT _test1();
 _test1 
────────
 (1,2)

This does return a record, not two integers. When the dblink() call reports

ERROR:  remote query result rowtype does not match the specified FROM clause rowtype

it's because locally you defined the row type as x(a int, b int), but that doesn't match the record on the remote. The reason is that dblink() itself doesn't know about (as it itself has a SETOF record return type), therefore it cannot push this information to the remote. So what you have to do is the following:

SELECT * 
  FROM dblink('local', 'SELECT * FROM _test1() AS x(a int, b int)') AS t(a int, b int);

 a │ b 
───┼───
 1 │ 2

Specifying the record type twice seems superfluous, but if you omitted the local one, it would result in another error:

SELECT * FROM dblink('local', 'SELECT * FROM _test1() AS x(a int, b int)') AS t;
ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM dblink('local', 'SELECT * FROM _test1() AS x(a...
                      ^

Omitting the remote record definition:

SELECT * FROM dblink('local', 'SELECT * FROM _test1() ') AS t(a int, b int);
ERROR:  a column definition list is required for functions returning "record"
CONTEXT:  Error occurred on dblink connection named "local": could not execute query.

(The difference in the error message shows this time the problem is on the remote side.)

One ore thing that is different in my version is SELECT * FROM _test(). See what happens when you call the function in the SELECT list instead of the FROM clause:

SELECT * FROM dblink('local', 'SELECT _test1() AS x(a int, b int)') AS t(a int, b int);
ERROR:  syntax error at or near "("
CONTEXT:  Error occurred on dblink connection named "local": could not execute query.

You can make this nicer if you created the remote function as RETURNS TABLE (or, equivalently, define OUT arguments there):

-- on the remote
CREATE FUNCTION bla() 
    RETURNS TABLE (a int, b int) LANGUAGE SQL AS $$
    SELECT 1, 2
$$;


SELECT * FROM dblink('local', 'SELECT * FROM bla() ') AS t(a int, b int);
 a │ b 
───┼───
 1 │ 2

The record type is designed to be flexible, but it comes at a price. You cannot avoid paying this price with dblink(), though, as it has to be able to accommodate any return type.

Related Question