Can't call pg_dump for all databases in PSQL

Question

please help me solve the following problem: Syntax error EXECUTE 'pg_dump...'

SELECT datname FROM pg_database WHERE datname NOT IN ('postgres', 'template0', 'template1');
CREATE OR REPLACE FUNCTION dump_databases() RETURNS void AS $$
DECLARE
save_path text := 'C:/Users/79209/Desktop/';
db_name text;
dbs CURSOR FOR SELECT datname FROM pg_database WHERE datname NOT IN ('postgres', 'template0', 'template1');
timestamp text := to_char(now(), 'YYYY-MM-DD');
BEGIN
OPEN dbs;
LOOP
FETCH dbs INTO db_name;
EXIT WHEN NOT FOUND;
EXECUTE 'pg_dump -U postgres -Fc -d ' || db_name || ' > ' || save_path || db_name || '_' || timestamp || '.dump';
END LOOP;
CLOSE dbs;
END $$ LANGUAGE plpgsql;
SELECT dump_databases();

Tried the following options - nothing worked...

EXECUTE 'C:/Program Files/PostgreSQL/15/bin/pg_dump -U postgres -Fc -d ' || "db_name" || ' > ' || save_path || "db_name" || '_' || timestamp || '.dump';

EXECUTE 'pg_dump -U postgres -Fc -d ' || quote_ident(db_name) || ' > ' || quote_literal(save_path || db_name || '_' || timestamp || '.dump');

EXECUTE format('pg_dump -U postgres -Fc -d ' || quote_ident(db_name) || ' > ' || quote_literal(save_path || db_name || '_' || timestamp || '.dump'));

Answer 1

If you execute the lines separately it works, launching the cmd file does not work, it closes the window when entering the loop.

chcp 65001
PAUSE
cd C:\Program Files\PostgreSQL\15\bin
PAUSE
FOR /F "usebackq" %i IN (`psql -U postgres -tAc "SELECT datname FROM pg_database WHERE datname NOT IN ('postgres', 'template0', 'template1')"`) DO pg_dump -U postgres -Fc %i > "C:\Users\79209\Desktop\%i-%DATE%.dump"
PAUSE