describe_comments.v3.patch
text/x-patch
Filename: describe_comments.v3.patch
Type: text/x-patch
Part: 0
Message:
Re: psql: display of object comments
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 54fc3ee..c5c4cbd 100644
*** a/doc/src/sgml/ref/psql-ref.sgml
--- b/doc/src/sgml/ref/psql-ref.sgml
*************** testdb=>
*** 966,972 ****
<varlistentry>
! <term><literal>\dC [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists type casts.
--- 966,972 ----
<varlistentry>
! <term><literal>\dC[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists type casts.
*************** testdb=>
*** 982,1005 ****
<term><literal>\dd[S] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
! Shows the descriptions of objects matching the <replaceable
! class="parameter">pattern</replaceable>, or of all visible objects if
! no argument is given. But in either case, only objects that have
! a description are listed.
By default, only user-created objects are shown; supply a
pattern or the <literal>S</literal> modifier to include system
objects.
- <quote>Object</quote> covers aggregates, functions, operators,
- types, relations (tables, views, indexes, sequences), large
- objects, rules, and triggers. For example:
- <programlisting>
- => <userinput>\dd version</userinput>
- Object descriptions
- Schema | Name | Object | Description
- ------------+---------+----------+---------------------------
- pg_catalog | version | function | PostgreSQL version string
- (1 row)
- </programlisting>
</para>
<para>
--- 982,1002 ----
<term><literal>\dd[S] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
! Shows the descriptions of objects of type <literal>constraint</>,
! <literal>operator class</>, <literal>operator family</>,
! <literal>rule</>, and <literal>trigger</>. All
! other comments may be viewed by the respective backslash commands for
! those object types.
! </para>
!
! <para>
! <literal>\dd</literal> displays descriptions for objects matching the
! <replaceable class="parameter">pattern</replaceable>, or of visible
! objects of the appropriate type if no argument is given. But in either
! case, only objects that have a description are listed.
By default, only user-created objects are shown; supply a
pattern or the <literal>S</literal> modifier to include system
objects.
</para>
<para>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index aeabe5b..0b20581 100644
*** a/src/bin/psql/command.c
--- b/src/bin/psql/command.c
*************** exec_command(const char *cmd,
*** 381,387 ****
success = listConversions(pattern, show_system);
break;
case 'C':
! success = listCasts(pattern);
break;
case 'd':
if (strncmp(cmd, "ddp", 3) == 0)
--- 381,387 ----
success = listConversions(pattern, show_system);
break;
case 'C':
! success = listCasts(pattern, show_verbose);
break;
case 'd':
if (strncmp(cmd, "ddp", 3) == 0)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 682cf8a..dd75711 100644
*** a/src/bin/psql/describe.c
--- b/src/bin/psql/describe.c
*************** listDefaultACLs(const char *pattern)
*** 830,837 ****
*
* \dd [foo]
*
! * Note: This only lists things that actually have a description. For complete
! * lists of things, there are other \d? commands.
*/
bool
objectDescription(const char *pattern, bool showSystem)
--- 830,840 ----
*
* \dd [foo]
*
! * Note: This command only lists comments for object types which do not have
! * their comments displayed by their own backslash commands. The following
! * types of objects will be displayed: constraint, operator class,
! * operator family, rule, and trigger.
! *
*/
bool
objectDescription(const char *pattern, bool showSystem)
*************** objectDescription(const char *pattern, b
*** 851,959 ****
gettext_noop("Object"),
gettext_noop("Description"));
! /* Aggregate descriptions */
appendPQExpBuffer(&buf,
! " SELECT p.oid as oid, p.tableoid as tableoid,\n"
" n.nspname as nspname,\n"
! " CAST(p.proname AS pg_catalog.text) as name,"
" CAST('%s' AS pg_catalog.text) as object\n"
! " FROM pg_catalog.pg_proc p\n"
! " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
! " WHERE p.proisagg\n",
! gettext_noop("aggregate"));
if (!showSystem && !pattern)
! appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
! processSQLNamePattern(pset.db, &buf, pattern, true, false,
! "n.nspname", "p.proname", NULL,
! "pg_catalog.pg_function_is_visible(p.oid)");
! /* Function descriptions */
appendPQExpBuffer(&buf,
"UNION ALL\n"
! " SELECT p.oid as oid, p.tableoid as tableoid,\n"
" n.nspname as nspname,\n"
! " CAST(p.proname AS pg_catalog.text) as name,"
" CAST('%s' AS pg_catalog.text) as object\n"
! " FROM pg_catalog.pg_proc p\n"
! " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
! " WHERE NOT p.proisagg\n",
! gettext_noop("function"));
if (!showSystem && !pattern)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
! "n.nspname", "p.proname", NULL,
! "pg_catalog.pg_function_is_visible(p.oid)");
!
! /* Operator descriptions */
! appendPQExpBuffer(&buf,
! "UNION ALL\n"
! " SELECT o.oid as oid, o.tableoid as tableoid,\n"
! " n.nspname as nspname,\n"
! " CAST(o.oprname AS pg_catalog.text) as name,"
! " CAST('%s' AS pg_catalog.text) as object\n"
! " FROM pg_catalog.pg_operator o\n"
! " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
! gettext_noop("operator"));
!
! if (!showSystem && !pattern)
! appendPQExpBuffer(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
! " AND n.nspname <> 'information_schema'\n");
!
! processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false,
! "n.nspname", "o.oprname", NULL,
! "pg_catalog.pg_operator_is_visible(o.oid)");
!
! /* Type descriptions */
! appendPQExpBuffer(&buf,
! "UNION ALL\n"
! " SELECT t.oid as oid, t.tableoid as tableoid,\n"
! " n.nspname as nspname,\n"
! " pg_catalog.format_type(t.oid, NULL) as name,"
! " CAST('%s' AS pg_catalog.text) as object\n"
! " FROM pg_catalog.pg_type t\n"
! " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n",
! gettext_noop("data type"));
!
! if (!showSystem && !pattern)
! appendPQExpBuffer(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
! " AND n.nspname <> 'information_schema'\n");
!
! processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false,
! "n.nspname", "pg_catalog.format_type(t.oid, NULL)",
! NULL,
! "pg_catalog.pg_type_is_visible(t.oid)");
! /* Relation (tables, views, indexes, sequences) descriptions */
! appendPQExpBuffer(&buf,
! "UNION ALL\n"
! " SELECT c.oid as oid, c.tableoid as tableoid,\n"
! " n.nspname as nspname,\n"
! " CAST(c.relname AS pg_catalog.text) as name,\n"
! " CAST(\n"
! " CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' WHEN 'f' THEN '%s' END"
! " AS pg_catalog.text) as object\n"
! " FROM pg_catalog.pg_class c\n"
! " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
! " WHERE c.relkind IN ('r', 'v', 'i', 'S', 'f')\n",
! gettext_noop("table"),
! gettext_noop("view"),
! gettext_noop("index"),
! gettext_noop("sequence"),
! gettext_noop("foreign table"));
! if (!showSystem && !pattern)
! appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
! " AND n.nspname <> 'information_schema'\n");
! processSQLNamePattern(pset.db, &buf, pattern, true, false,
! "n.nspname", "c.relname", NULL,
! "pg_catalog.pg_table_is_visible(c.oid)");
/* Rule descriptions (ignore rules for views) */
appendPQExpBuffer(&buf,
--- 854,926 ----
gettext_noop("Object"),
gettext_noop("Description"));
! /* Constraint descriptions */
appendPQExpBuffer(&buf,
! " SELECT pgc.oid as oid, pgc.tableoid AS tableoid,\n"
" n.nspname as nspname,\n"
! " CAST(pgc.conname AS pg_catalog.text) as name,"
" CAST('%s' AS pg_catalog.text) as object\n"
! " FROM pg_catalog.pg_constraint pgc\n"
! " JOIN pg_catalog.pg_class c "
! "ON c.oid = pgc.conrelid\n"
! " LEFT JOIN pg_catalog.pg_namespace n "
! " ON n.oid = c.relnamespace\n",
! gettext_noop("constraint"));
if (!showSystem && !pattern)
! appendPQExpBuffer(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
! processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern,
! false, "n.nspname", "pgc.conname", NULL,
! "pg_catalog.pg_table_is_visible(c.oid)");
! /* Operator class descriptions */
appendPQExpBuffer(&buf,
"UNION ALL\n"
! " SELECT o.oid as oid, o.tableoid as tableoid,\n"
" n.nspname as nspname,\n"
! " CAST(o.opcname AS pg_catalog.text) as name,\n"
" CAST('%s' AS pg_catalog.text) as object\n"
! " FROM pg_catalog.pg_opclass o\n"
! " JOIN pg_catalog.pg_am am ON o.opcmethod = am.oid\n"
! " JOIN pg_catalog.pg_namespace n ON "
! "n.oid = o.opcnamespace\n",
! gettext_noop("operator class"));
if (!showSystem && !pattern)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
! "n.nspname", "o.opcname", NULL,
! "pg_catalog.pg_opclass_is_visible(o.oid)");
! /* comment on operator family only available in 8.3+ */
! if (pset.sversion >= 80300)
! {
! /* Operator family descriptions */
! appendPQExpBuffer(&buf,
! "UNION ALL\n"
! " SELECT opf.oid as oid, opf.tableoid as tableoid,\n"
! " n.nspname as nspname,\n"
! " CAST(opf.opfname AS pg_catalog.text) AS name,\n"
! " CAST('%s' AS pg_catalog.text) as object\n"
! " FROM pg_catalog.pg_opfamily opf\n"
! " JOIN pg_catalog.pg_am am "
! "ON opf.opfmethod = am.oid\n"
! " JOIN pg_catalog.pg_namespace n "
! "ON opf.opfnamespace = n.oid\n",
! gettext_noop("operator family"));
! if (!showSystem && !pattern)
! appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
! " AND n.nspname <> 'information_schema'\n");
! processSQLNamePattern(pset.db, &buf, pattern, true, false,
! "n.nspname", "opf.opfname", NULL,
! "pg_catalog.pg_opfamily_is_visible(opf.oid)");
! }
/* Rule descriptions (ignore rules for views) */
appendPQExpBuffer(&buf,
*************** objectDescription(const char *pattern, b
*** 972,978 ****
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
- /* XXX not sure what to do about visibility rule here? */
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "r.rulename", NULL,
"pg_catalog.pg_table_is_visible(c.oid)");
--- 939,944 ----
*************** objectDescription(const char *pattern, b
*** 993,999 ****
appendPQExpBuffer(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
- /* XXX not sure what to do about visibility rule here? */
processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false,
"n.nspname", "t.tgname", NULL,
"pg_catalog.pg_table_is_visible(c.oid)");
--- 959,964 ----
*************** listLanguages(const char *pattern, bool
*** 2631,2638 ****
gettext_noop("Owner"));
appendPQExpBuffer(&buf,
! " l.lanpltrusted AS \"%s\"",
! gettext_noop("Trusted"));
if (verbose)
{
--- 2596,2605 ----
gettext_noop("Owner"));
appendPQExpBuffer(&buf,
! " l.lanpltrusted AS \"%s\",\n"
! " d.description AS \"%s\"",
! gettext_noop("Trusted"),
! gettext_noop("Description"));
if (verbose)
{
*************** listLanguages(const char *pattern, bool
*** 2650,2662 ****
}
appendPQExpBuffer(&buf,
! "\nFROM pg_catalog.pg_language l\n");
! processSQLNamePattern(pset.db, &buf, pattern, false, false,
! NULL, "l.lanname", NULL, NULL);
if (!showSystem && !pattern)
! appendPQExpBuffer(&buf, "WHERE lanplcallfoid != 0\n");
appendPQExpBuffer(&buf, "ORDER BY 1;");
--- 2617,2634 ----
}
appendPQExpBuffer(&buf,
! "\nFROM pg_catalog.pg_language l\n"
! "LEFT JOIN pg_catalog.pg_description d\n"
! " ON d.classoid = l.tableoid AND d.objoid = l.oid\n"
! " AND d.objsubid = 0\n");
! if (pattern)
! processSQLNamePattern(pset.db, &buf, pattern, false, false,
! NULL, "l.lanname", NULL, NULL);
if (!showSystem && !pattern)
! appendPQExpBuffer(&buf, "WHERE l.lanplcallfoid != 0\n");
!
appendPQExpBuffer(&buf, "ORDER BY 1;");
*************** listDomains(const char *pattern, bool sh
*** 2710,2720 ****
appendPQExpBuffer(&buf,
" pg_catalog.array_to_string(ARRAY(\n"
" SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM pg_catalog.pg_constraint r WHERE t.oid = r.contypid\n"
! " ), ' ') as \"%s\"\n"
"FROM pg_catalog.pg_type t\n"
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n"
"WHERE t.typtype = 'd'\n",
! gettext_noop("Check"));
if (!showSystem && !pattern)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
--- 2682,2697 ----
appendPQExpBuffer(&buf,
" pg_catalog.array_to_string(ARRAY(\n"
" SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM pg_catalog.pg_constraint r WHERE t.oid = r.contypid\n"
! " ), ' ') as \"%s\",\n"
! " d.description as \"%s\"\n"
"FROM pg_catalog.pg_type t\n"
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n"
+ " LEFT JOIN pg_catalog.pg_description d "
+ "ON d.classoid = t.tableoid AND d.objoid = t.oid "
+ "AND d.objsubid = 0\n"
"WHERE t.typtype = 'd'\n",
! gettext_noop("Check"),
! gettext_noop("Description"));
if (!showSystem && !pattern)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
*************** listConversions(const char *pattern, boo
*** 2762,2776 ****
" pg_catalog.pg_encoding_to_char(c.conforencoding) AS \"%s\",\n"
" pg_catalog.pg_encoding_to_char(c.contoencoding) AS \"%s\",\n"
" CASE WHEN c.condefault THEN '%s'\n"
! " ELSE '%s' END AS \"%s\"\n"
! "FROM pg_catalog.pg_conversion c, pg_catalog.pg_namespace n\n"
! "WHERE n.oid = c.connamespace\n",
gettext_noop("Schema"),
gettext_noop("Name"),
gettext_noop("Source"),
gettext_noop("Destination"),
gettext_noop("yes"), gettext_noop("no"),
! gettext_noop("Default?"));
if (!showSystem && !pattern)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
--- 2739,2759 ----
" pg_catalog.pg_encoding_to_char(c.conforencoding) AS \"%s\",\n"
" pg_catalog.pg_encoding_to_char(c.contoencoding) AS \"%s\",\n"
" CASE WHEN c.condefault THEN '%s'\n"
! " ELSE '%s' END AS \"%s\",\n"
! " d.description AS \"%s\"\n"
! "FROM pg_catalog.pg_conversion c "
! "JOIN pg_catalog.pg_namespace n "
! "ON n.oid = c.connamespace\n"
! "LEFT JOIN pg_catalog.pg_description d "
! "ON d.classoid = c.tableoid AND d.objoid = c.oid "
! "AND d.objsubid = 0\n",
gettext_noop("Schema"),
gettext_noop("Name"),
gettext_noop("Source"),
gettext_noop("Destination"),
gettext_noop("yes"), gettext_noop("no"),
! gettext_noop("Default?"),
! gettext_noop("Description"));
if (!showSystem && !pattern)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
*************** listConversions(const char *pattern, boo
*** 2804,2810 ****
* Describes casts.
*/
bool
! listCasts(const char *pattern)
{
PQExpBufferData buf;
PGresult *res;
--- 2787,2793 ----
* Describes casts.
*/
bool
! listCasts(const char *pattern, bool verbose)
{
PQExpBufferData buf;
PGresult *res;
*************** listCasts(const char *pattern)
*** 2828,2834 ****
" CASE WHEN c.castcontext = 'e' THEN '%s'\n"
" WHEN c.castcontext = 'a' THEN '%s'\n"
" ELSE '%s'\n"
! " END as \"%s\"\n"
"FROM pg_catalog.pg_cast c LEFT JOIN pg_catalog.pg_proc p\n"
" ON c.castfunc = p.oid\n"
" LEFT JOIN pg_catalog.pg_type ts\n"
--- 2811,2831 ----
" CASE WHEN c.castcontext = 'e' THEN '%s'\n"
" WHEN c.castcontext = 'a' THEN '%s'\n"
" ELSE '%s'\n"
! " END as \"%s\"",
! gettext_noop("Source type"),
! gettext_noop("Target type"),
! gettext_noop("Function"),
! gettext_noop("no"),
! gettext_noop("in assignment"),
! gettext_noop("yes"),
! gettext_noop("Implicit?"));
!
! if (verbose)
! appendPQExpBuffer(&buf,
! ",\n d.description AS \"%s\"\n",
! gettext_noop("Description"));
!
! appendPQExpBuffer(&buf,
"FROM pg_catalog.pg_cast c LEFT JOIN pg_catalog.pg_proc p\n"
" ON c.castfunc = p.oid\n"
" LEFT JOIN pg_catalog.pg_type ts\n"
*************** listCasts(const char *pattern)
*** 2838,2850 ****
" LEFT JOIN pg_catalog.pg_type tt\n"
" ON c.casttarget = tt.oid\n"
" LEFT JOIN pg_catalog.pg_namespace nt\n"
! " ON nt.oid = tt.typnamespace\n"
! "WHERE (true",
! gettext_noop("Source type"),
! gettext_noop("Target type"),
! gettext_noop("Function"),
! gettext_noop("no"), gettext_noop("in assignment"), gettext_noop("yes"),
! gettext_noop("Implicit?"));
/*
* Match name pattern against either internal or external name of either
--- 2835,2849 ----
" LEFT JOIN pg_catalog.pg_type tt\n"
" ON c.casttarget = tt.oid\n"
" LEFT JOIN pg_catalog.pg_namespace nt\n"
! " ON nt.oid = tt.typnamespace\n");
!
! if (verbose)
! appendPQExpBuffer(&buf,
! " LEFT JOIN pg_catalog.pg_description d\n"
! " ON d.classoid = c.tableoid AND d.objoid = "
! "c.oid AND d.objsubid = 0\n");
!
! appendPQExpBuffer(&buf, "WHERE ( (true");
/*
* Match name pattern against either internal or external name of either
*************** listCasts(const char *pattern)
*** 2862,2868 ****
"pg_catalog.format_type(tt.oid, NULL)",
"pg_catalog.pg_type_is_visible(tt.oid)");
! appendPQExpBuffer(&buf, ")\nORDER BY 1, 2;");
res = PSQLexec(buf.data, false);
termPQExpBuffer(&buf);
--- 2861,2867 ----
"pg_catalog.format_type(tt.oid, NULL)",
"pg_catalog.pg_type_is_visible(tt.oid)");
! appendPQExpBuffer(&buf, ") )\nORDER BY 1, 2;");
res = PSQLexec(buf.data, false);
termPQExpBuffer(&buf);
*************** listForeignDataWrappers(const char *patt
*** 3593,3610 ****
initPQExpBuffer(&buf);
printfPQExpBuffer(&buf,
! "SELECT fdwname AS \"%s\",\n"
! " pg_catalog.pg_get_userbyid(fdwowner) AS \"%s\",\n",
gettext_noop("Name"),
gettext_noop("Owner"));
if (pset.sversion >= 90100)
appendPQExpBuffer(&buf,
! " fdwhandler::pg_catalog.regproc AS \"%s\",\n",
gettext_noop("Handler"));
appendPQExpBuffer(&buf,
! " fdwvalidator::pg_catalog.regproc AS \"%s\"",
gettext_noop("Validator"));
if (verbose)
{
appendPQExpBuffer(&buf, ",\n ");
--- 3592,3614 ----
initPQExpBuffer(&buf);
printfPQExpBuffer(&buf,
! "SELECT fdw.fdwname AS \"%s\",\n"
! " pg_catalog.pg_get_userbyid(fdw.fdwowner) AS \"%s\",\n",
gettext_noop("Name"),
gettext_noop("Owner"));
if (pset.sversion >= 90100)
appendPQExpBuffer(&buf,
! " fdw.fdwhandler::pg_catalog.regproc AS \"%s\",\n",
gettext_noop("Handler"));
appendPQExpBuffer(&buf,
! " fdw.fdwvalidator::pg_catalog.regproc AS \"%s\"",
gettext_noop("Validator"));
+ if (pset.sversion >= 90100)
+ appendPQExpBuffer(&buf,
+ ",\nd.description AS \"%s\" ",
+ gettext_noop("Description"));
+
if (verbose)
{
appendPQExpBuffer(&buf, ",\n ");
*************** listForeignDataWrappers(const char *patt
*** 3614,3620 ****
gettext_noop("Options"));
}
! appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_foreign_data_wrapper\n");
processSQLNamePattern(pset.db, &buf, pattern, false, false,
NULL, "fdwname", NULL, NULL);
--- 3618,3630 ----
gettext_noop("Options"));
}
! appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_foreign_data_wrapper fdw\n");
!
! if (pset.sversion >= 90100)
! appendPQExpBuffer(&buf,
! "LEFT JOIN pg_catalog.pg_description d\n"
! "ON d.classoid = fdw.tableoid "
! "AND d.objoid = fdw.oid AND d.objsubid = 0\n");
processSQLNamePattern(pset.db, &buf, pattern, false, false,
NULL, "fdwname", NULL, NULL);
*************** listForeignServers(const char *pattern,
*** 3659,3668 ****
printfPQExpBuffer(&buf,
"SELECT s.srvname AS \"%s\",\n"
" pg_catalog.pg_get_userbyid(s.srvowner) AS \"%s\",\n"
! " f.fdwname AS \"%s\"",
gettext_noop("Name"),
gettext_noop("Owner"),
! gettext_noop("Foreign-data wrapper"));
if (verbose)
{
--- 3669,3680 ----
printfPQExpBuffer(&buf,
"SELECT s.srvname AS \"%s\",\n"
" pg_catalog.pg_get_userbyid(s.srvowner) AS \"%s\",\n"
! " f.fdwname AS \"%s\",\n"
! " d.description AS \"%s\"",
gettext_noop("Name"),
gettext_noop("Owner"),
! gettext_noop("Foreign-data wrapper"),
! gettext_noop("Description"));
if (verbose)
{
*************** listForeignServers(const char *pattern,
*** 3680,3686 ****
appendPQExpBuffer(&buf,
"\nFROM pg_catalog.pg_foreign_server s\n"
! " JOIN pg_catalog.pg_foreign_data_wrapper f ON f.oid=s.srvfdw\n");
processSQLNamePattern(pset.db, &buf, pattern, false, false,
NULL, "s.srvname", NULL, NULL);
--- 3692,3701 ----
appendPQExpBuffer(&buf,
"\nFROM pg_catalog.pg_foreign_server s\n"
! " JOIN pg_catalog.pg_foreign_data_wrapper f ON f.oid=s.srvfdw\n"
! "LEFT JOIN pg_description d "
! "ON d.classoid = s.tableoid AND d.objoid = s.oid "
! "AND d.objsubid = 0\n");
processSQLNamePattern(pset.db, &buf, pattern, false, false,
NULL, "s.srvname", NULL, NULL);
*************** listForeignTables(const char *pattern, b
*** 3778,3802 ****
printfPQExpBuffer(&buf,
"SELECT n.nspname AS \"%s\",\n"
" c.relname AS \"%s\",\n"
! " s.srvname AS \"%s\"",
gettext_noop("Schema"),
gettext_noop("Table"),
! gettext_noop("Server"));
if (verbose)
appendPQExpBuffer(&buf,
",\n ft.ftoptions 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\n");
! appendPQExpBuffer(&buf, "\nWHERE c.oid = ft.ftrelid");
! appendPQExpBuffer(&buf, "\nAND s.oid = ft.ftserver\n");
! appendPQExpBuffer(&buf, "\nAND n.oid = c.relnamespace\n");
! processSQLNamePattern(pset.db, &buf, pattern, true, false,
NULL, "n.nspname", "c.relname", NULL);
appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
--- 3793,3822 ----
printfPQExpBuffer(&buf,
"SELECT n.nspname AS \"%s\",\n"
" c.relname AS \"%s\",\n"
! " s.srvname AS \"%s\",\n"
! " d.description AS \"%s\"\n",
gettext_noop("Schema"),
gettext_noop("Table"),
! gettext_noop("Server"),
! gettext_noop("Description"));
if (verbose)
appendPQExpBuffer(&buf,
",\n ft.ftoptions AS \"%s\"",
gettext_noop("Options"));
! appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_foreign_table ft\n"
! " INNER JOIN pg_catalog.pg_class c"
! " ON c.oid = ft.ftrelid\n"
! " INNER JOIN pg_catalog.pg_namespace n"
! " ON n.oid = c.relnamespace\n"
! " INNER JOIN pg_catalog.pg_foreign_server s"
! " ON s.oid = ft.ftserver\n"
! " LEFT JOIN pg_catalog.pg_description d\n"
! " ON d.classoid = c.tableoid AND d.objoid = c.oid "
! "AND d.objsubid = 0\n");
! processSQLNamePattern(pset.db, &buf, pattern, false, false,
NULL, "n.nspname", "c.relname", NULL);
appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index fb86d1e..7dc9a2c 100644
*** a/src/bin/psql/describe.h
--- b/src/bin/psql/describe.h
*************** extern bool listDomains(const char *patt
*** 67,73 ****
extern bool listConversions(const char *pattern, bool showSystem);
/* \dC */
! extern bool listCasts(const char *pattern);
/* \dO */
extern bool listCollations(const char *pattern, bool verbose, bool showSystem);
--- 67,73 ----
extern bool listConversions(const char *pattern, bool showSystem);
/* \dC */
! extern bool listCasts(const char *pattern, bool verbose);
/* \dO */
extern bool listCollations(const char *pattern, bool verbose, bool showSystem);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index e56ab61..5b294f7 100644
*** a/src/bin/psql/help.c
--- b/src/bin/psql/help.c
*************** slashUsage(unsigned short int pager)
*** 196,203 ****
fprintf(output, _(" \\da[S] [PATTERN] list aggregates\n"));
fprintf(output, _(" \\db[+] [PATTERN] list tablespaces\n"));
fprintf(output, _(" \\dc[S] [PATTERN] list conversions\n"));
! fprintf(output, _(" \\dC [PATTERN] list casts\n"));
! fprintf(output, _(" \\dd[S] [PATTERN] show comments on objects\n"));
fprintf(output, _(" \\ddp [PATTERN] list default privileges\n"));
fprintf(output, _(" \\dD[S] [PATTERN] list domains\n"));
fprintf(output, _(" \\det[+] [PATTERN] list foreign tables\n"));
--- 196,203 ----
fprintf(output, _(" \\da[S] [PATTERN] list aggregates\n"));
fprintf(output, _(" \\db[+] [PATTERN] list tablespaces\n"));
fprintf(output, _(" \\dc[S] [PATTERN] list conversions\n"));
! fprintf(output, _(" \\dC[+] [PATTERN] list casts\n"));
! fprintf(output, _(" \\dd[S] [PATTERN] show object descriptions not displayed elsewhere\n"));
fprintf(output, _(" \\ddp [PATTERN] list default privileges\n"));
fprintf(output, _(" \\dD[S] [PATTERN] list domains\n"));
fprintf(output, _(" \\det[+] [PATTERN] list foreign tables\n"));
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index 2b3eddf..8d1bc16 100644
*** a/src/test/regress/expected/foreign_data.out
--- b/src/test/regress/expected/foreign_data.out
*************** CREATE FOREIGN DATA WRAPPER foo VALIDATO
*** 39,50 ****
ERROR: function bar(text[], oid) does not exist
CREATE FOREIGN DATA WRAPPER foo;
\dew
! List of foreign-data wrappers
! Name | Owner | Handler | Validator
! ------------+-------------------+---------+--------------------------
! dummy | foreign_data_user | - | -
! foo | foreign_data_user | - | -
! postgresql | foreign_data_user | - | postgresql_fdw_validator
(3 rows)
CREATE FOREIGN DATA WRAPPER foo; -- duplicate
--- 39,50 ----
ERROR: function bar(text[], oid) does not exist
CREATE FOREIGN DATA WRAPPER foo;
\dew
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Description
! ------------+-------------------+---------+--------------------------+-------------
! dummy | foreign_data_user | - | - | useless
! foo | foreign_data_user | - | - |
! postgresql | foreign_data_user | - | postgresql_fdw_validator |
(3 rows)
CREATE FOREIGN DATA WRAPPER foo; -- duplicate
*************** ERROR: foreign-data wrapper "foo" alrea
*** 52,63 ****
DROP FOREIGN DATA WRAPPER foo;
CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | Options
! ------------+-------------------+---------+--------------------------+-------------------+-------------
! dummy | foreign_data_user | - | - | |
! foo | foreign_data_user | - | - | | {testing=1}
! postgresql | foreign_data_user | - | postgresql_fdw_validator | |
(3 rows)
DROP FOREIGN DATA WRAPPER foo;
--- 52,63 ----
DROP FOREIGN DATA WRAPPER foo;
CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Description | Access privileges | Options
! ------------+-------------------+---------+--------------------------+-------------+-------------------+-------------
! dummy | foreign_data_user | - | - | useless | |
! foo | foreign_data_user | - | - | | | {testing=1}
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
DROP FOREIGN DATA WRAPPER foo;
*************** CREATE FOREIGN DATA WRAPPER foo OPTIONS
*** 65,76 ****
ERROR: option "testing" provided more than once
CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', another '2');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | Options
! ------------+-------------------+---------+--------------------------+-------------------+-----------------------
! dummy | foreign_data_user | - | - | |
! foo | foreign_data_user | - | - | | {testing=1,another=2}
! postgresql | foreign_data_user | - | postgresql_fdw_validator | |
(3 rows)
DROP FOREIGN DATA WRAPPER foo;
--- 65,76 ----
ERROR: option "testing" provided more than once
CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', another '2');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Description | Access privileges | Options
! ------------+-------------------+---------+--------------------------+-------------+-------------------+-----------------------
! dummy | foreign_data_user | - | - | useless | |
! foo | foreign_data_user | - | - | | | {testing=1,another=2}
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
DROP FOREIGN DATA WRAPPER foo;
*************** HINT: Must be superuser to create a for
*** 81,92 ****
RESET ROLE;
CREATE FOREIGN DATA WRAPPER foo VALIDATOR postgresql_fdw_validator;
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | Options
! ------------+-------------------+---------+--------------------------+-------------------+---------
! dummy | foreign_data_user | - | - | |
! foo | foreign_data_user | - | postgresql_fdw_validator | |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | |
(3 rows)
-- ALTER FOREIGN DATA WRAPPER
--- 81,92 ----
RESET ROLE;
CREATE FOREIGN DATA WRAPPER foo VALIDATOR postgresql_fdw_validator;
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Description | Access privileges | Options
! ------------+-------------------+---------+--------------------------+-------------+-------------------+---------
! dummy | foreign_data_user | - | - | useless | |
! foo | foreign_data_user | - | postgresql_fdw_validator | | |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
-- ALTER FOREIGN DATA WRAPPER
*************** ALTER FOREIGN DATA WRAPPER foo VALIDATOR
*** 98,109 ****
ERROR: function bar(text[], oid) does not exist
ALTER FOREIGN DATA WRAPPER foo NO VALIDATOR;
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | Options
! ------------+-------------------+---------+--------------------------+-------------------+---------
! dummy | foreign_data_user | - | - | |
! foo | foreign_data_user | - | - | |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '1', b '2');
--- 98,109 ----
ERROR: function bar(text[], oid) does not exist
ALTER FOREIGN DATA WRAPPER foo NO VALIDATOR;
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Description | Access privileges | Options
! ------------+-------------------+---------+--------------------------+-------------+-------------------+---------
! dummy | foreign_data_user | - | - | useless | |
! foo | foreign_data_user | - | - | | |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '1', b '2');
*************** ALTER FOREIGN DATA WRAPPER foo OPTIONS (
*** 113,146 ****
ERROR: option "c" not found
ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD x '1', DROP x);
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | Options
! ------------+-------------------+---------+--------------------------+-------------------+-----------
! dummy | foreign_data_user | - | - | |
! foo | foreign_data_user | - | - | | {a=1,b=2}
! postgresql | foreign_data_user | - | postgresql_fdw_validator | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP a, SET b '3', ADD c '4');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | Options
! ------------+-------------------+---------+--------------------------+-------------------+-----------
! dummy | foreign_data_user | - | - | |
! foo | foreign_data_user | - | - | | {b=3,c=4}
! postgresql | foreign_data_user | - | postgresql_fdw_validator | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '2');
ALTER FOREIGN DATA WRAPPER foo OPTIONS (b '4'); -- ERROR
ERROR: option "b" provided more than once
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | Options
! ------------+-------------------+---------+--------------------------+-------------------+---------------
! dummy | foreign_data_user | - | - | |
! foo | foreign_data_user | - | - | | {b=3,c=4,a=2}
! postgresql | foreign_data_user | - | postgresql_fdw_validator | |
(3 rows)
SET ROLE regress_test_role;
--- 113,146 ----
ERROR: option "c" not found
ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD x '1', DROP x);
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Description | Access privileges | Options
! ------------+-------------------+---------+--------------------------+-------------+-------------------+-----------
! dummy | foreign_data_user | - | - | useless | |
! foo | foreign_data_user | - | - | | | {a=1,b=2}
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP a, SET b '3', ADD c '4');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Description | Access privileges | Options
! ------------+-------------------+---------+--------------------------+-------------+-------------------+-----------
! dummy | foreign_data_user | - | - | useless | |
! foo | foreign_data_user | - | - | | | {b=3,c=4}
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '2');
ALTER FOREIGN DATA WRAPPER foo OPTIONS (b '4'); -- ERROR
ERROR: option "b" provided more than once
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Description | Access privileges | Options
! ------------+-------------------+---------+--------------------------+-------------+-------------------+---------------
! dummy | foreign_data_user | - | - | useless | |
! foo | foreign_data_user | - | - | | | {b=3,c=4,a=2}
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
SET ROLE regress_test_role;
*************** HINT: Must be superuser to alter a fore
*** 150,161 ****
SET ROLE regress_test_role_super;
ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD d '5');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | Options
! ------------+-------------------+---------+--------------------------+-------------------+-------------------
! dummy | foreign_data_user | - | - | |
! foo | foreign_data_user | - | - | | {b=3,c=4,a=2,d=5}
! postgresql | foreign_data_user | - | postgresql_fdw_validator | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_test_role; -- ERROR
--- 150,161 ----
SET ROLE regress_test_role_super;
ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD d '5');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Description | Access privileges | Options
! ------------+-------------------+---------+--------------------------+-------------+-------------------+-------------------
! dummy | foreign_data_user | - | - | useless | |
! foo | foreign_data_user | - | - | | | {b=3,c=4,a=2,d=5}
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_test_role; -- ERROR
*************** ERROR: permission denied to alter forei
*** 169,180 ****
HINT: Must be superuser to alter a foreign-data wrapper.
RESET ROLE;
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | Options
! ------------+-------------------------+---------+--------------------------+-------------------+-------------------
! dummy | foreign_data_user | - | - | |
! foo | regress_test_role_super | - | - | | {b=3,c=4,a=2,d=5}
! postgresql | foreign_data_user | - | postgresql_fdw_validator | |
(3 rows)
-- DROP FOREIGN DATA WRAPPER
--- 169,180 ----
HINT: Must be superuser to alter a foreign-data wrapper.
RESET ROLE;
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Description | Access privileges | Options
! ------------+-------------------------+---------+--------------------------+-------------+-------------------+-------------------
! dummy | foreign_data_user | - | - | useless | |
! foo | regress_test_role_super | - | - | | | {b=3,c=4,a=2,d=5}
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
-- DROP FOREIGN DATA WRAPPER
*************** ERROR: foreign-data wrapper "nonexisten
*** 183,194 ****
DROP FOREIGN DATA WRAPPER IF EXISTS nonexistent;
NOTICE: foreign-data wrapper "nonexistent" does not exist, skipping
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | Options
! ------------+-------------------------+---------+--------------------------+-------------------+-------------------
! dummy | foreign_data_user | - | - | |
! foo | regress_test_role_super | - | - | | {b=3,c=4,a=2,d=5}
! postgresql | foreign_data_user | - | postgresql_fdw_validator | |
(3 rows)
DROP ROLE regress_test_role_super; -- ERROR
--- 183,194 ----
DROP FOREIGN DATA WRAPPER IF EXISTS nonexistent;
NOTICE: foreign-data wrapper "nonexistent" does not exist, skipping
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Description | Access privileges | Options
! ------------+-------------------------+---------+--------------------------+-------------+-------------------+-------------------
! dummy | foreign_data_user | - | - | useless | |
! foo | regress_test_role_super | - | - | | | {b=3,c=4,a=2,d=5}
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
DROP ROLE regress_test_role_super; -- ERROR
*************** ALTER ROLE regress_test_role_super SUPER
*** 203,213 ****
DROP FOREIGN DATA WRAPPER foo;
DROP ROLE regress_test_role_super;
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | Options
! ------------+-------------------+---------+--------------------------+-------------------+---------
! dummy | foreign_data_user | - | - | |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | |
(2 rows)
CREATE FOREIGN DATA WRAPPER foo;
--- 203,213 ----
DROP FOREIGN DATA WRAPPER foo;
DROP ROLE regress_test_role_super;
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Description | Access privileges | Options
! ------------+-------------------+---------+--------------------------+-------------+-------------------+---------
! dummy | foreign_data_user | - | - | useless | |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(2 rows)
CREATE FOREIGN DATA WRAPPER foo;
*************** CREATE SERVER s1 FOREIGN DATA WRAPPER fo
*** 215,233 ****
COMMENT ON SERVER s1 IS 'foreign server';
CREATE USER MAPPING FOR current_user SERVER s1;
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | Options
! ------------+-------------------+---------+--------------------------+-------------------+---------
! dummy | foreign_data_user | - | - | |
! foo | foreign_data_user | - | - | |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | |
(3 rows)
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | Options
! ------+-------------------+----------------------+-------------------+------+---------+---------
! s1 | foreign_data_user | foo | | | |
(1 row)
\deu+
--- 215,233 ----
COMMENT ON SERVER s1 IS 'foreign server';
CREATE USER MAPPING FOR current_user SERVER s1;
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Description | Access privileges | Options
! ------------+-------------------+---------+--------------------------+-------------+-------------------+---------
! dummy | foreign_data_user | - | - | useless | |
! foo | foreign_data_user | - | - | | |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Description | Access privileges | Type | Version | Options
! ------+-------------------+----------------------+----------------+-------------------+------+---------+---------
! s1 | foreign_data_user | foo | foreign server | | | |
(1 row)
\deu+
*************** NOTICE: drop cascades to 2 other object
*** 252,268 ****
DETAIL: drop cascades to server s1
drop cascades to user mapping for foreign_data_user
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | Options
! ------------+-------------------+---------+--------------------------+-------------------+---------
! dummy | foreign_data_user | - | - | |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | |
(2 rows)
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | Options
! ------+-------+----------------------+-------------------+------+---------+---------
(0 rows)
\deu+
--- 252,268 ----
DETAIL: drop cascades to server s1
drop cascades to user mapping for foreign_data_user
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Description | Access privileges | Options
! ------------+-------------------+---------+--------------------------+-------------+-------------------+---------
! dummy | foreign_data_user | - | - | useless | |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(2 rows)
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Description | Access privileges | Type | Version | Options
! ------+-------+----------------------+-------------+-------------------+------+---------+---------
(0 rows)
\deu+
*************** ERROR: invalid option "foo"
*** 289,305 ****
HINT: Valid options in this context are: authtype, service, connect_timeout, dbname, host, hostaddr, port, tty, options, requiressl, sslmode, gsslib
CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (host 'localhost', dbname 's8db');
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | Options
! ------+-------------------+----------------------+-------------------+--------+---------+------------------------------
! s1 | foreign_data_user | foo | | | |
! s2 | foreign_data_user | foo | | | | {host=a,dbname=b}
! s3 | foreign_data_user | foo | | oracle | |
! s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b}
! s5 | foreign_data_user | foo | | | 15.0 |
! s6 | foreign_data_user | foo | | | 16.0 | {host=a,dbname=b}
! s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b}
! s8 | foreign_data_user | postgresql | | | | {host=localhost,dbname=s8db}
(8 rows)
SET ROLE regress_test_role;
--- 289,305 ----
HINT: Valid options in this context are: authtype, service, connect_timeout, dbname, host, hostaddr, port, tty, options, requiressl, sslmode, gsslib
CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (host 'localhost', dbname 's8db');
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Description | Access privileges | Type | Version | Options
! ------+-------------------+----------------------+-------------+-------------------+--------+---------+------------------------------
! s1 | foreign_data_user | foo | | | | |
! s2 | foreign_data_user | foo | | | | | {host=a,dbname=b}
! s3 | foreign_data_user | foo | | | oracle | |
! s4 | foreign_data_user | foo | | | oracle | | {host=a,dbname=b}
! s5 | foreign_data_user | foo | | | | 15.0 |
! s6 | foreign_data_user | foo | | | | 16.0 | {host=a,dbname=b}
! s7 | foreign_data_user | foo | | | oracle | 17.0 | {host=a,dbname=b}
! s8 | foreign_data_user | postgresql | | | | | {host=localhost,dbname=s8db}
(8 rows)
SET ROLE regress_test_role;
*************** SET ROLE regress_test_role;
*** 311,328 ****
CREATE SERVER t1 FOREIGN DATA WRAPPER foo;
RESET ROLE;
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | Options
! ------+-------------------+----------------------+-------------------+--------+---------+------------------------------
! s1 | foreign_data_user | foo | | | |
! s2 | foreign_data_user | foo | | | | {host=a,dbname=b}
! s3 | foreign_data_user | foo | | oracle | |
! s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b}
! s5 | foreign_data_user | foo | | | 15.0 |
! s6 | foreign_data_user | foo | | | 16.0 | {host=a,dbname=b}
! s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b}
! s8 | foreign_data_user | postgresql | | | | {host=localhost,dbname=s8db}
! t1 | regress_test_role | foo | | | |
(9 rows)
REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_test_role;
--- 311,328 ----
CREATE SERVER t1 FOREIGN DATA WRAPPER foo;
RESET ROLE;
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Description | Access privileges | Type | Version | Options
! ------+-------------------+----------------------+-------------+-------------------+--------+---------+------------------------------
! s1 | foreign_data_user | foo | | | | |
! s2 | foreign_data_user | foo | | | | | {host=a,dbname=b}
! s3 | foreign_data_user | foo | | | oracle | |
! s4 | foreign_data_user | foo | | | oracle | | {host=a,dbname=b}
! s5 | foreign_data_user | foo | | | | 15.0 |
! s6 | foreign_data_user | foo | | | | 16.0 | {host=a,dbname=b}
! s7 | foreign_data_user | foo | | | oracle | 17.0 | {host=a,dbname=b}
! s8 | foreign_data_user | postgresql | | | | | {host=localhost,dbname=s8db}
! t1 | regress_test_role | foo | | | | |
(9 rows)
REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_test_role;
*************** GRANT regress_test_indirect TO regress_t
*** 335,353 ****
SET ROLE regress_test_role;
CREATE SERVER t2 FOREIGN DATA WRAPPER foo;
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | Options
! ------+-------------------+----------------------+-------------------+--------+---------+------------------------------
! s1 | foreign_data_user | foo | | | |
! s2 | foreign_data_user | foo | | | | {host=a,dbname=b}
! s3 | foreign_data_user | foo | | oracle | |
! s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b}
! s5 | foreign_data_user | foo | | | 15.0 |
! s6 | foreign_data_user | foo | | | 16.0 | {host=a,dbname=b}
! s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b}
! s8 | foreign_data_user | postgresql | | | | {host=localhost,dbname=s8db}
! t1 | regress_test_role | foo | | | |
! t2 | regress_test_role | foo | | | |
(10 rows)
RESET ROLE;
--- 335,353 ----
SET ROLE regress_test_role;
CREATE SERVER t2 FOREIGN DATA WRAPPER foo;
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Description | Access privileges | Type | Version | Options
! ------+-------------------+----------------------+-------------+-------------------+--------+---------+------------------------------
! s1 | foreign_data_user | foo | | | | |
! s2 | foreign_data_user | foo | | | | | {host=a,dbname=b}
! s3 | foreign_data_user | foo | | | oracle | |
! s4 | foreign_data_user | foo | | | oracle | | {host=a,dbname=b}
! s5 | foreign_data_user | foo | | | | 15.0 |
! s6 | foreign_data_user | foo | | | | 16.0 | {host=a,dbname=b}
! s7 | foreign_data_user | foo | | | oracle | 17.0 | {host=a,dbname=b}
! s8 | foreign_data_user | postgresql | | | | | {host=localhost,dbname=s8db}
! t1 | regress_test_role | foo | | | | |
! t2 | regress_test_role | foo | | | | |
(10 rows)
RESET ROLE;
*************** ALTER SERVER s3 OPTIONS (tnsname 'orcl',
*** 365,385 ****
GRANT USAGE ON FOREIGN SERVER s1 TO regress_test_role;
GRANT USAGE ON FOREIGN SERVER s6 TO regress_test_role2 WITH GRANT OPTION;
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | Options
! ------+-------------------+----------------------+-----------------------------------------+--------+---------+------------------------------
! s1 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user +| | 1.0 | {servername=s1}
! | | | regress_test_role=U/foreign_data_user | | |
! s2 | foreign_data_user | foo | | | 1.1 | {host=a,dbname=b}
! s3 | foreign_data_user | foo | | oracle | | {tnsname=orcl,port=1521}
! s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b}
! s5 | foreign_data_user | foo | | | 15.0 |
! s6 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user +| | 16.0 | {host=a,dbname=b}
! | | | regress_test_role2=U*/foreign_data_user | | |
! s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b}
! s8 | foreign_data_user | postgresql | | | | {host=localhost,dbname=s8db}
! t1 | regress_test_role | foo | | | |
! t2 | regress_test_role | foo | | | |
(10 rows)
SET ROLE regress_test_role;
--- 365,385 ----
GRANT USAGE ON FOREIGN SERVER s1 TO regress_test_role;
GRANT USAGE ON FOREIGN SERVER s6 TO regress_test_role2 WITH GRANT OPTION;
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Description | Access privileges | Type | Version | Options
! ------+-------------------+----------------------+-------------+-----------------------------------------+--------+---------+------------------------------
! s1 | foreign_data_user | foo | | foreign_data_user=U/foreign_data_user +| | 1.0 | {servername=s1}
! | | | | regress_test_role=U/foreign_data_user | | |
! s2 | foreign_data_user | foo | | | | 1.1 | {host=a,dbname=b}
! s3 | foreign_data_user | foo | | | oracle | | {tnsname=orcl,port=1521}
! s4 | foreign_data_user | foo | | | oracle | | {host=a,dbname=b}
! s5 | foreign_data_user | foo | | | | 15.0 |
! s6 | foreign_data_user | foo | | foreign_data_user=U/foreign_data_user +| | 16.0 | {host=a,dbname=b}
! | | | | regress_test_role2=U*/foreign_data_user | | |
! s7 | foreign_data_user | foo | | | oracle | 17.0 | {host=a,dbname=b}
! s8 | foreign_data_user | postgresql | | | | | {host=localhost,dbname=s8db}
! t1 | regress_test_role | foo | | | | |
! t2 | regress_test_role | foo | | | | |
(10 rows)
SET ROLE regress_test_role;
*************** ERROR: role "regress_test_indirect" can
*** 416,436 ****
DETAIL: owner of server s1
privileges for foreign-data wrapper foo
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | Options
! ------+-----------------------+----------------------+-----------------------------------------+--------+---------+---------------------------------
! s1 | regress_test_indirect | foo | foreign_data_user=U/foreign_data_user +| | 1.1 | {servername=s1}
! | | | regress_test_role=U/foreign_data_user | | |
! s2 | foreign_data_user | foo | | | 1.1 | {host=a,dbname=b}
! s3 | foreign_data_user | foo | | oracle | | {tnsname=orcl,port=1521}
! s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b}
! s5 | foreign_data_user | foo | | | 15.0 |
! s6 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user +| | 16.0 | {host=a,dbname=b}
! | | | regress_test_role2=U*/foreign_data_user | | |
! s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b}
! s8 | foreign_data_user | postgresql | | | | {dbname=db1,connect_timeout=30}
! t1 | regress_test_role | foo | | | |
! t2 | regress_test_role | foo | | | |
(10 rows)
-- DROP SERVER
--- 416,436 ----
DETAIL: owner of server s1
privileges for foreign-data wrapper foo
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Description | Access privileges | Type | Version | Options
! ------+-----------------------+----------------------+-------------+-----------------------------------------+--------+---------+---------------------------------
! s1 | regress_test_indirect | foo | | foreign_data_user=U/foreign_data_user +| | 1.1 | {servername=s1}
! | | | | regress_test_role=U/foreign_data_user | | |
! s2 | foreign_data_user | foo | | | | 1.1 | {host=a,dbname=b}
! s3 | foreign_data_user | foo | | | oracle | | {tnsname=orcl,port=1521}
! s4 | foreign_data_user | foo | | | oracle | | {host=a,dbname=b}
! s5 | foreign_data_user | foo | | | | 15.0 |
! s6 | foreign_data_user | foo | | foreign_data_user=U/foreign_data_user +| | 16.0 | {host=a,dbname=b}
! | | | | regress_test_role2=U*/foreign_data_user | | |
! s7 | foreign_data_user | foo | | | oracle | 17.0 | {host=a,dbname=b}
! s8 | foreign_data_user | postgresql | | | | | {dbname=db1,connect_timeout=30}
! t1 | regress_test_role | foo | | | | |
! t2 | regress_test_role | foo | | | | |
(10 rows)
-- DROP SERVER
*************** ERROR: server "nonexistent" does not ex
*** 439,457 ****
DROP SERVER IF EXISTS nonexistent;
NOTICE: server "nonexistent" does not exist, skipping
\des
! List of foreign servers
! Name | Owner | Foreign-data wrapper
! ------+-----------------------+----------------------
! s1 | regress_test_indirect | foo
! s2 | foreign_data_user | foo
! s3 | foreign_data_user | foo
! s4 | foreign_data_user | foo
! s5 | foreign_data_user | foo
! s6 | foreign_data_user | foo
! s7 | foreign_data_user | foo
! s8 | foreign_data_user | postgresql
! t1 | regress_test_role | foo
! t2 | regress_test_role | foo
(10 rows)
SET ROLE regress_test_role;
--- 439,457 ----
DROP SERVER IF EXISTS nonexistent;
NOTICE: server "nonexistent" does not exist, skipping
\des
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Description
! ------+-----------------------+----------------------+-------------
! s1 | regress_test_indirect | foo |
! s2 | foreign_data_user | foo |
! s3 | foreign_data_user | foo |
! s4 | foreign_data_user | foo |
! s5 | foreign_data_user | foo |
! s6 | foreign_data_user | foo |
! s7 | foreign_data_user | foo |
! s8 | foreign_data_user | postgresql |
! t1 | regress_test_role | foo |
! t2 | regress_test_role | foo |
(10 rows)
SET ROLE regress_test_role;
*************** ERROR: must be owner of foreign server
*** 460,477 ****
DROP SERVER s1;
RESET ROLE;
\des
! List of foreign servers
! Name | Owner | Foreign-data wrapper
! ------+-------------------+----------------------
! s2 | foreign_data_user | foo
! s3 | foreign_data_user | foo
! s4 | foreign_data_user | foo
! s5 | foreign_data_user | foo
! s6 | foreign_data_user | foo
! s7 | foreign_data_user | foo
! s8 | foreign_data_user | postgresql
! t1 | regress_test_role | foo
! t2 | regress_test_role | foo
(9 rows)
ALTER SERVER s2 OWNER TO regress_test_role;
--- 460,477 ----
DROP SERVER s1;
RESET ROLE;
\des
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Description
! ------+-------------------+----------------------+-------------
! s2 | foreign_data_user | foo |
! s3 | foreign_data_user | foo |
! s4 | foreign_data_user | foo |
! s5 | foreign_data_user | foo |
! s6 | foreign_data_user | foo |
! s7 | foreign_data_user | foo |
! s8 | foreign_data_user | postgresql |
! t1 | regress_test_role | foo |
! t2 | regress_test_role | foo |
(9 rows)
ALTER SERVER s2 OWNER TO regress_test_role;
*************** SET ROLE regress_test_role;
*** 479,495 ****
DROP SERVER s2;
RESET ROLE;
\des
! List of foreign servers
! Name | Owner | Foreign-data wrapper
! ------+-------------------+----------------------
! s3 | foreign_data_user | foo
! s4 | foreign_data_user | foo
! s5 | foreign_data_user | foo
! s6 | foreign_data_user | foo
! s7 | foreign_data_user | foo
! s8 | foreign_data_user | postgresql
! t1 | regress_test_role | foo
! t2 | regress_test_role | foo
(8 rows)
CREATE USER MAPPING FOR current_user SERVER s3;
--- 479,495 ----
DROP SERVER s2;
RESET ROLE;
\des
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Description
! ------+-------------------+----------------------+-------------
! s3 | foreign_data_user | foo |
! s4 | foreign_data_user | foo |
! s5 | foreign_data_user | foo |
! s6 | foreign_data_user | foo |
! s7 | foreign_data_user | foo |
! s8 | foreign_data_user | postgresql |
! t1 | regress_test_role | foo |
! t2 | regress_test_role | foo |
(8 rows)
CREATE USER MAPPING FOR current_user SERVER s3;
*************** HINT: Use DROP ... CASCADE to drop the
*** 507,522 ****
DROP SERVER s3 CASCADE;
NOTICE: drop cascades to user mapping for foreign_data_user
\des
! List of foreign servers
! Name | Owner | Foreign-data wrapper
! ------+-------------------+----------------------
! s4 | foreign_data_user | foo
! s5 | foreign_data_user | foo
! s6 | foreign_data_user | foo
! s7 | foreign_data_user | foo
! s8 | foreign_data_user | postgresql
! t1 | regress_test_role | foo
! t2 | regress_test_role | foo
(7 rows)
\deu
--- 507,522 ----
DROP SERVER s3 CASCADE;
NOTICE: drop cascades to user mapping for foreign_data_user
\des
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Description
! ------+-------------------+----------------------+-------------
! s4 | foreign_data_user | foo |
! s5 | foreign_data_user | foo |
! s6 | foreign_data_user | foo |
! s7 | foreign_data_user | foo |
! s8 | foreign_data_user | postgresql |
! t1 | regress_test_role | foo |
! t2 | regress_test_role | foo |
(7 rows)
\deu
*************** Server: sc
*** 663,672 ****
Has OIDs: no
\det+
! List of foreign tables
! Schema | Table | Server | Options
! --------+-------+--------+----------------------------
! public | ft1 | sc | {"delimiter=,","quote=\""}
(1 row)
CREATE INDEX id_ft1_c2 ON ft1 (c2); -- ERROR
--- 663,672 ----
Has OIDs: no
\det+
! List of foreign tables
! Schema | Table | Server | Description | Options
! --------+-------+--------+-------------+----------------------------
! public | ft1 | sc | ft1 | {"delimiter=,","quote=\""}
(1 row)
CREATE INDEX id_ft1_c2 ON ft1 (c2); -- ERROR