Cacher is the code snippet organizer for pro developers

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;