PostgreSQL/DBLink

ENABLE_DB_LINK=$(cat <<-DDL CREATE EXTENSION IF NOT EXISTS dblink; CREATE EXTENSION IF NOT EXISTS dblink SCHEMA extensions; SELECT * FROM pg_available_extensions WHERE name = 'dblink'; DDL ) echo "${ENABLE_DB_LINK}" | psql -p 5432 -U ${USER} echo "${ENABLE_DB_LINK}" | psql -p 5432 -U ${USER} academia_data_staging

POC

DBLink POC
CREATE EXTENSION dblink; CREATE EXTENSION dblink SCHEMA extensions; SELECT * FROM pg_available_extensions; SELECT * FROM pg_available_extensions WHERE name = 'dblink';
-- using pgsql block DO $BODY$ DECLARE p_db_host varchar:= host(inet_server_addr()); p_db_port varchar:= inet_server_port(); p_db_user varchar:= CURRENT_USER; p_db_pass varchar:= 'p@$$w0rd'; p_db_name varchar:= 'postgres'; p_db_extn_name varchar; p_db_exec_query varchar; p_db_conn_format varchar:= 'hostaddr=%s port=%s dbname=%s user=%s password=%s'; p_db_conn_string varchar:= format(p_db_conn_format, p_db_host, p_db_port, p_db_name, p_db_user, p_db_pass); BEGIN p_db_exec_query := 'SELECT name FROM pg_available_extensions WHERE name = ''dblink'''; SELECT INTO p_db_extn_name name FROM dblink(p_db_conn_string, p_db_exec_query) AS extensions(name varchar); RAISE NOTICE 'Extension Name: %', p_db_extn_name; END $BODY$;
-- using pgsql query WITH user_info AS ( SELECT 'hostaddr=%s port=%s dbname=%s user=%s password=%s' "db_conn_format", host(inet_server_addr()) "db_host", inet_server_port() "db_port", CURRENT_USER "db_user", 'p@$$w0rd' "db_pass", 'postgres' "db_name" ) , conn_info AS ( SELECT db_conn_format, format(db_conn_format, db_host, db_port, db_name, db_user, db_pass) "db_conn_string", 'SELECT name FROM pg_available_extensions WHERE name = ''dblink''' "db_exec_query" FROM user_info ) SELECT * FROM dblink( (SELECT db_conn_string FROM conn_info), (SELECT db_exec_query FROM conn_info) ) AS extensions(name varchar)

Exec

DBLink Exec
DO $BODY$ DECLARE p_connstr text := 'hostaddr=127.0.0.1 port=5432 dbname=academia'; BEGIN PERFORM dblink_exec(p_connstr, format('UPDATE commission SET type = ''%s'' WHERE type = ''%s''', 'new_type', 'old_type')); END $BODY$;
DO $BODY$ DECLARE p_schema varchar := 'academia'; p_passwd varchar := 'p@$$w0rd'; p_dbname varchar := 'academia_audit_staging'; p_dblink varchar := 'hostaddr=127.0.0.1 port=5432 dbname=postgres'; BEGIN SELECT INTO p_dblink 'hostaddr='|| host(inet_server_addr()) || ' port=' || inet_server_port() || ' dbname=' || current_database(); RAISE NOTICE '%', p_dblink; IF NOT EXISTS (SELECT FROM pg_catalog.pg_database WHERE datname = p_dbname) THEN -- PERFORM dblink_exec(p_dblink, format('CREATE DATABASE %s', p_dbname)); RAISE NOTICE 'CREATE DATABASE %', p_dbname; END IF; IF NOT EXISTS (SELECT FROM pg_catalog.pg_user WHERE usename = p_schema) THEN -- PERFORM format('CREATE USER %s WITH ENCRYPTED PASSWORD ''%s''', p_schema, p_passwd); RAISE NOTICE 'CREATE USER % WITH ENCRYPTED PASSWORD ''%''', p_schema, p_passwd; END IF; -- PERFORM format('GRANT ALL PRIVILEGES ON DATABASE %s TO %s', p_dbname, p_schema); RAISE NOTICE 'GRANT ALL PRIVILEGES ON DATABASE % TO %', p_dbname, p_schema; END $BODY$;