Instantly share code, notes, and snippets.

Embed
What would you like to do?
CREATE OR REPLACE FUNCTION remote_db(_table anyelement)
RETURNS SETOF anyelement
AS $func$
DECLARE
_host text := 'ngrok.com'
_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;
BEGIN
-- 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)',
_dblink_schema,
_server,
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)',
_table_name,
_dblink_schema,
_server,
format('SELECT * FROM %I', _table_name),
_cols_names
);
END;
$func$ LANGUAGE plpgsql;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment