We use "CREATE DATABASE foo TEMPLATE foo_bk" to restore development
databases to a known snapshot (ex: prior to testing DB migrations).
Currently psql only autocompletes "foo_bk" if it's marked as a template
database. It's mildly inconvenient to have to type out the entire database
name (as they're not marked as templates).
The CREATE DATABASE command allows a super user to use any database as the
template, and a non-super user (with CREATEDB privilege) to use any
database of which it's the owner.
The attached patch updates the psql "CREATE DATABASE ... TEMPLATE <tab>"
completion to match what the command actually allows.
Regards,
-- Sehrope Sarkuni
Founder & CEO | JackDB, Inc. | https://www.jackdb.com/
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 5f27120..fc1edb0 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -599,8 +599,13 @@ static const SchemaQuery Query_for_list_of_matviews = {
" OR '\"' || nspname || '\"' ='%s') "
#define Query_for_list_of_template_databases \
-"SELECT pg_catalog.quote_ident(datname) FROM pg_catalog.pg_database "\
-" WHERE substring(pg_catalog.quote_ident(datname),1,%d)='%s' AND datistemplate"
+"SELECT pg_catalog.quote_ident(d.datname) "\
+"FROM pg_catalog.pg_database d "\
+" JOIN pg_catalog.pg_user u ON u.usesysid = d.datdba "\
+"WHERE substring(pg_catalog.quote_ident(datname),1,%d)='%s' "\
+" AND (d.datistemplate "\
+" OR u.usename = USER "\
+" OR (SELECT z.usesuper FROM pg_catalog.pg_user z WHERE z.usename = USER))"
#define Query_for_list_of_databases \
"SELECT pg_catalog.quote_ident(datname) FROM pg_catalog.pg_database "\
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
