From 143c07bffcfeae4c2bb34d7a54370e04094284a2 Mon Sep 17 00:00:00 2001 From: Corey Huinker Date: Sun, 10 Aug 2025 19:36:52 -0400 Subject: [PATCH v1 2/2] Use psql vars to eliminate the need for DO blocks. Several statements need to reference the current connection's current database name and current port value. Until now, this has been accomplished by creating dynamic SQL statements inside of a DO block, which isn't as easy to read, and takes away some of the granularity of any error messages that might occur, however unlikely. By capturing the connection-specific settings into psql vars, it becomes possible to write the SQL statements with simple :'varname' interpolations. --- .../postgres_fdw/expected/postgres_fdw.out | 44 ++++++------------ contrib/postgres_fdw/sql/postgres_fdw.sql | 46 ++++++------------- 2 files changed, 29 insertions(+), 61 deletions(-) diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index d3323b04676..6af35d04a4e 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -2,23 +2,16 @@ -- create FDW objects -- =================================================================== CREATE EXTENSION postgres_fdw; +SELECT current_database() AS current_database, + current_setting('port') AS current_port +\gset CREATE SERVER testserver1 FOREIGN DATA WRAPPER postgres_fdw; -DO $d$ - BEGIN - EXECUTE $$CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw - OPTIONS (dbname '$$||current_database()||$$', - port '$$||current_setting('port')||$$' - )$$; - EXECUTE $$CREATE SERVER loopback2 FOREIGN DATA WRAPPER postgres_fdw - OPTIONS (dbname '$$||current_database()||$$', - port '$$||current_setting('port')||$$' - )$$; - EXECUTE $$CREATE SERVER loopback3 FOREIGN DATA WRAPPER postgres_fdw - OPTIONS (dbname '$$||current_database()||$$', - port '$$||current_setting('port')||$$' - )$$; - END; -$d$; +CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw + OPTIONS (dbname :'current_database', port :'current_port'); +CREATE SERVER loopback2 FOREIGN DATA WRAPPER postgres_fdw + OPTIONS (dbname :'current_database', port :'current_port'); +CREATE SERVER loopback3 FOREIGN DATA WRAPPER postgres_fdw + OPTIONS (dbname :'current_database', port :'current_port'); CREATE USER MAPPING FOR public SERVER testserver1 OPTIONS (user 'value', password 'value'); CREATE USER MAPPING FOR CURRENT_USER SERVER loopback; @@ -235,12 +228,7 @@ SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should work ALTER SERVER loopback OPTIONS (SET dbname 'no such database'); SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should fail ERROR: could not connect to server "loopback" -DO $d$ - BEGIN - EXECUTE $$ALTER SERVER loopback - OPTIONS (SET dbname '$$||current_database()||$$')$$; - END; -$d$; +ALTER SERVER loopback OPTIONS (SET dbname :'current_database'); SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should work again c3 | c4 -------+------------------------------ @@ -10643,14 +10631,8 @@ SHOW is_superuser; (1 row) -- This will be OK, we can create the FDW -DO $d$ - BEGIN - EXECUTE $$CREATE SERVER loopback_nopw FOREIGN DATA WRAPPER postgres_fdw - OPTIONS (dbname '$$||current_database()||$$', - port '$$||current_setting('port')||$$' - )$$; - END; -$d$; +CREATE SERVER loopback_nopw FOREIGN DATA WRAPPER postgres_fdw + OPTIONS (dbname :'current_database', port :'current_port'); -- But creation of user mappings for non-superusers should fail CREATE USER MAPPING FOR public SERVER loopback_nopw; CREATE USER MAPPING FOR CURRENT_USER SERVER loopback_nopw; @@ -12724,3 +12706,5 @@ SELECT server_name, -- Clean up \set VERBOSITY default RESET debug_discard_caches; +\unset current_database +\unset current_port diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 2c609e060b7..63bba3982cb 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -4,24 +4,17 @@ CREATE EXTENSION postgres_fdw; -CREATE SERVER testserver1 FOREIGN DATA WRAPPER postgres_fdw; -DO $d$ - BEGIN - EXECUTE $$CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw - OPTIONS (dbname '$$||current_database()||$$', - port '$$||current_setting('port')||$$' - )$$; - EXECUTE $$CREATE SERVER loopback2 FOREIGN DATA WRAPPER postgres_fdw - OPTIONS (dbname '$$||current_database()||$$', - port '$$||current_setting('port')||$$' - )$$; - EXECUTE $$CREATE SERVER loopback3 FOREIGN DATA WRAPPER postgres_fdw - OPTIONS (dbname '$$||current_database()||$$', - port '$$||current_setting('port')||$$' - )$$; - END; -$d$; +SELECT current_database() AS current_database, + current_setting('port') AS current_port +\gset +CREATE SERVER testserver1 FOREIGN DATA WRAPPER postgres_fdw; +CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw + OPTIONS (dbname :'current_database', port :'current_port'); +CREATE SERVER loopback2 FOREIGN DATA WRAPPER postgres_fdw + OPTIONS (dbname :'current_database', port :'current_port'); +CREATE SERVER loopback3 FOREIGN DATA WRAPPER postgres_fdw + OPTIONS (dbname :'current_database', port :'current_port'); CREATE USER MAPPING FOR public SERVER testserver1 OPTIONS (user 'value', password 'value'); CREATE USER MAPPING FOR CURRENT_USER SERVER loopback; @@ -233,12 +226,7 @@ ALTER FOREIGN TABLE ft2 ALTER COLUMN c1 OPTIONS (column_name 'C 1'); SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should work ALTER SERVER loopback OPTIONS (SET dbname 'no such database'); SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should fail -DO $d$ - BEGIN - EXECUTE $$ALTER SERVER loopback - OPTIONS (SET dbname '$$||current_database()||$$')$$; - END; -$d$; +ALTER SERVER loopback OPTIONS (SET dbname :'current_database'); SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should work again -- Test that alteration of user mapping options causes reconnection @@ -3375,14 +3363,8 @@ SET ROLE regress_nosuper; SHOW is_superuser; -- This will be OK, we can create the FDW -DO $d$ - BEGIN - EXECUTE $$CREATE SERVER loopback_nopw FOREIGN DATA WRAPPER postgres_fdw - OPTIONS (dbname '$$||current_database()||$$', - port '$$||current_setting('port')||$$' - )$$; - END; -$d$; +CREATE SERVER loopback_nopw FOREIGN DATA WRAPPER postgres_fdw + OPTIONS (dbname :'current_database', port :'current_port'); -- But creation of user mappings for non-superusers should fail CREATE USER MAPPING FOR public SERVER loopback_nopw; @@ -4435,3 +4417,5 @@ SELECT server_name, -- Clean up \set VERBOSITY default RESET debug_discard_caches; +\unset current_database +\unset current_port -- 2.50.1