diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 5e5f8a7..f8a5f37 100644 *** a/doc/src/sgml/catalogs.sgml --- b/doc/src/sgml/catalogs.sgml *************** *** 2184,2189 **** --- 2184,2288 ---- + + <structname>pg_comments</structname> + + + pg_comments + + + + The pg_comments view displays the comments + associated with all database objects; only objects which have a comment + are displayed. Object comments are manipulated + using the COMMENT ON command. + + + + <structname>pg_comments</> Columns + + + + + Name + Type + References + Description + + + + + + objoid + oid + pg_description.objoid + The OID of the object to which the comment pertains. + + + + classoid + oid + pg_class.oid + + The OID of the system catalog this object appears in + + + + + objsubid + int4 + pg_class.objsubid + + For a comment on a table column, this is the column number + (the objoid and classoid refer to the table itself). For all other + object types, this column is zero. + + + + + objtype + text + + + The type of object to which the comment pertains. + + + + + objnamespace + oid + pg_namespace.oid + + The OID of the namespace that contains this object, if applicable, + otherwise 0 + + + + + objname + text + + + The name of the object + + + + + description + text + pg_description.description or pg_shdescription.description + + The comment attached to the object + + + + + +
+ + + + <structname>pg_conversion</structname> diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index 6385c78..615874d 100644 *** a/doc/src/sgml/ref/psql-ref.sgml --- b/doc/src/sgml/ref/psql-ref.sgml *************** testdb=> *** 982,1005 **** \dd[S] [ pattern ] ! Shows the descriptions of objects matching the pattern, 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 S modifier to include system objects. - Object covers aggregates, functions, operators, - types, relations (tables, views, indexes, sequences), large - objects, rules, and triggers. For example: - - => \dd version - Object descriptions - Schema | Name | Object | Description - ------------+---------+----------+--------------------------- - pg_catalog | version | function | PostgreSQL version string - (1 row) - --- 982,1002 ---- \dd[S] [ pattern ] ! Shows the descriptions of objects of type constraint, ! operator class, operator family, ! rule, and trigger. All ! other comments may be viewed by the respective backslash commands for ! those object types. ! ! ! ! \dd displays descriptions for objects matching the ! pattern, 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 S modifier to include system objects. diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 2253ca8..dd0ce60 100644 *** a/src/backend/catalog/system_views.sql --- b/src/backend/catalog/system_views.sql *************** *** 6,11 **** --- 6,425 ---- * src/backend/catalog/system_views.sql */ + CREATE VIEW pg_comments AS + SELECT + d.objoid, d.classoid, d.objsubid, + CASE WHEN rel.relkind = 'r' THEN 'table'::text + WHEN rel.relkind = 'v' THEN 'view'::text + WHEN rel.relkind = 'i' THEN 'index'::text + WHEN rel.relkind = 'S' THEN 'sequence'::text + WHEN rel.relkind = 'f' THEN 'foreign table'::text END AS objtype, + rel.relnamespace AS objnamespace, + CASE WHEN pg_table_is_visible(rel.oid) + THEN quote_ident(rel.relname) + ELSE quote_ident(nsp.nspname) || '.' || quote_ident(rel.relname) + END AS objname, + d.description + FROM + pg_description d + JOIN pg_class rel ON d.classoid = rel.tableoid AND d.objoid = rel.oid + JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid + WHERE + d.objsubid = 0 + UNION ALL + SELECT + d.objoid, d.classoid, d.objsubid, + 'column'::text AS objtype, + rel.relnamespace AS objnamespace, + CASE WHEN pg_table_is_visible(rel.oid) + THEN quote_ident(rel.relname) + ELSE quote_ident(nsp.nspname) || '.' || quote_ident(rel.relname) + END || '.' || att.attname AS objname, + d.description + FROM + pg_description d + JOIN pg_class rel ON d.classoid = rel.tableoid AND d.objoid = rel.oid + JOIN pg_attribute att + ON rel.oid = att.attrelid AND d.objsubid = att.attnum + JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid + WHERE + d.objsubid != 0 + UNION ALL + SELECT + d.objoid, d.classoid, d.objsubid, + CASE WHEN pro.proisagg = true THEN 'aggregate'::text + WHEN pro.proisagg = false THEN 'function'::text + END AS objtype, + pro.pronamespace AS objnamespace, + CASE WHEN pg_function_is_visible(pro.oid) + THEN quote_ident(pro.proname) + ELSE quote_ident(nsp.nspname) || '.' || quote_ident(pro.proname) + END || '(' + || pg_catalog.pg_get_function_arguments(pro.oid) || ')' AS objname, + d.description + FROM + pg_description d + JOIN pg_proc pro ON d.classoid = pro.tableoid AND d.objoid = pro.oid + JOIN pg_namespace nsp ON pro.pronamespace = nsp.oid + WHERE + d.objsubid = 0 + UNION ALL + SELECT + d.objoid, d.classoid, d.objsubid, + 'cast'::text AS objtype, + NULL::oid AS objnamespace, + '(' || format_type(cst.castsource, NULL) + || ' AS ' || format_type(cst.casttarget, NULL) || ')' AS objname, + d.description + FROM + pg_description d + JOIN pg_cast cst ON d.classoid = cst.tableoid AND d.objoid = cst.oid + WHERE + d.objsubid = 0 + UNION ALL + SELECT + d.objoid, d.classoid, d.objsubid, + 'collation'::text AS objtype, + col.collnamespace AS objnamespace, + col.collname AS objname, + d.description + FROM + pg_description d + JOIN pg_collation col ON d.classoid = col.tableoid AND d.objoid = col.oid + JOIN pg_namespace nsp ON col.collnamespace = nsp.oid + WHERE + d.objsubid = 0 + UNION ALL + SELECT + d.objoid, d.classoid, d.objsubid, + 'constraint'::text AS objtype, + rel.relnamespace AS objnamespace, + con.conname || ' ON ' || + CASE WHEN pg_table_is_visible(rel.oid) + THEN quote_ident(rel.relname) + ELSE quote_ident(nsp.nspname) || '.' || quote_ident(rel.relname) + END AS objname, + d.description + FROM + pg_description d + JOIN pg_constraint con ON d.classoid = con.tableoid AND d.objoid = con.oid + JOIN pg_class rel ON con.conrelid = rel.oid + JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid + WHERE + d.objsubid = 0 + UNION ALL + SELECT + d.objoid, d.classoid, d.objsubid, + 'conversion'::text AS objtype, + con.connamespace AS objnamespace, + CASE WHEN pg_conversion_is_visible(con.oid) + THEN quote_ident(con.conname) + ELSE quote_ident(nsp.nspname) || '.' || quote_ident(con.conname) + END AS objname, + d.description + FROM + pg_description d + JOIN pg_conversion con ON d.classoid = con.tableoid AND d.objoid = con.oid + JOIN pg_namespace nsp ON con.connamespace = nsp.oid + WHERE + d.objsubid = 0 + UNION ALL + SELECT + d.objoid, d.classoid, d.objsubid, + CASE WHEN typ.typtype = 'd' THEN 'domain'::text + ELSE 'type'::text END AS objtype, + typ.typnamespace AS objnamespace, + CASE WHEN pg_type_is_visible(typ.oid) + THEN quote_ident(typ.typname) + ELSE quote_ident(nsp.nspname) || '.' || quote_ident(typ.typname) + END AS objname, + d.description + FROM + pg_description d + JOIN pg_type typ ON d.classoid = typ.tableoid AND d.objoid = typ.oid + JOIN pg_namespace nsp ON typ.typnamespace = nsp.oid + WHERE + d.objsubid = 0 + UNION ALL + SELECT + d.objoid, d.classoid, d.objsubid, + 'extension'::text AS objtype, + ext.extnamespace AS objnamespace, + ext.extname AS objname, + d.description + FROM + pg_description d + JOIN pg_extension ext ON d.classoid = ext.tableoid AND d.objoid = ext.oid + JOIN pg_namespace nsp ON ext.extnamespace = nsp.oid + WHERE + d.objsubid = 0 + UNION ALL + SELECT + d.objoid, d.classoid, d.objsubid, + 'large object'::text AS objtype, + NULL::oid AS objnamespace, + d.objoid::text AS objname, + d.description + FROM + pg_description d + JOIN pg_largeobject_metadata lom ON d.objoid = lom.oid + WHERE + d.classoid = (SELECT oid FROM pg_class WHERE relname = 'pg_largeobject') + AND d.objsubid = 0 + UNION ALL + SELECT + d.objoid, d.classoid, d.objsubid, + 'operator'::text AS objtype, + opr.oprnamespace AS objnamespace, + CASE WHEN pg_operator_is_visible(opr.oid) + THEN opr.oprname + ELSE quote_ident(nsp.nspname) || '.' || opr.oprname + END + || '(' + || CASE WHEN opr.oprkind = 'r' THEN 'NONE' + ELSE format_type(opr.oprleft, NULL) END + || ',' + || CASE WHEN opr.oprkind = 'l' THEN 'NONE' + ELSE format_type(opr.oprright, NULL) END + || ')' AS objname, + d.description + FROM + pg_description d + JOIN pg_operator opr ON d.classoid = opr.tableoid AND d.objoid = opr.oid + JOIN pg_namespace nsp ON opr.oprnamespace = nsp.oid + WHERE + d.objsubid = 0 + UNION ALL + SELECT + d.objoid, d.classoid, d.objsubid, + 'operator class'::text AS objtype, + opc.opcnamespace AS objnamespace, + CASE WHEN pg_opclass_is_visible(opc.oid) + THEN quote_ident(opc.opcname) + ELSE quote_ident(nsp.nspname) || '.' || quote_ident(opc.opcname) + END || ' USING ' || am.amname AS objname, + d.description + FROM + pg_description d + JOIN pg_opclass opc ON d.classoid = opc.tableoid AND d.objoid = opc.oid + JOIN pg_am am ON opc.opcmethod = am.oid + JOIN pg_namespace nsp ON opc.opcnamespace = nsp.oid + WHERE + d.objsubid = 0 + UNION ALL + SELECT + d.objoid, d.classoid, d.objsubid, + 'operator family'::text AS objtype, + opf.opfnamespace AS objnamespace, + CASE WHEN pg_opfamily_is_visible(opf.oid) + THEN quote_ident(opf.opfname) + ELSE quote_ident(nsp.nspname) || '.' || quote_ident(opf.opfname) + END || ' USING ' || am.amname AS objname, + d.description + FROM + pg_description d + JOIN pg_opfamily opf ON d.classoid = opf.tableoid AND d.objoid = opf.oid + JOIN pg_am am ON opf.opfmethod = am.oid + JOIN pg_namespace nsp ON opf.opfnamespace = nsp.oid + WHERE + d.objsubid = 0 + UNION ALL + SELECT + d.objoid, d.classoid, d.objsubid, + 'language'::text AS objtype, + NULL::oid AS objnamespace, + quote_ident(lan.lanname) AS objname, + d.description + FROM + pg_description d + JOIN pg_language lan ON d.classoid = lan.tableoid AND d.objoid = lan.oid + WHERE + d.objsubid = 0 + UNION ALL + SELECT + d.objoid, d.classoid, d.objsubid, + 'rule'::text AS objtype, + rel.relnamespace AS objnamespace, + quote_ident(rew.rulename) || ' ON ' || + CASE WHEN pg_table_is_visible(rel.oid) + THEN quote_ident(rel.relname) + ELSE quote_ident(nsp.nspname) || '.' || quote_ident(rel.relname) + END AS objname, + d.description + FROM + pg_description d + JOIN pg_rewrite rew ON d.classoid = rew.tableoid AND d.objoid = rew.oid + JOIN pg_class rel ON rew.ev_class = rel.oid + JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid + WHERE + d.objsubid = 0 + UNION ALL + SELECT + d.objoid, d.classoid, d.objsubid, + 'schema'::text AS objtype, + nsp.oid AS objnamespace, + quote_ident(nsp.nspname) AS objname, + d.description + FROM + pg_description d + JOIN pg_namespace nsp ON d.classoid = nsp.tableoid AND d.objoid = nsp.oid + WHERE + d.objsubid = 0 + UNION ALL + SELECT + d.objoid, d.classoid, d.objsubid, + 'text search configuration'::text AS objtype, + cfg.cfgnamespace AS objnamespace, + CASE WHEN pg_ts_config_is_visible(cfg.oid) + THEN quote_ident(cfg.cfgname) + ELSE quote_ident(nsp.nspname) || '.' || quote_ident(cfg.cfgname) + END AS objname, + d.description + FROM + pg_description d + JOIN pg_ts_config cfg ON d.classoid = cfg.tableoid AND d.objoid = cfg.oid + JOIN pg_namespace nsp ON cfg.cfgnamespace = nsp.oid + WHERE + d.objsubid = 0 + UNION ALL + SELECT + d.objoid, d.classoid, d.objsubid, + 'text search dictionary'::text AS objtype, + dict.dictnamespace AS objnamespace, + CASE WHEN pg_ts_dict_is_visible(dict.oid) + THEN quote_ident(dict.dictname) + ELSE quote_ident(nsp.nspname) || '.' || quote_ident(dict.dictname) + END AS objname, + d.description + FROM + pg_description d + JOIN pg_ts_dict dict ON d.classoid = dict.tableoid AND d.objoid = dict.oid + JOIN pg_namespace nsp ON dict.dictnamespace = nsp.oid + WHERE + d.objsubid = 0 + UNION ALL + SELECT + d.objoid, d.classoid, d.objsubid, + 'text search parser'::text AS objtype, + prs.prsnamespace AS objnamespace, + CASE WHEN pg_ts_parser_is_visible(prs.oid) + THEN quote_ident(prs.prsname) + ELSE quote_ident(nsp.nspname) || '.' || quote_ident(prs.prsname) + END AS objname, + d.description + FROM + pg_description d + JOIN pg_ts_parser prs ON d.classoid = prs.tableoid AND d.objoid = prs.oid + JOIN pg_namespace nsp ON prs.prsnamespace = nsp.oid + WHERE + d.objsubid = 0 + UNION ALL + SELECT + d.objoid, d.classoid, d.objsubid, + 'text search template'::text AS objtype, + tmpl.tmplnamespace AS objnamespace, + CASE WHEN pg_ts_template_is_visible(tmpl.oid) + THEN quote_ident(tmpl.tmplname) + ELSE quote_ident(nsp.nspname) || '.' || quote_ident(tmpl.tmplname) + END AS objname, + d.description + FROM + pg_description d + JOIN pg_ts_template tmpl + ON d.classoid = tmpl.tableoid AND d.objoid = tmpl.oid + JOIN pg_namespace nsp ON tmpl.tmplnamespace = nsp.oid + WHERE + d.objsubid = 0 + UNION ALL + SELECT + d.objoid, d.classoid, d.objsubid, + 'trigger'::text AS objtype, + rel.relnamespace AS objnamespace, + quote_ident(tg.tgname) || ' ON ' || + CASE WHEN pg_table_is_visible(rel.oid) + THEN quote_ident(rel.relname) + ELSE quote_ident(nsp.nspname) || '.' || quote_ident(rel.relname) + END AS objname, + d.description + FROM + pg_description d + JOIN pg_trigger tg ON d.classoid = tg.tableoid AND d.objoid = tg.oid + JOIN pg_class rel ON tg.tgrelid = rel.oid + JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid + WHERE + d.objsubid = 0 + UNION ALL + SELECT + d.objoid, d.classoid, d.objsubid, + 'access method'::text AS objtype, + NULL::oid AS objnamespace, + quote_ident(am.amname) AS objname, + d.description + FROM + pg_description d + JOIN pg_am am ON d.classoid = am.tableoid AND d.objoid = am.oid + WHERE + d.objsubid = 0 + UNION ALL + SELECT + d.objoid, d.classoid, 0::integer AS objsubid, + 'database'::text AS objtype, + NULL::oid AS objnamespace, + quote_ident(dat.datname) AS objname, + d.description + FROM + pg_shdescription d + JOIN pg_database dat ON d.classoid = dat.tableoid AND d.objoid = dat.oid + UNION ALL + SELECT + d.objoid, d.classoid, 0::integer AS objsubid, + 'role'::text AS objtype, + NULL::oid AS objnamespace, + quote_ident(rol.rolname) AS objname, + d.description + FROM + pg_shdescription d + JOIN pg_authid rol ON d.classoid = rol.tableoid AND d.objoid = rol.oid + UNION ALL + SELECT + d.objoid, d.classoid, 0::integer AS objsubid, + 'tablespace'::text AS objtype, + NULL::oid AS objnamespace, + quote_ident(spc.spcname) AS objname, + d.description + FROM + pg_shdescription d + JOIN pg_tablespace spc + ON d.classoid = spc.tableoid AND d.objoid = spc.oid + UNION ALL + SELECT + d.objoid, d.classoid, d.objsubid, + 'foreign data wrapper'::text AS objtype, + NULL::oid AS objnamespace, + quote_ident(fdw.fdwname) AS objname, + d.description + FROM + pg_description d + JOIN pg_foreign_data_wrapper fdw + ON d.classoid = fdw.tableoid AND d.objoid = fdw.oid + WHERE + d.objsubid = 0 + UNION ALL + SELECT + d.objoid, d.classoid, d.objsubid, + 'server'::text AS objtype, + NULL::oid AS objnamespace, + quote_ident(srv.srvname) AS objname, + d.description + FROM + pg_description d + JOIN pg_foreign_server srv + ON d.classoid = srv.tableoid AND d.objoid = srv.oid + WHERE + d.objsubid = 0; + + + CREATE VIEW pg_roles AS SELECT rolname, diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index 16ff9e9..ee4c4a2 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..40b5d9e 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 *** 842,848 **** static const bool translate_columns[] = {false, false, true, false}; initPQExpBuffer(&buf); - appendPQExpBuffer(&buf, "SELECT DISTINCT tt.nspname AS \"%s\", tt.name AS \"%s\", tt.object AS \"%s\", d.description AS \"%s\"\n" "FROM (\n", --- 845,850 ---- *************** 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, --- 853,929 ---- 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"); ! /* XXX not sure what to do about visibility rule here? */ ! 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"); + /* XXX not sure what to do about visibility rule here? */ 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"); ! /* XXX not sure what to do about visibility rule here? */ ! 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 *** 1002,1007 **** --- 972,978 ---- ") AS tt\n" " JOIN pg_catalog.pg_description d ON (tt.oid = d.objoid AND tt.tableoid = d.classoid AND d.objsubid = 0)\n"); + appendPQExpBuffer(&buf, "ORDER BY 1, 2, 3;"); res = PSQLexec(buf.data, false); *************** listLanguages(const char *pattern, bool *** 2631,2638 **** gettext_noop("Owner")); appendPQExpBuffer(&buf, ! " l.lanpltrusted AS \"%s\"", ! gettext_noop("Trusted")); if (verbose) { --- 2602,2611 ---- 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,2664 **** } 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;"); res = PSQLexec(buf.data, false); termPQExpBuffer(&buf); --- 2623,2641 ---- } 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"); processSQLNamePattern(pset.db, &buf, pattern, false, false, NULL, "l.lanname", NULL, NULL); + appendPQExpBuffer(&buf, "WHERE (d.objsubid IS NULL OR d.objsubid = 0) "); + if (!showSystem && !pattern) ! appendPQExpBuffer(&buf, "AND lanplcallfoid != 0"); ! appendPQExpBuffer(&buf, "\nORDER BY 1;"); res = PSQLexec(buf.data, false); termPQExpBuffer(&buf); *************** 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" --- 2687,2702 ---- 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\n" ! "WHERE t.typtype = 'd' AND\n" ! "(d.objsubid IS NULL OR d.objsubid = 0)\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" --- 2744,2764 ---- " 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\n" ! "WHERE (d.objsubid = 0 OR d.objsubid IS NULL)\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; --- 2792,2798 ---- * 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" --- 2816,2836 ---- " 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 --- 2840,2853 ---- " 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\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); --- 2865,2876 ---- "pg_catalog.format_type(tt.oid, NULL)", "pg_catalog.pg_type_is_visible(tt.oid)"); ! appendPQExpBuffer(&buf, ") )"); ! ! if (verbose) ! appendPQExpBuffer(&buf, "AND (d.objsubid IS NULL OR d.objsubid = 0)"); ! ! 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 "); --- 3601,3623 ---- 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,3622 **** gettext_noop("Options")); } ! appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_foreign_data_wrapper\n"); ! processSQLNamePattern(pset.db, &buf, pattern, false, false, NULL, "fdwname", NULL, NULL); appendPQExpBuffer(&buf, "ORDER BY 1;"); --- 3627,3643 ---- 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\n"); ! ! appendPQExpBuffer(&buf, "WHERE (d.objsubid IS NULL OR d.objsubid = 0)\n"); ! ! processSQLNamePattern(pset.db, &buf, pattern, true, false, NULL, "fdwname", NULL, NULL); appendPQExpBuffer(&buf, "ORDER BY 1;"); *************** 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) { --- 3680,3691 ---- 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,3688 **** 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); appendPQExpBuffer(&buf, "ORDER BY 1;"); --- 3703,3714 ---- 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\n" ! "WHERE (d.objsubid IS NULL OR d.objsubid = 0)\n"); ! processSQLNamePattern(pset.db, &buf, pattern, true, false, NULL, "s.srvname", NULL, NULL); appendPQExpBuffer(&buf, "ORDER BY 1;"); *************** listForeignTables(const char *pattern, b *** 3778,3800 **** 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); --- 3804,3831 ---- 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\n" ! "WHERE (d.objsubid IS NULL OR d.objsubid = 0)\n"); processSQLNamePattern(pset.db, &buf, pattern, true, false, NULL, "n.nspname", "c.relname", NULL); 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..a4a4142 100644 *** a/src/bin/psql/help.c --- b/src/bin/psql/help.c *************** slashUsage(unsigned short int pager) *** 197,203 **** 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")); --- 197,203 ---- 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 diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 454e1f9..4d95d63 100644 *** a/src/test/regress/expected/rules.out --- b/src/test/regress/expected/rules.out *************** drop table cchild; *** 1276,1286 **** -- Check that ruleutils are working -- SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schema' ORDER BY viewname; ! viewname | definition ! ---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- iexit | SELECT ih.name, ih.thepath, interpt_pp(ih.thepath, r.thepath) AS exit FROM ihighway ih, ramp r WHERE (ih.thepath ## r.thepath); pg_available_extension_versions | SELECT e.name, e.version, (x.extname IS NOT NULL) AS installed, e.superuser, e.relocatable, e.schema, e.requires, e.comment FROM (pg_available_extension_versions() e(name, version, superuser, relocatable, schema, requires, comment) LEFT JOIN pg_extension x ON (((e.name = x.extname) AND (e.version = x.extversion)))); pg_available_extensions | SELECT e.name, e.default_version, x.extversion AS installed_version, e.comment FROM (pg_available_extensions() e(name, default_version, comment) LEFT JOIN pg_extension x ON ((e.name = x.extname))); pg_cursors | SELECT c.name, c.statement, c.is_holdable, c.is_binary, c.is_scrollable, c.creation_time FROM pg_cursor() c(name, statement, is_holdable, is_binary, is_scrollable, creation_time); pg_group | SELECT pg_authid.rolname AS groname, pg_authid.oid AS grosysid, ARRAY(SELECT pg_auth_members.member FROM pg_auth_members WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist FROM pg_authid WHERE (NOT pg_authid.rolcanlogin); pg_indexes | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, t.spcname AS tablespace, pg_get_indexdef(i.oid) AS indexdef FROM ((((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = i.reltablespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind = 'i'::"char")); --- 1276,1287 ---- -- Check that ruleutils are working -- SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schema' ORDER BY viewname; ! viewname | definition ! ---------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- iexit | SELECT ih.name, ih.thepath, interpt_pp(ih.thepath, r.thepath) AS exit FROM ihighway ih, ramp r WHERE (ih.thepath ## r.thepath); pg_available_extension_versions | SELECT e.name, e.version, (x.extname IS NOT NULL) AS installed, e.superuser, e.relocatable, e.schema, e.requires, e.comment FROM (pg_available_extension_versions() e(name, version, superuser, relocatable, schema, requires, comment) LEFT JOIN pg_extension x ON (((e.name = x.extname) AND (e.version = x.extversion)))); pg_available_extensions | SELECT e.name, e.default_version, x.extversion AS installed_version, e.comment FROM (pg_available_extensions() e(name, default_version, comment) LEFT JOIN pg_extension x ON ((e.name = x.extname))); + pg_comments | (((((((((((((((((((((((((SELECT d.objoid, d.classoid, d.objsubid, CASE WHEN (rel.relkind = 'r'::"char") THEN 'table'::text WHEN (rel.relkind = 'v'::"char") THEN 'view'::text WHEN (rel.relkind = 'i'::"char") THEN 'index'::text WHEN (rel.relkind = 'S'::"char") THEN 'sequence'::text WHEN (rel.relkind = 'f'::"char") THEN 'foreign table'::text ELSE NULL::text END AS objtype, rel.relnamespace AS objnamespace, CASE WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text)) END AS objname, d.description FROM ((pg_description d JOIN pg_class rel ON (((d.classoid = rel.tableoid) AND (d.objoid = rel.oid)))) JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid))) WHERE (d.objsubid = 0) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'column'::text AS objtype, rel.relnamespace AS objnamespace, ((CASE WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text)) END || '.'::text) || (att.attname)::text) AS objname, d.description FROM (((pg_description d JOIN pg_class rel ON (((d.classoid = rel.tableoid) AND (d.objoid = rel.oid)))) JOIN pg_attribute att ON (((rel.oid = att.attrelid) AND (d.objsubid = att.attnum)))) JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid))) WHERE (d.objsubid <> 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, CASE WHEN (pro.proisagg = true) THEN 'aggregate'::text WHEN (pro.proisagg = false) THEN 'function'::text ELSE NULL::text END AS objtype, pro.pronamespace AS objnamespace, (((CASE WHEN pg_function_is_visible(pro.oid) THEN quote_ident((pro.proname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((pro.proname)::text)) END || '('::text) || pg_get_function_arguments(pro.oid)) || ')'::text) AS objname, d.description FROM ((pg_description d JOIN pg_proc pro ON (((d.classoid = pro.tableoid) AND (d.objoid = pro.oid)))) JOIN pg_namespace nsp ON ((pro.pronamespace = nsp.oid))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'cast'::text AS objtype, NULL::oid AS objnamespace, (((('('::text || format_type(cst.castsource, NULL::integer)) || ' AS '::text) || format_type(cst.casttarget, NULL::integer)) || ')'::text) AS objname, d.description FROM (pg_description d JOIN pg_cast cst ON (((d.classoid = cst.tableoid) AND (d.objoid = cst.oid)))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'collation'::text AS objtype, col.collnamespace AS objnamespace, col.collname AS objname, d.description FROM ((pg_description d JOIN pg_collation col ON (((d.classoid = col.tableoid) AND (d.objoid = col.oid)))) JOIN pg_namespace nsp ON ((col.collnamespace = nsp.oid))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'constraint'::text AS objtype, rel.relnamespace AS objnamespace, (((con.conname)::text || ' ON '::text) || CASE WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text)) END) AS objname, d.description FROM (((pg_description d JOIN pg_constraint con ON (((d.classoid = con.tableoid) AND (d.objoid = con.oid)))) JOIN pg_class rel ON ((con.conrelid = rel.oid))) JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'conversion'::text AS objtype, con.connamespace AS objnamespace, CASE WHEN pg_conversion_is_visible(con.oid) THEN quote_ident((con.conname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((con.conname)::text)) END AS objname, d.description FROM ((pg_description d JOIN pg_conversion con ON (((d.classoid = con.tableoid) AND (d.objoid = con.oid)))) JOIN pg_namespace nsp ON ((con.connamespace = nsp.oid))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, CASE WHEN (typ.typtype = 'd'::"char") THEN 'domain'::text ELSE 'type'::text END AS objtype, typ.typnamespace AS objnamespace, CASE WHEN pg_type_is_visible(typ.oid) THEN quote_ident((typ.typname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((typ.typname)::text)) END AS objname, d.description FROM ((pg_description d JOIN pg_type typ ON (((d.classoid = typ.tableoid) AND (d.objoid = typ.oid)))) JOIN pg_namespace nsp ON ((typ.typnamespace = nsp.oid))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'extension'::text AS objtype, ext.extnamespace AS objnamespace, ext.extname AS objname, d.description FROM ((pg_description d JOIN pg_extension ext ON (((d.classoid = ext.tableoid) AND (d.objoid = ext.oid)))) JOIN pg_namespace nsp ON ((ext.extnamespace = nsp.oid))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'large object'::text AS objtype, NULL::oid AS objnamespace, (d.objoid)::text AS objname, d.description FROM (pg_description d JOIN pg_largeobject_metadata lom ON ((d.objoid = lom.oid))) WHERE ((d.classoid = (SELECT pg_class.oid FROM pg_class WHERE (pg_class.relname = 'pg_largeobject'::name))) AND (d.objsubid = 0))) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'operator'::text AS objtype, opr.oprnamespace AS objnamespace, (((((CASE WHEN pg_operator_is_visible(opr.oid) THEN (opr.oprname)::text ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || (opr.oprname)::text) END || '('::text) || CASE WHEN (opr.oprkind = 'r'::"char") THEN 'NONE'::text ELSE format_type(opr.oprleft, NULL::integer) END) || ','::text) || CASE WHEN (opr.oprkind = 'l'::"char") THEN 'NONE'::text ELSE format_type(opr.oprright, NULL::integer) END) || ')'::text) AS objname, d.description FROM ((pg_description d JOIN pg_operator opr ON (((d.classoid = opr.tableoid) AND (d.objoid = opr.oid)))) JOIN pg_namespace nsp ON ((opr.oprnamespace = nsp.oid))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'operator class'::text AS objtype, opc.opcnamespace AS objnamespace, ((CASE WHEN pg_opclass_is_visible(opc.oid) THEN quote_ident((opc.opcname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((opc.opcname)::text)) END || ' USING '::text) || (am.amname)::text) AS objname, d.description FROM (((pg_description d JOIN pg_opclass opc ON (((d.classoid = opc.tableoid) AND (d.objoid = opc.oid)))) JOIN pg_am am ON ((opc.opcmethod = am.oid))) JOIN pg_namespace nsp ON ((opc.opcnamespace = nsp.oid))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'operator family'::text AS objtype, opf.opfnamespace AS objnamespace, ((CASE WHEN pg_opfamily_is_visible(opf.oid) THEN quote_ident((opf.opfname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((opf.opfname)::text)) END || ' USING '::text) || (am.amname)::text) AS objname, d.description FROM (((pg_description d JOIN pg_opfamily opf ON (((d.classoid = opf.tableoid) AND (d.objoid = opf.oid)))) JOIN pg_am am ON ((opf.opfmethod = am.oid))) JOIN pg_namespace nsp ON ((opf.opfnamespace = nsp.oid))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'language'::text AS objtype, NULL::oid AS objnamespace, quote_ident((lan.lanname)::text) AS objname, d.description FROM (pg_description d JOIN pg_language lan ON (((d.classoid = lan.tableoid) AND (d.objoid = lan.oid)))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'rule'::text AS objtype, rel.relnamespace AS objnamespace, ((quote_ident((rew.rulename)::text) || ' ON '::text) || CASE WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text)) END) AS objname, d.description FROM (((pg_description d JOIN pg_rewrite rew ON (((d.classoid = rew.tableoid) AND (d.objoid = rew.oid)))) JOIN pg_class rel ON ((rew.ev_class = rel.oid))) JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'schema'::text AS objtype, nsp.oid AS objnamespace, quote_ident((nsp.nspname)::text) AS objname, d.description FROM (pg_description d JOIN pg_namespace nsp ON (((d.classoid = nsp.tableoid) AND (d.objoid = nsp.oid)))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'text search configuration'::text AS objtype, cfg.cfgnamespace AS objnamespace, CASE WHEN pg_ts_config_is_visible(cfg.oid) THEN quote_ident((cfg.cfgname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((cfg.cfgname)::text)) END AS objname, d.description FROM ((pg_description d JOIN pg_ts_config cfg ON (((d.classoid = cfg.tableoid) AND (d.objoid = cfg.oid)))) JOIN pg_namespace nsp ON ((cfg.cfgnamespace = nsp.oid))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'text search dictionary'::text AS objtype, dict.dictnamespace AS objnamespace, CASE WHEN pg_ts_dict_is_visible(dict.oid) THEN quote_ident((dict.dictname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((dict.dictname)::text)) END AS objname, d.description FROM ((pg_description d JOIN pg_ts_dict dict ON (((d.classoid = dict.tableoid) AND (d.objoid = dict.oid)))) JOIN pg_namespace nsp ON ((dict.dictnamespace = nsp.oid))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'text search parser'::text AS objtype, prs.prsnamespace AS objnamespace, CASE WHEN pg_ts_parser_is_visible(prs.oid) THEN quote_ident((prs.prsname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((prs.prsname)::text)) END AS objname, d.description FROM ((pg_description d JOIN pg_ts_parser prs ON (((d.classoid = prs.tableoid) AND (d.objoid = prs.oid)))) JOIN pg_namespace nsp ON ((prs.prsnamespace = nsp.oid))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'text search template'::text AS objtype, tmpl.tmplnamespace AS objnamespace, CASE WHEN pg_ts_template_is_visible(tmpl.oid) THEN quote_ident((tmpl.tmplname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((tmpl.tmplname)::text)) END AS objname, d.description FROM ((pg_description d JOIN pg_ts_template tmpl ON (((d.classoid = tmpl.tableoid) AND (d.objoid = tmpl.oid)))) JOIN pg_namespace nsp ON ((tmpl.tmplnamespace = nsp.oid))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'trigger'::text AS objtype, rel.relnamespace AS objnamespace, ((quote_ident((tg.tgname)::text) || ' ON '::text) || CASE WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text)) END) AS objname, d.description FROM (((pg_description d JOIN pg_trigger tg ON (((d.classoid = tg.tableoid) AND (d.objoid = tg.oid)))) JOIN pg_class rel ON ((tg.tgrelid = rel.oid))) JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'access method'::text AS objtype, NULL::oid AS objnamespace, quote_ident((am.amname)::text) AS objname, d.description FROM (pg_description d JOIN pg_am am ON (((d.classoid = am.tableoid) AND (d.objoid = am.oid)))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, 0 AS objsubid, 'database'::text AS objtype, NULL::oid AS objnamespace, quote_ident((dat.datname)::text) AS objname, d.description FROM (pg_shdescription d JOIN pg_database dat ON (((d.classoid = dat.tableoid) AND (d.objoid = dat.oid))))) UNION ALL SELECT d.objoid, d.classoid, 0 AS objsubid, 'role'::text AS objtype, NULL::oid AS objnamespace, quote_ident((rol.rolname)::text) AS objname, d.description FROM (pg_shdescription d JOIN pg_authid rol ON (((d.classoid = rol.tableoid) AND (d.objoid = rol.oid))))) UNION ALL SELECT d.objoid, d.classoid, 0 AS objsubid, 'tablespace'::text AS objtype, NULL::oid AS objnamespace, quote_ident((spc.spcname)::text) AS objname, d.description FROM (pg_shdescription d JOIN pg_tablespace spc ON (((d.classoid = spc.tableoid) AND (d.objoid = spc.oid))))) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'foreign data wrapper'::text AS objtype, NULL::oid AS objnamespace, quote_ident((fdw.fdwname)::text) AS objname, d.description FROM (pg_description d JOIN pg_foreign_data_wrapper fdw ON (((d.classoid = fdw.tableoid) AND (d.objoid = fdw.oid)))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'server'::text AS objtype, NULL::oid AS objnamespace, quote_ident((srv.srvname)::text) AS objname, d.description FROM (pg_description d JOIN pg_foreign_server srv ON (((d.classoid = srv.tableoid) AND (d.objoid = srv.oid)))) WHERE (d.objsubid = 0); pg_cursors | SELECT c.name, c.statement, c.is_holdable, c.is_binary, c.is_scrollable, c.creation_time FROM pg_cursor() c(name, statement, is_holdable, is_binary, is_scrollable, creation_time); pg_group | SELECT pg_authid.rolname AS groname, pg_authid.oid AS grosysid, ARRAY(SELECT pg_auth_members.member FROM pg_auth_members WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist FROM pg_authid WHERE (NOT pg_authid.rolcanlogin); pg_indexes | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, t.spcname AS tablespace, pg_get_indexdef(i.oid) AS indexdef FROM ((((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = i.reltablespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind = 'i'::"char")); *************** SELECT viewname, definition FROM pg_view *** 1338,1344 **** shoelace_obsolete | SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor = shoelace.sl_color)))); street | SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath); toyemp | SELECT emp.name, emp.age, emp.location, (12 * emp.salary) AS annualsal FROM emp; ! (60 rows) SELECT tablename, rulename, definition FROM pg_rules ORDER BY tablename, rulename; --- 1339,1345 ---- shoelace_obsolete | SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor = shoelace.sl_color)))); street | SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath); toyemp | SELECT emp.name, emp.age, emp.location, (12 * emp.salary) AS annualsal FROM emp; ! (61 rows) SELECT tablename, rulename, definition FROM pg_rules ORDER BY tablename, rulename;