dblink - How to run the pg_relation_filepath-function on the remote PostgreSQL cluster?

Question

I have a PostgreSQL cluster on the server 10.10.10.1 and a test database 'PG114' with the test table 'dbo.emp_t1'.

Working with the cluster I can execute:

SELECT pg_relation_filepath ( 'dbo.emp_t1' );

I am getting the following results:

pg_tblspc/16394/PG_11_201809051/16395/3978415

Now I am working with a PostgreSQL cluster on another server 10.10.10.2 .

I would like to connect to the cluster on the first server 10.10.10.1 (for instance with the use of dblink-extention) and get the same information about the same table or object 'dbo.emp_t1'.

What is the best way to run such a task?

Answer 1

This is a pretty straightforward composition of two different features. The hardest part about it is the need to have nested quote marks, which I handle here by using dollar quoting for one level. I've used my own IP address and table name rather than yours, so that I can verify it works:

select * from dblink(
   'host=192.168.0.14',
    $$SELECT pg_relation_filepath ( 'public.j' )$$
) f(x text);