Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement