psql: show default extension version in \dx output

mhagander · mhagander · commit d696406a9b25 · 2025-03-24T14:25:05.000+01:00
Reviewed-By: Julien Rouhaud <rjuju123@gmail.com> Reviewed-By: Michael Banck <mbanck@gmx.net> Reviewed-By: Yugo Nagata <nagata@sraoss.co.jp> Reviewed-By: Nathan Bossart <nathandbossart@gmail.com> Reviewed-By: Jelte Fennema-Nio <postgres@jeltef.nl> Discussion: https://postgr.es/m/CABUevEyTMyXC6OvCWkj+rPnHrfi8_Rw_+DD_jzgFFNPqgf+Oig@mail.gmail.com
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
@@ -6188,13 +6188,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"));
 
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
@@ -6460,9 +6460,9 @@ List of schemas
 (0 rows)
 
 \dx "no.such.installed.extension"
-     List of installed extensions
- Name | Version | Schema | Description 
-------+---------+--------+-------------
+              List of installed extensions
+ Name | Version | Default version | Schema | Description 
+------+---------+-----------------+--------+-------------
 (0 rows)
 
 \dX "no.such.extended.statistics"
