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$;
|
|
References
|
References | ||
|---|---|---|