jdbc_fdw

pig repo add pgdg -u    # add pgdg repo and update cache

pig install jdbc_fdw;		# install via package name, for the active PG version

pig install jdbc_fdw -v 16;   # install for PG 16
pig install jdbc_fdw -v 15;   # install for PG 15
pig install jdbc_fdw -v 14;   # install for PG 14

CREATE EXTENSION jdbc_fdw;

CREATE EXTENSION jdbc_fdw;

CREATE SERVER jdbc_server FOREIGN DATA WRAPPER jdbc_fdw
  OPTIONS (
    drivername 'org.postgresql.Driver',
    url 'jdbc:postgresql://remotehost:5432/mydb',
    jarfile '/usr/share/java/postgresql.jar',
    maxheapsize '256'
  );

CREATE USER MAPPING FOR CURRENT_USER SERVER jdbc_server
  OPTIONS (username 'dbuser', password 'dbpass');

CREATE FOREIGN TABLE remote_table (
  id integer OPTIONS (key 'true'),
  name text,
  value numeric
)
SERVER jdbc_server
OPTIONS (table_name 'schema.tablename');

SELECT * FROM remote_table WHERE id > 100;

SELECT * FROM jdbc_exec('jdbc_server', 'SELECT id, name FROM remote_schema.remote_table WHERE status = 1')
  AS t(id integer, name text);