From 148fd0dc6d2987a32ecc4c32e6ba9dbe33c0e5ca Mon Sep 17 00:00:00 2001
From: Shigeru Hanada <hanada@metrosystems.co.jp>
Date: Fri, 3 Jun 2011 15:40:27 +0900
Subject: [PATCH] Fix psql to support \dec command, which shows list of foreign
 table columns.

---
 doc/src/sgml/ref/psql-ref.sgml | 14 +++++++
 src/bin/psql/command.c         |  3 ++
 src/bin/psql/describe.c        | 67 ++++++++++++++++++++++++++++++++++
 src/bin/psql/describe.h        |  3 ++
 src/bin/psql/help.c            |  1 +
 5 files changed, 88 insertions(+)

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 132a7b354b..ff09e88bbc 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1108,6 +1108,20 @@ testdb=&gt;
       </varlistentry>
 
 
+      <varlistentry>
+        <term><literal>\dec[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+        <listitem>
+        <para>
+        Lists foreign table columns (mnemonic: <quote>external columns</quote>).
+        If <replaceable class="parameter">pattern</replaceable> is
+        specified, only entries whose table name or schema name matches
+        the pattern are listed.  If the form <literal>\dec+</literal>
+        is used, generic options are also displayed.
+        </para>
+        </listitem>
+      </varlistentry>
+
+
       <varlistentry>
         <term><literal>\deu[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <listitem>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 378330b96a..bd1ec80bae 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -493,6 +493,9 @@ exec_command(const char *cmd,
 					case 't':
 						success = listForeignTables(pattern, show_verbose);
 						break;
+					case 'c':
+						success = listForeignTableColumns(pattern, show_verbose);
+						break;
 					default:
 						status = PSQL_CMD_UNKNOWN;
 						break;
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 7f9e1fe844..1008c035d6 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3823,6 +3823,73 @@ listForeignTables(const char *pattern, bool verbose)
 	return true;
 }
 
+/*
+ * \dec
+ *
+ * Describes foreign table columns.
+ */
+bool
+listForeignTableColumns(const char *pattern, bool verbose)
+{
+	PQExpBufferData buf;
+	PGresult   *res;
+	printQueryOpt myopt = pset.popt;
+
+	/* FIXME should be modified to 90200 before posting patch */
+	if (pset.sversion < 90100)
+	{
+		fprintf(stderr, _("The server (version %d.%d) does not support foreign table column options.\n"),
+				pset.sversion / 10000, (pset.sversion / 100) % 100);
+		return true;
+	}
+
+	initPQExpBuffer(&buf);
+	printfPQExpBuffer(&buf,
+					  "SELECT n.nspname AS \"%s\",\n"
+					  "  c.relname AS \"%s\",\n"
+					  "  a.attname AS \"%s\","
+					  "  s.srvname AS \"%s\"",
+					  gettext_noop("Schema"),
+					  gettext_noop("Table"),
+					  gettext_noop("Column"),
+					  gettext_noop("Server"));
+
+	if (verbose)
+		appendPQExpBuffer(&buf,
+						  ",\n  a.attfdwoptions AS \"%s\"",
+						  gettext_noop("Options"));
+
+	appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_foreign_table ft,");
+	appendPQExpBuffer(&buf, "\n pg_catalog.pg_class c,");
+	appendPQExpBuffer(&buf, "\n pg_catalog.pg_namespace n,");
+	appendPQExpBuffer(&buf, "\n pg_catalog.pg_foreign_server s,");
+	appendPQExpBuffer(&buf, "\n pg_catalog.pg_attribute a\n");
+	appendPQExpBuffer(&buf, "\nWHERE c.oid = ft.ftrelid");
+	appendPQExpBuffer(&buf, "\nAND s.oid = ft.ftserver\n");
+	appendPQExpBuffer(&buf, "\nAND n.oid = c.relnamespace\n");
+	appendPQExpBuffer(&buf, "\nAND a.attrelid= c.oid\n");
+	appendPQExpBuffer(&buf, "\nAND a.attnum > 0\n");
+
+	processSQLNamePattern(pset.db, &buf, pattern, true, false,
+						  NULL, "n.nspname", "c.relname", NULL);
+
+	appendPQExpBuffer(&buf, "ORDER BY 1, 2, a.attnum;");
+
+	res = PSQLexec(buf.data, false);
+	termPQExpBuffer(&buf);
+	if (!res)
+		return false;
+
+	myopt.nullPrint = NULL;
+	myopt.title = _("List of foreign table columns");
+	myopt.translate_header = true;
+
+	printQuery(res, &myopt, pset.queryFout, pset.logfile);
+
+	PQclear(res);
+	return true;
+}
+
 /*
  * \dx
  *
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index fb86d1e487..b6bc472cee 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -87,6 +87,9 @@ extern bool listUserMappings(const char *pattern, bool verbose);
 /* \det */
 extern bool listForeignTables(const char *pattern, bool verbose);
 
+/* \dec */
+extern bool listForeignTableColumns(const char *pattern, bool verbose);
+
 /* \dL */
 extern bool listLanguages(const char *pattern, bool verbose, bool showSystem);
 
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index ac5edca65d..0c8d011f0f 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -200,6 +200,7 @@ slashUsage(unsigned short int pager)
 	fprintf(output, _("  \\ddp    [PATTERN]      list default privileges\n"));
 	fprintf(output, _("  \\dD[S]  [PATTERN]      list domains\n"));
 	fprintf(output, _("  \\det[+] [PATTERN]      list foreign tables\n"));
+	fprintf(output, _("  \\dec[+] [PATTERN]      list foreign table columns\n"));
 	fprintf(output, _("  \\des[+] [PATTERN]      list foreign servers\n"));
 	fprintf(output, _("  \\deu[+] [PATTERN]      list user mappings\n"));
 	fprintf(output, _("  \\dew[+] [PATTERN]      list foreign-data wrappers\n"));
-- 
2.39.5