Subject: Adding extension default version to \dx

From: Magnus Hagander <[email-masked]>
Date: Fri, 10 Jan 2025 13:04:14 +0100

In an effort to make at least a couple of more people realize they have to
run ALTER EXTENSION UPDATE after they've upgraded an extension,  as well as
make it a bit easier to realize when you have to do it, I propose we add
the default version of an extension to \dx in psql. We currently show the
installed version in the schema, but no indications that a newer one might
be installed on the system.

PFA a patch to do this.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ <http://www.hagander.net/>
 Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index d5543fd62b0..319ad15d4de 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -6107,13 +6107,16 @@ listExtensions(const char *pattern)
 	initPQExpBuffer(&buf);
 	printfPQExpBuffer(&buf,
 					  "SELECT e.extname AS \"%s\", "
-					  "e.extversion AS \"%s\", n.nspname AS \"%s\", c.description AS \"%s\"\n"
+					  "e.extversion AS \"%s\", ae.default_version AS \"%s\","
+					  "n.nspname AS \"%s\", c.description AS \"%s\"\n"
 					  "FROM pg_catalog.pg_extension e "
 					  "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace "
 					  "LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid "
+					  "LEFT JOIN pg_catalog.pg_available_extensions() ae(name, default_version, comment)  ON ae.name=e.extname "
 					  "AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass\n",
 					  gettext_noop("Name"),
 					  gettext_noop("Version"),
+					  gettext_noop("Default version"),
 					  gettext_noop("Schema"),
 					  gettext_noop("Description"));