Dblink postgres - Revision history

Busk at 16:43, 2 April 2023

2023-04-02T16:43:31Z

2023-04-02T16:38:49Z

2023-04-02T16:18:49Z

Created page with "https://dbaclass.com/article/how-to-create-database-link-in-postgres/"

New page

https://dbaclass.com/article/how-to-create-database-link-in-postgres/

← Older revision		Revision as of 16:43, 2 April 2023
Line 9:		Line 9:

	CREATE USER dblinkuser WITH PASSWORD 'dblinkuser';		CREATE USER dblinkuser WITH PASSWORD 'dblinkuser';
		+	```
		+
		+	https://stackoverflow.com/questions/4386118/create-database-using-a-stored-function
		+
		+	```
		+	CREATE OR REPLACE FUNCTION create_unique_db()
		+	RETURNS TEXT AS $$
		+	DECLARE
		+	_db TEXT := (SELECT 'u' \|\| REPLACE((SELECT gen_random_uuid()::TEXT), '-', ''));
		+	BEGIN
		+	IF EXISTS (SELECT 1 FROM pg_database WHERE datname = _db) THEN
		+	RAISE NOTICE 'Database already exists';
		+	ELSE
		+	EXECUTE FORMAT('CREATE ROLE %s',_db);
		+	-- PERFORM dblink_exec('dbname=' \|\| current_database() -- current db
		+	-- , 'CREATE DATABASE ' \|\| quote_ident(dbname));
		+	PERFORM dblink_exec('create database foothee');
		+
		+	-- EXECUTE FORMAT('CREATE DATABASE %s OWNER %s',_db, _db);
		+	END IF;
		+	RETURN _db;
		+	END;
		+	$$ LANGUAGE plpgsql;
	```		```

← Older revision		Revision as of 16:38, 2 April 2023
Line 1:		Line 1:
	https://dbaclass.com/article/how-to-create-database-link-in-postgres/		https://dbaclass.com/article/how-to-create-database-link-in-postgres/
		+
		+
		+	create users in function via dblink
		+	```
		+	create role tester1 with superuser login password 'tester1';
		+	create database tester1 owner tester1;
		+	dblink_connect('hostaddr=127.0.0.1 port=5432 dbname=tester1 user=tester1 password=tester1');
		+
		+	CREATE USER dblinkuser WITH PASSWORD 'dblinkuser';
		+	```