Skip to content

Instantly share code, notes, and snippets.

Last active June 6, 2018 12:53
  • Star 2 You must be signed in to star a gist
  • Fork 2 You must be signed in to fork a gist
Star You must be signed in to star a gist
What would you like to do?
CREATE OR REPLACE FUNCTION remote_db(_table anyelement)
RETURNS SETOF anyelement
AS $func$
_host text := ''
_port text := '53813'
_user text := 'postgres'
_password text := 'postgres'
_db_name text := 'backup-28-08'
_server text := format('host=%s port=%s user=%s password=%s dbname=%s', _host, _port, _user, _password, _db_name);
_table_name text := pg_typeof(_table);
_dblink_schema text;
_cols_names text;
_remote_schema_query text;
_information_schema text;
-- Still unclear about what this does, what a schema is etc.
SELECT nspname INTO _dblink_schema
FROM pg_namespace n, pg_extension e
WHERE e.extname = 'dblink' AND e.extnamespace = n.oid;
-- Fetch the remote table information_schema to get column names and types
EXECUTE format(
'SELECT array_to_string(array_agg(remote_cols.column_name || '' '' || remote_cols.udt_name), '', '')
FROM %I.dblink(%L, %L) AS remote_cols (column_name text, udt_name text)',
format('SELECT column_name, udt_name FROM information_schema.columns WHERE table_name = ''%s'' ORDER BY ordinal_position', _table_name)
) INTO _cols2;
-- Execute the query itself and returns it
RETURN QUERY EXECUTE format('SELECT (remote::%I).* FROM %I.dblink(%L, %L) AS remote (%s)',
format('SELECT * FROM %I', _table_name),
$func$ LANGUAGE plpgsql;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment