*** a/src/backend/utils/adt/ruleutils.c --- b/src/backend/utils/adt/ruleutils.c *************** *** 512,519 **** pg_get_viewdef(PG_FUNCTION_ARGS) { /* By OID */ Oid viewoid = PG_GETARG_OID(0); ! PG_RETURN_TEXT_P(string_to_text(pg_get_viewdef_worker(viewoid, 0, -1))); } --- 512,520 ---- { /* By OID */ Oid viewoid = PG_GETARG_OID(0); + int prettyFlags = PRETTYFLAG_INDENT; ! PG_RETURN_TEXT_P(string_to_text(pg_get_viewdef_worker(viewoid, prettyFlags, -1))); } *************** *** 525,531 **** pg_get_viewdef_ext(PG_FUNCTION_ARGS) bool pretty = PG_GETARG_BOOL(1); int prettyFlags; ! prettyFlags = pretty ? PRETTYFLAG_PAREN | PRETTYFLAG_INDENT : 0; PG_RETURN_TEXT_P(string_to_text(pg_get_viewdef_worker(viewoid, prettyFlags, WRAP_COLUMN_DEFAULT))); } --- 526,532 ---- bool pretty = PG_GETARG_BOOL(1); int prettyFlags; ! prettyFlags = pretty ? PRETTYFLAG_PAREN | PRETTYFLAG_INDENT : PRETTYFLAG_INDENT; PG_RETURN_TEXT_P(string_to_text(pg_get_viewdef_worker(viewoid, prettyFlags, WRAP_COLUMN_DEFAULT))); } *** a/src/test/regress/expected/aggregates.out --- b/src/test/regress/expected/aggregates.out *************** *** 943,951 **** select * from agg_view1; (1 row) select pg_get_viewdef('agg_view1'::regclass); ! pg_get_viewdef ! -------------------------------------------------------------------------------------------------------------------------------------------------------- ! SELECT aggfns(v.a, v.b, v.c) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c); (1 row) create or replace view agg_view1 as --- 943,952 ---- (1 row) select pg_get_viewdef('agg_view1'::regclass); ! pg_get_viewdef ! --------------------------------------------------------------------------------------------------------------------- ! SELECT aggfns(v.a, v.b, v.c) AS aggfns + ! FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c); (1 row) create or replace view agg_view1 as *************** *** 959,967 **** select * from agg_view1; (1 row) select pg_get_viewdef('agg_view1'::regclass); ! pg_get_viewdef ! --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ! SELECT aggfns(DISTINCT v.a, v.b, v.c) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), generate_series(1, 3) i(i); (1 row) create or replace view agg_view1 as --- 960,969 ---- (1 row) select pg_get_viewdef('agg_view1'::regclass); ! pg_get_viewdef ! ------------------------------------------------------------------------------------------------------------------------------------------------- ! SELECT aggfns(DISTINCT v.a, v.b, v.c) AS aggfns + ! FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), generate_series(1, 3) i(i); (1 row) create or replace view agg_view1 as *************** *** 975,983 **** select * from agg_view1; (1 row) select pg_get_viewdef('agg_view1'::regclass); ! pg_get_viewdef ! ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ! SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.b) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), generate_series(1, 3) i(i); (1 row) create or replace view agg_view1 as --- 977,986 ---- (1 row) select pg_get_viewdef('agg_view1'::regclass); ! pg_get_viewdef ! ------------------------------------------------------------------------------------------------------------------------------------------------- ! SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.b) AS aggfns + ! FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), generate_series(1, 3) i(i); (1 row) create or replace view agg_view1 as *************** *** 990,998 **** select * from agg_view1; (1 row) select pg_get_viewdef('agg_view1'::regclass); ! pg_get_viewdef ! --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ! SELECT aggfns(v.a, v.b, v.c ORDER BY (v.b + 1)) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c); (1 row) create or replace view agg_view1 as --- 993,1002 ---- (1 row) select pg_get_viewdef('agg_view1'::regclass); ! pg_get_viewdef ! --------------------------------------------------------------------------------------------------------------------- ! SELECT aggfns(v.a, v.b, v.c ORDER BY (v.b + 1)) AS aggfns + ! FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c); (1 row) create or replace view agg_view1 as *************** *** 1005,1013 **** select * from agg_view1; (1 row) select pg_get_viewdef('agg_view1'::regclass); ! pg_get_viewdef ! --------------------------------------------------------------------------------------------------------------------------------------------------------------------- ! SELECT aggfns(v.a, v.a, v.c ORDER BY v.b) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c); (1 row) create or replace view agg_view1 as --- 1009,1018 ---- (1 row) select pg_get_viewdef('agg_view1'::regclass); ! pg_get_viewdef ! --------------------------------------------------------------------------------------------------------------------- ! SELECT aggfns(v.a, v.a, v.c ORDER BY v.b) AS aggfns + ! FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c); (1 row) create or replace view agg_view1 as *************** *** 1020,1028 **** select * from agg_view1; (1 row) select pg_get_viewdef('agg_view1'::regclass); ! pg_get_viewdef ! ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ! SELECT aggfns(v.a, v.b, v.c ORDER BY v.c USING ~<~ NULLS LAST) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c); (1 row) create or replace view agg_view1 as --- 1025,1034 ---- (1 row) select pg_get_viewdef('agg_view1'::regclass); ! pg_get_viewdef ! --------------------------------------------------------------------------------------------------------------------- ! SELECT aggfns(v.a, v.b, v.c ORDER BY v.c USING ~<~ NULLS LAST) AS aggfns + ! FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c); (1 row) create or replace view agg_view1 as *************** *** 1036,1044 **** select * from agg_view1; (1 row) select pg_get_viewdef('agg_view1'::regclass); ! pg_get_viewdef ! ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ! SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.a, v.c USING ~<~ NULLS LAST, v.b) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), generate_series(1, 2) i(i); (1 row) drop view agg_view1; --- 1042,1051 ---- (1 row) select pg_get_viewdef('agg_view1'::regclass); ! pg_get_viewdef ! ------------------------------------------------------------------------------------------------------------------------------------------------- ! SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.a, v.c USING ~<~ NULLS LAST, v.b) AS aggfns + ! FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), generate_series(1, 2) i(i); (1 row) drop view agg_view1; *** a/src/test/regress/expected/collate.out --- b/src/test/regress/expected/collate.out *************** *** 194,204 **** CREATE VIEW collview2 AS SELECT a, b FROM collate_test1 ORDER BY b COLLATE "C"; CREATE VIEW collview3 AS SELECT a, lower((x || x) COLLATE "POSIX") FROM collate_test10; SELECT table_name, view_definition FROM information_schema.views WHERE table_name LIKE 'collview%' ORDER BY 1; ! table_name | view_definition ! ------------+------------------------------------------------------------------------------------------------------------------------ ! collview1 | SELECT collate_test1.a, collate_test1.b FROM collate_test1 WHERE ((collate_test1.b COLLATE "C") >= 'bbc'::text); ! collview2 | SELECT collate_test1.a, collate_test1.b FROM collate_test1 ORDER BY (collate_test1.b COLLATE "C"); ! collview3 | SELECT collate_test10.a, lower(((collate_test10.x || collate_test10.x) COLLATE "POSIX")) AS lower FROM collate_test10; (3 rows) -- collation propagation in various expression types --- 194,209 ---- CREATE VIEW collview3 AS SELECT a, lower((x || x) COLLATE "POSIX") FROM collate_test10; SELECT table_name, view_definition FROM information_schema.views WHERE table_name LIKE 'collview%' ORDER BY 1; ! table_name | view_definition ! ------------+---------------------------------------------------------------------------------------------------- ! collview1 | SELECT collate_test1.a, collate_test1.b + ! | FROM collate_test1 + ! | WHERE ((collate_test1.b COLLATE "C") >= 'bbc'::text); ! collview2 | SELECT collate_test1.a, collate_test1.b + ! | FROM collate_test1 + ! | ORDER BY (collate_test1.b COLLATE "C"); ! collview3 | SELECT collate_test10.a, lower(((collate_test10.x || collate_test10.x) COLLATE "POSIX")) AS lower+ ! | FROM collate_test10; (3 rows) -- collation propagation in various expression types *** a/src/test/regress/expected/rules.out --- b/src/test/regress/expected/rules.out *************** *** 1277,1344 **** drop table cchild; -- 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")); ! pg_locks | SELECT l.locktype, l.database, l.relation, l.page, l.tuple, l.virtualxid, l.transactionid, l.classid, l.objid, l.objsubid, l.virtualtransaction, l.pid, l.mode, l.granted, l.fastpath FROM pg_lock_status() l(locktype, database, relation, page, tuple, virtualxid, transactionid, classid, objid, objsubid, virtualtransaction, pid, mode, granted, fastpath); ! pg_prepared_statements | SELECT p.name, p.statement, p.prepare_time, p.parameter_types, p.from_sql FROM pg_prepared_statement() p(name, statement, prepare_time, parameter_types, from_sql); ! pg_prepared_xacts | SELECT p.transaction, p.gid, p.prepared, u.rolname AS owner, d.datname AS database FROM ((pg_prepared_xact() p(transaction, gid, prepared, ownerid, dbid) LEFT JOIN pg_authid u ON ((p.ownerid = u.oid))) LEFT JOIN pg_database d ON ((p.dbid = d.oid))); ! pg_roles | SELECT pg_authid.rolname, pg_authid.rolsuper, pg_authid.rolinherit, pg_authid.rolcreaterole, pg_authid.rolcreatedb, pg_authid.rolcatupdate, pg_authid.rolcanlogin, pg_authid.rolreplication, pg_authid.rolconnlimit, '********'::text AS rolpassword, pg_authid.rolvaliduntil, s.setconfig AS rolconfig, pg_authid.oid FROM (pg_authid LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid)))); ! pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) AS definition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (r.rulename <> '_RETURN'::name); ! pg_seclabels | (((((((((SELECT l.objoid, l.classoid, l.objsubid, CASE WHEN (rel.relkind = 'r'::"char") THEN 'table'::text WHEN (rel.relkind = 'v'::"char") THEN 'view'::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, l.provider, l.label FROM ((pg_seclabel l JOIN pg_class rel ON (((l.classoid = rel.tableoid) AND (l.objoid = rel.oid)))) JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid))) WHERE (l.objsubid = 0) UNION ALL SELECT l.objoid, l.classoid, l.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, l.provider, l.label FROM (((pg_seclabel l JOIN pg_class rel ON (((l.classoid = rel.tableoid) AND (l.objoid = rel.oid)))) JOIN pg_attribute att ON (((rel.oid = att.attrelid) AND (l.objsubid = att.attnum)))) JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid))) WHERE (l.objsubid <> 0)) UNION ALL SELECT l.objoid, l.classoid, l.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, l.provider, l.label FROM ((pg_seclabel l JOIN pg_proc pro ON (((l.classoid = pro.tableoid) AND (l.objoid = pro.oid)))) JOIN pg_namespace nsp ON ((pro.pronamespace = nsp.oid))) WHERE (l.objsubid = 0)) UNION ALL SELECT l.objoid, l.classoid, l.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, l.provider, l.label FROM ((pg_seclabel l JOIN pg_type typ ON (((l.classoid = typ.tableoid) AND (l.objoid = typ.oid)))) JOIN pg_namespace nsp ON ((typ.typnamespace = nsp.oid))) WHERE (l.objsubid = 0)) UNION ALL SELECT l.objoid, l.classoid, l.objsubid, 'large object'::text AS objtype, NULL::oid AS objnamespace, (l.objoid)::text AS objname, l.provider, l.label FROM (pg_seclabel l JOIN pg_largeobject_metadata lom ON ((l.objoid = lom.oid))) WHERE ((l.classoid = ('pg_largeobject'::regclass)::oid) AND (l.objsubid = 0))) UNION ALL SELECT l.objoid, l.classoid, l.objsubid, 'language'::text AS objtype, NULL::oid AS objnamespace, quote_ident((lan.lanname)::text) AS objname, l.provider, l.label FROM (pg_seclabel l JOIN pg_language lan ON (((l.classoid = lan.tableoid) AND (l.objoid = lan.oid)))) WHERE (l.objsubid = 0)) UNION ALL SELECT l.objoid, l.classoid, l.objsubid, 'schema'::text AS objtype, nsp.oid AS objnamespace, quote_ident((nsp.nspname)::text) AS objname, l.provider, l.label FROM (pg_seclabel l JOIN pg_namespace nsp ON (((l.classoid = nsp.tableoid) AND (l.objoid = nsp.oid)))) WHERE (l.objsubid = 0)) UNION ALL SELECT l.objoid, l.classoid, l.objsubid, 'event trigger'::text AS objtype, NULL::oid AS objnamespace, quote_ident((evt.evtname)::text) AS objname, l.provider, l.label FROM (pg_seclabel l JOIN pg_event_trigger evt ON (((l.classoid = evt.tableoid) AND (l.objoid = evt.oid)))) WHERE (l.objsubid = 0)) UNION ALL SELECT l.objoid, l.classoid, 0 AS objsubid, 'database'::text AS objtype, NULL::oid AS objnamespace, quote_ident((dat.datname)::text) AS objname, l.provider, l.label FROM (pg_shseclabel l JOIN pg_database dat ON (((l.classoid = dat.tableoid) AND (l.objoid = dat.oid))))) UNION ALL SELECT l.objoid, l.classoid, 0 AS objsubid, 'tablespace'::text AS objtype, NULL::oid AS objnamespace, quote_ident((spc.spcname)::text) AS objname, l.provider, l.label FROM (pg_shseclabel l JOIN pg_tablespace spc ON (((l.classoid = spc.tableoid) AND (l.objoid = spc.oid))))) UNION ALL SELECT l.objoid, l.classoid, 0 AS objsubid, 'role'::text AS objtype, NULL::oid AS objnamespace, quote_ident((rol.rolname)::text) AS objname, l.provider, l.label FROM (pg_shseclabel l JOIN pg_authid rol ON (((l.classoid = rol.tableoid) AND (l.objoid = rol.oid)))); ! pg_settings | SELECT a.name, a.setting, a.unit, a.category, a.short_desc, a.extra_desc, a.context, a.vartype, a.source, a.min_val, a.max_val, a.enumvals, a.boot_val, a.reset_val, a.sourcefile, a.sourceline FROM pg_show_all_settings() a(name, setting, unit, category, short_desc, extra_desc, context, vartype, source, min_val, max_val, enumvals, boot_val, reset_val, sourcefile, sourceline); ! pg_shadow | SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb AS usecreatedb, pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolreplication AS userepl, pg_authid.rolpassword AS passwd, (pg_authid.rolvaliduntil)::abstime AS valuntil, s.setconfig AS useconfig FROM (pg_authid LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid)))) WHERE pg_authid.rolcanlogin; ! pg_stat_activity | SELECT s.datid, d.datname, s.pid, s.usesysid, u.rolname AS usename, s.application_name, s.client_addr, s.client_hostname, s.client_port, s.backend_start, s.xact_start, s.query_start, s.state_change, s.waiting, s.state, s.query FROM pg_database d, pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port), pg_authid u WHERE ((s.datid = d.oid) AND (s.usesysid = u.oid)); ! pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])); ! pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan, ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd, pg_stat_get_live_tuples(c.oid) AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, pg_stat_get_last_analyze_time(c.oid) AS last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze, pg_stat_get_vacuum_count(c.oid) AS vacuum_count, pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count, pg_stat_get_analyze_count(c.oid) AS analyze_count, pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname; ! pg_stat_bgwriter | SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req, pg_stat_get_checkpoint_write_time() AS checkpoint_write_time, pg_stat_get_checkpoint_sync_time() AS checkpoint_sync_time, pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint, pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean, pg_stat_get_buf_written_backend() AS buffers_backend, pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync, pg_stat_get_buf_alloc() AS buffers_alloc, pg_stat_get_bgwriter_stat_reset_time() AS stats_reset; ! pg_stat_database | SELECT d.oid AS datid, d.datname, pg_stat_get_db_numbackends(d.oid) AS numbackends, pg_stat_get_db_xact_commit(d.oid) AS xact_commit, pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, (pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS blks_read, pg_stat_get_db_blocks_hit(d.oid) AS blks_hit, pg_stat_get_db_tuples_returned(d.oid) AS tup_returned, pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched, pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted, pg_stat_get_db_tuples_updated(d.oid) AS tup_updated, pg_stat_get_db_tuples_deleted(d.oid) AS tup_deleted, pg_stat_get_db_conflict_all(d.oid) AS conflicts, pg_stat_get_db_temp_files(d.oid) AS temp_files, pg_stat_get_db_temp_bytes(d.oid) AS temp_bytes, pg_stat_get_db_deadlocks(d.oid) AS deadlocks, pg_stat_get_db_blk_read_time(d.oid) AS blk_read_time, pg_stat_get_db_blk_write_time(d.oid) AS blk_write_time, pg_stat_get_db_stat_reset_time(d.oid) AS stats_reset FROM pg_database d; ! pg_stat_database_conflicts | SELECT d.oid AS datid, d.datname, pg_stat_get_db_conflict_tablespace(d.oid) AS confl_tablespace, pg_stat_get_db_conflict_lock(d.oid) AS confl_lock, pg_stat_get_db_conflict_snapshot(d.oid) AS confl_snapshot, pg_stat_get_db_conflict_bufferpin(d.oid) AS confl_bufferpin, pg_stat_get_db_conflict_startup_deadlock(d.oid) AS confl_deadlock FROM pg_database d; ! pg_stat_replication | SELECT s.pid, s.usesysid, u.rolname AS usename, s.application_name, s.client_addr, s.client_hostname, s.client_port, s.backend_start, w.state, w.sent_location, w.write_location, w.flush_location, w.replay_location, w.sync_priority, w.sync_state FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port), pg_authid u, pg_stat_get_wal_senders() w(pid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state) WHERE ((s.usesysid = u.oid) AND (s.pid = w.pid)); ! pg_stat_sys_indexes | SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch FROM pg_stat_all_indexes WHERE ((pg_stat_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_indexes.schemaname ~ '^pg_toast'::text)); ! pg_stat_sys_tables | SELECT pg_stat_all_tables.relid, pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd, pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_tup_hot_upd, pg_stat_all_tables.n_live_tup, pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze, pg_stat_all_tables.vacuum_count, pg_stat_all_tables.autovacuum_count, pg_stat_all_tables.analyze_count, pg_stat_all_tables.autoanalyze_count FROM pg_stat_all_tables WHERE ((pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_tables.schemaname ~ '^pg_toast'::text)); ! pg_stat_user_functions | SELECT p.oid AS funcid, n.nspname AS schemaname, p.proname AS funcname, pg_stat_get_function_calls(p.oid) AS calls, pg_stat_get_function_total_time(p.oid) AS total_time, pg_stat_get_function_self_time(p.oid) AS self_time FROM (pg_proc p LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace))) WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_function_calls(p.oid) IS NOT NULL)); ! pg_stat_user_indexes | SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch FROM pg_stat_all_indexes WHERE ((pg_stat_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_indexes.schemaname !~ '^pg_toast'::text)); ! pg_stat_user_tables | SELECT pg_stat_all_tables.relid, pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd, pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_tup_hot_upd, pg_stat_all_tables.n_live_tup, pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze, pg_stat_all_tables.vacuum_count, pg_stat_all_tables.autovacuum_count, pg_stat_all_tables.analyze_count, pg_stat_all_tables.autoanalyze_count FROM pg_stat_all_tables WHERE ((pg_stat_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_tables.schemaname !~ '^pg_toast'::text)); ! pg_stat_xact_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_xact_numscans(c.oid) AS seq_scan, pg_stat_get_xact_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_xact_numscans(i.indexrelid)))::bigint AS idx_scan, ((sum(pg_stat_get_xact_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_xact_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_xact_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_xact_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_xact_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_xact_tuples_hot_updated(c.oid) AS n_tup_hot_upd FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname; ! pg_stat_xact_sys_tables | SELECT pg_stat_xact_all_tables.relid, pg_stat_xact_all_tables.schemaname, pg_stat_xact_all_tables.relname, pg_stat_xact_all_tables.seq_scan, pg_stat_xact_all_tables.seq_tup_read, pg_stat_xact_all_tables.idx_scan, pg_stat_xact_all_tables.idx_tup_fetch, pg_stat_xact_all_tables.n_tup_ins, pg_stat_xact_all_tables.n_tup_upd, pg_stat_xact_all_tables.n_tup_del, pg_stat_xact_all_tables.n_tup_hot_upd FROM pg_stat_xact_all_tables WHERE ((pg_stat_xact_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_xact_all_tables.schemaname ~ '^pg_toast'::text)); ! pg_stat_xact_user_functions | SELECT p.oid AS funcid, n.nspname AS schemaname, p.proname AS funcname, pg_stat_get_xact_function_calls(p.oid) AS calls, pg_stat_get_xact_function_total_time(p.oid) AS total_time, pg_stat_get_xact_function_self_time(p.oid) AS self_time FROM (pg_proc p LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace))) WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_xact_function_calls(p.oid) IS NOT NULL)); ! pg_stat_xact_user_tables | SELECT pg_stat_xact_all_tables.relid, pg_stat_xact_all_tables.schemaname, pg_stat_xact_all_tables.relname, pg_stat_xact_all_tables.seq_scan, pg_stat_xact_all_tables.seq_tup_read, pg_stat_xact_all_tables.idx_scan, pg_stat_xact_all_tables.idx_tup_fetch, pg_stat_xact_all_tables.n_tup_ins, pg_stat_xact_all_tables.n_tup_upd, pg_stat_xact_all_tables.n_tup_del, pg_stat_xact_all_tables.n_tup_hot_upd FROM pg_stat_xact_all_tables WHERE ((pg_stat_xact_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_xact_all_tables.schemaname !~ '^pg_toast'::text)); ! pg_statio_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, (pg_stat_get_blocks_fetched(i.oid) - pg_stat_get_blocks_hit(i.oid)) AS idx_blks_read, pg_stat_get_blocks_hit(i.oid) AS idx_blks_hit FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])); ! pg_statio_all_sequences | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS blks_read, pg_stat_get_blocks_hit(c.oid) AS blks_hit FROM (pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'S'::"char"); ! pg_statio_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS heap_blks_read, pg_stat_get_blocks_hit(c.oid) AS heap_blks_hit, (sum((pg_stat_get_blocks_fetched(i.indexrelid) - pg_stat_get_blocks_hit(i.indexrelid))))::bigint AS idx_blks_read, (sum(pg_stat_get_blocks_hit(i.indexrelid)))::bigint AS idx_blks_hit, (pg_stat_get_blocks_fetched(t.oid) - pg_stat_get_blocks_hit(t.oid)) AS toast_blks_read, pg_stat_get_blocks_hit(t.oid) AS toast_blks_hit, (pg_stat_get_blocks_fetched(x.oid) - pg_stat_get_blocks_hit(x.oid)) AS tidx_blks_read, pg_stat_get_blocks_hit(x.oid) AS tidx_blks_hit FROM ((((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_class t ON ((c.reltoastrelid = t.oid))) LEFT JOIN pg_class x ON ((t.reltoastidxid = x.oid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname, t.oid, x.oid; ! pg_statio_sys_indexes | SELECT pg_statio_all_indexes.relid, pg_statio_all_indexes.indexrelid, pg_statio_all_indexes.schemaname, pg_statio_all_indexes.relname, pg_statio_all_indexes.indexrelname, pg_statio_all_indexes.idx_blks_read, pg_statio_all_indexes.idx_blks_hit FROM pg_statio_all_indexes WHERE ((pg_statio_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_indexes.schemaname ~ '^pg_toast'::text)); ! pg_statio_sys_sequences | SELECT pg_statio_all_sequences.relid, pg_statio_all_sequences.schemaname, pg_statio_all_sequences.relname, pg_statio_all_sequences.blks_read, pg_statio_all_sequences.blks_hit FROM pg_statio_all_sequences WHERE ((pg_statio_all_sequences.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_sequences.schemaname ~ '^pg_toast'::text)); ! pg_statio_sys_tables | SELECT pg_statio_all_tables.relid, pg_statio_all_tables.schemaname, pg_statio_all_tables.relname, pg_statio_all_tables.heap_blks_read, pg_statio_all_tables.heap_blks_hit, pg_statio_all_tables.idx_blks_read, pg_statio_all_tables.idx_blks_hit, pg_statio_all_tables.toast_blks_read, pg_statio_all_tables.toast_blks_hit, pg_statio_all_tables.tidx_blks_read, pg_statio_all_tables.tidx_blks_hit FROM pg_statio_all_tables WHERE ((pg_statio_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_tables.schemaname ~ '^pg_toast'::text)); ! pg_statio_user_indexes | SELECT pg_statio_all_indexes.relid, pg_statio_all_indexes.indexrelid, pg_statio_all_indexes.schemaname, pg_statio_all_indexes.relname, pg_statio_all_indexes.indexrelname, pg_statio_all_indexes.idx_blks_read, pg_statio_all_indexes.idx_blks_hit FROM pg_statio_all_indexes WHERE ((pg_statio_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_indexes.schemaname !~ '^pg_toast'::text)); ! pg_statio_user_sequences | SELECT pg_statio_all_sequences.relid, pg_statio_all_sequences.schemaname, pg_statio_all_sequences.relname, pg_statio_all_sequences.blks_read, pg_statio_all_sequences.blks_hit FROM pg_statio_all_sequences WHERE ((pg_statio_all_sequences.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_sequences.schemaname !~ '^pg_toast'::text)); ! pg_statio_user_tables | SELECT pg_statio_all_tables.relid, pg_statio_all_tables.schemaname, pg_statio_all_tables.relname, pg_statio_all_tables.heap_blks_read, pg_statio_all_tables.heap_blks_hit, pg_statio_all_tables.idx_blks_read, pg_statio_all_tables.idx_blks_hit, pg_statio_all_tables.toast_blks_read, pg_statio_all_tables.toast_blks_hit, pg_statio_all_tables.tidx_blks_read, pg_statio_all_tables.tidx_blks_hit FROM pg_statio_all_tables WHERE ((pg_statio_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_tables.schemaname !~ '^pg_toast'::text)); ! pg_stats | SELECT n.nspname AS schemaname, c.relname AS tablename, a.attname, s.stainherit AS inherited, s.stanullfrac AS null_frac, s.stawidth AS avg_width, s.stadistinct AS n_distinct, CASE WHEN (s.stakind1 = 1) THEN s.stavalues1 WHEN (s.stakind2 = 1) THEN s.stavalues2 WHEN (s.stakind3 = 1) THEN s.stavalues3 WHEN (s.stakind4 = 1) THEN s.stavalues4 WHEN (s.stakind5 = 1) THEN s.stavalues5 ELSE NULL::anyarray END AS most_common_vals, CASE WHEN (s.stakind1 = 1) THEN s.stanumbers1 WHEN (s.stakind2 = 1) THEN s.stanumbers2 WHEN (s.stakind3 = 1) THEN s.stanumbers3 WHEN (s.stakind4 = 1) THEN s.stanumbers4 WHEN (s.stakind5 = 1) THEN s.stanumbers5 ELSE NULL::real[] END AS most_common_freqs, CASE WHEN (s.stakind1 = 2) THEN s.stavalues1 WHEN (s.stakind2 = 2) THEN s.stavalues2 WHEN (s.stakind3 = 2) THEN s.stavalues3 WHEN (s.stakind4 = 2) THEN s.stavalues4 WHEN (s.stakind5 = 2) THEN s.stavalues5 ELSE NULL::anyarray END AS histogram_bounds, CASE WHEN (s.stakind1 = 3) THEN s.stanumbers1[1] WHEN (s.stakind2 = 3) THEN s.stanumbers2[1] WHEN (s.stakind3 = 3) THEN s.stanumbers3[1] WHEN (s.stakind4 = 3) THEN s.stanumbers4[1] WHEN (s.stakind5 = 3) THEN s.stanumbers5[1] ELSE NULL::real END AS correlation, CASE WHEN (s.stakind1 = 4) THEN s.stavalues1 WHEN (s.stakind2 = 4) THEN s.stavalues2 WHEN (s.stakind3 = 4) THEN s.stavalues3 WHEN (s.stakind4 = 4) THEN s.stavalues4 WHEN (s.stakind5 = 4) THEN s.stavalues5 ELSE NULL::anyarray END AS most_common_elems, CASE WHEN (s.stakind1 = 4) THEN s.stanumbers1 WHEN (s.stakind2 = 4) THEN s.stanumbers2 WHEN (s.stakind3 = 4) THEN s.stanumbers3 WHEN (s.stakind4 = 4) THEN s.stanumbers4 WHEN (s.stakind5 = 4) THEN s.stanumbers5 ELSE NULL::real[] END AS most_common_elem_freqs, CASE WHEN (s.stakind1 = 5) THEN s.stanumbers1 WHEN (s.stakind2 = 5) THEN s.stanumbers2 WHEN (s.stakind3 = 5) THEN s.stanumbers3 WHEN (s.stakind4 = 5) THEN s.stanumbers4 WHEN (s.stakind5 = 5) THEN s.stanumbers5 ELSE NULL::real[] END AS elem_count_histogram FROM (((pg_statistic s JOIN pg_class c ON ((c.oid = s.starelid))) JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum)))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE ((NOT a.attisdropped) AND has_column_privilege(c.oid, a.attnum, 'select'::text)); ! pg_tables | SELECT n.nspname AS schemaname, c.relname AS tablename, pg_get_userbyid(c.relowner) AS tableowner, t.spcname AS tablespace, c.relhasindex AS hasindexes, c.relhasrules AS hasrules, c.relhastriggers AS hastriggers FROM ((pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace))) WHERE (c.relkind = 'r'::"char"); ! pg_timezone_abbrevs | SELECT pg_timezone_abbrevs.abbrev, pg_timezone_abbrevs.utc_offset, pg_timezone_abbrevs.is_dst FROM pg_timezone_abbrevs() pg_timezone_abbrevs(abbrev, utc_offset, is_dst); ! pg_timezone_names | SELECT pg_timezone_names.name, pg_timezone_names.abbrev, pg_timezone_names.utc_offset, pg_timezone_names.is_dst FROM pg_timezone_names() pg_timezone_names(name, abbrev, utc_offset, is_dst); ! pg_user | SELECT pg_shadow.usename, pg_shadow.usesysid, pg_shadow.usecreatedb, pg_shadow.usesuper, pg_shadow.usecatupd, pg_shadow.userepl, '********'::text AS passwd, pg_shadow.valuntil, pg_shadow.useconfig FROM pg_shadow; ! pg_user_mappings | SELECT u.oid AS umid, s.oid AS srvid, s.srvname, u.umuser, CASE WHEN (u.umuser = (0)::oid) THEN 'public'::name ELSE a.rolname END AS usename, CASE WHEN (pg_has_role(s.srvowner, 'USAGE'::text) OR has_server_privilege(s.oid, 'USAGE'::text)) THEN u.umoptions ELSE NULL::text[] END AS umoptions FROM ((pg_user_mapping u LEFT JOIN pg_authid a ON ((a.oid = u.umuser))) JOIN pg_foreign_server s ON ((u.umserver = s.oid))); ! pg_views | SELECT n.nspname AS schemaname, c.relname AS viewname, pg_get_userbyid(c.relowner) AS viewowner, pg_get_viewdef(c.oid) AS definition FROM (pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'v'::"char"); ! rtest_v1 | SELECT rtest_t1.a, rtest_t1.b FROM rtest_t1; ! rtest_vcomp | SELECT x.part, (x.size * y.factor) AS size_in_cm FROM rtest_comp x, rtest_unitfact y WHERE (x.unit = y.unit); ! rtest_vview1 | SELECT x.a, x.b FROM rtest_view1 x WHERE (0 < (SELECT count(*) AS count FROM rtest_view2 y WHERE (y.a = x.a))); ! rtest_vview2 | SELECT rtest_view1.a, rtest_view1.b FROM rtest_view1 WHERE rtest_view1.v; ! rtest_vview3 | SELECT x.a, x.b FROM rtest_vview2 x WHERE (0 < (SELECT count(*) AS count FROM rtest_view2 y WHERE (y.a = x.a))); ! rtest_vview4 | SELECT x.a, x.b, count(y.a) AS refcount FROM rtest_view1 x, rtest_view2 y WHERE (x.a = y.a) GROUP BY x.a, x.b; ! rtest_vview5 | SELECT rtest_view1.a, rtest_view1.b, rtest_viewfunc1(rtest_view1.a) AS refcount FROM rtest_view1; ! shoe | SELECT sh.shoename, sh.sh_avail, sh.slcolor, sh.slminlen, (sh.slminlen * un.un_fact) AS slminlen_cm, sh.slmaxlen, (sh.slmaxlen * un.un_fact) AS slmaxlen_cm, sh.slunit FROM shoe_data sh, unit un WHERE (sh.slunit = un.un_name); ! shoe_ready | SELECT rsh.shoename, rsh.sh_avail, rsl.sl_name, rsl.sl_avail, int4smaller(rsh.sh_avail, rsl.sl_avail) AS total_avail FROM shoe rsh, shoelace rsl WHERE (((rsl.sl_color = rsh.slcolor) AND (rsl.sl_len_cm >= rsh.slminlen_cm)) AND (rsl.sl_len_cm <= rsh.slmaxlen_cm)); ! shoelace | SELECT s.sl_name, s.sl_avail, s.sl_color, s.sl_len, s.sl_unit, (s.sl_len * u.un_fact) AS sl_len_cm FROM shoelace_data s, unit u WHERE (s.sl_unit = u.un_name); ! shoelace_candelete | SELECT shoelace_obsolete.sl_name, shoelace_obsolete.sl_avail, shoelace_obsolete.sl_color, shoelace_obsolete.sl_len, shoelace_obsolete.sl_unit, shoelace_obsolete.sl_len_cm FROM shoelace_obsolete WHERE (shoelace_obsolete.sl_avail = 0); ! 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 --- 1277,1643 ---- -- 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")); ! pg_locks | SELECT l.locktype, l.database, l.relation, l.page, l.tuple, l.virtualxid, l.transactionid, l.classid, l.objid, l.objsubid, l.virtualtransaction, l.pid, l.mode, l.granted, l.fastpath + ! | FROM pg_lock_status() l(locktype, database, relation, page, tuple, virtualxid, transactionid, classid, objid, objsubid, virtualtransaction, pid, mode, granted, fastpath); ! pg_prepared_statements | SELECT p.name, p.statement, p.prepare_time, p.parameter_types, p.from_sql + ! | FROM pg_prepared_statement() p(name, statement, prepare_time, parameter_types, from_sql); ! pg_prepared_xacts | SELECT p.transaction, p.gid, p.prepared, u.rolname AS owner, d.datname AS database + ! | FROM ((pg_prepared_xact() p(transaction, gid, prepared, ownerid, dbid) + ! | LEFT JOIN pg_authid u ON ((p.ownerid = u.oid))) + ! | LEFT JOIN pg_database d ON ((p.dbid = d.oid))); ! pg_roles | SELECT pg_authid.rolname, pg_authid.rolsuper, pg_authid.rolinherit, pg_authid.rolcreaterole, pg_authid.rolcreatedb, pg_authid.rolcatupdate, pg_authid.rolcanlogin, pg_authid.rolreplication, pg_authid.rolconnlimit, '********'::text AS rolpassword, pg_authid.rolvaliduntil, s.setconfig AS rolconfig, pg_authid.oid + ! | FROM (pg_authid + ! | LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid)))); ! pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) AS definition + ! | FROM ((pg_rewrite r + ! | JOIN pg_class c ON ((c.oid = r.ev_class))) + ! | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + ! | WHERE (r.rulename <> '_RETURN'::name); ! pg_seclabels | ( ( ( ( ( ( ( ( ( SELECT l.objoid, l.classoid, l.objsubid, + ! | CASE + ! | WHEN (rel.relkind = 'r'::"char") THEN 'table'::text + ! | WHEN (rel.relkind = 'v'::"char") THEN 'view'::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, l.provider, l.label + ! | FROM ((pg_seclabel l + ! | JOIN pg_class rel ON (((l.classoid = rel.tableoid) AND (l.objoid = rel.oid)))) + ! | JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid))) + ! | WHERE (l.objsubid = 0) + ! | UNION ALL + ! | SELECT l.objoid, l.classoid, l.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, l.provider, l.label + ! | FROM (((pg_seclabel l + ! | JOIN pg_class rel ON (((l.classoid = rel.tableoid) AND (l.objoid = rel.oid)))) + ! | JOIN pg_attribute att ON (((rel.oid = att.attrelid) AND (l.objsubid = att.attnum)))) + ! | JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid))) + ! | WHERE (l.objsubid <> 0)) + ! | UNION ALL + ! | SELECT l.objoid, l.classoid, l.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, l.provider, l.label + ! | FROM ((pg_seclabel l + ! | JOIN pg_proc pro ON (((l.classoid = pro.tableoid) AND (l.objoid = pro.oid)))) + ! | JOIN pg_namespace nsp ON ((pro.pronamespace = nsp.oid))) + ! | WHERE (l.objsubid = 0)) + ! | UNION ALL + ! | SELECT l.objoid, l.classoid, l.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, l.provider, l.label + ! | FROM ((pg_seclabel l + ! | JOIN pg_type typ ON (((l.classoid = typ.tableoid) AND (l.objoid = typ.oid)))) + ! | JOIN pg_namespace nsp ON ((typ.typnamespace = nsp.oid))) + ! | WHERE (l.objsubid = 0)) + ! | UNION ALL + ! | SELECT l.objoid, l.classoid, l.objsubid, 'large object'::text AS objtype, NULL::oid AS objnamespace, (l.objoid)::text AS objname, l.provider, l.label + ! | FROM (pg_seclabel l + ! | JOIN pg_largeobject_metadata lom ON ((l.objoid = lom.oid))) + ! | WHERE ((l.classoid = ('pg_largeobject'::regclass)::oid) AND (l.objsubid = 0))) + ! | UNION ALL + ! | SELECT l.objoid, l.classoid, l.objsubid, 'language'::text AS objtype, NULL::oid AS objnamespace, quote_ident((lan.lanname)::text) AS objname, l.provider, l.label + ! | FROM (pg_seclabel l + ! | JOIN pg_language lan ON (((l.classoid = lan.tableoid) AND (l.objoid = lan.oid)))) + ! | WHERE (l.objsubid = 0)) + ! | UNION ALL + ! | SELECT l.objoid, l.classoid, l.objsubid, 'schema'::text AS objtype, nsp.oid AS objnamespace, quote_ident((nsp.nspname)::text) AS objname, l.provider, l.label + ! | FROM (pg_seclabel l + ! | JOIN pg_namespace nsp ON (((l.classoid = nsp.tableoid) AND (l.objoid = nsp.oid)))) + ! | WHERE (l.objsubid = 0)) + ! | UNION ALL + ! | SELECT l.objoid, l.classoid, l.objsubid, 'event trigger'::text AS objtype, NULL::oid AS objnamespace, quote_ident((evt.evtname)::text) AS objname, l.provider, l.label + ! | FROM (pg_seclabel l + ! | JOIN pg_event_trigger evt ON (((l.classoid = evt.tableoid) AND (l.objoid = evt.oid)))) + ! | WHERE (l.objsubid = 0)) + ! | UNION ALL + ! | SELECT l.objoid, l.classoid, 0 AS objsubid, 'database'::text AS objtype, NULL::oid AS objnamespace, quote_ident((dat.datname)::text) AS objname, l.provider, l.label + ! | FROM (pg_shseclabel l + ! | JOIN pg_database dat ON (((l.classoid = dat.tableoid) AND (l.objoid = dat.oid))))) + ! | UNION ALL + ! | SELECT l.objoid, l.classoid, 0 AS objsubid, 'tablespace'::text AS objtype, NULL::oid AS objnamespace, quote_ident((spc.spcname)::text) AS objname, l.provider, l.label + ! | FROM (pg_shseclabel l + ! | JOIN pg_tablespace spc ON (((l.classoid = spc.tableoid) AND (l.objoid = spc.oid))))) + ! | UNION ALL + ! | SELECT l.objoid, l.classoid, 0 AS objsubid, 'role'::text AS objtype, NULL::oid AS objnamespace, quote_ident((rol.rolname)::text) AS objname, l.provider, l.label + ! | FROM (pg_shseclabel l + ! | JOIN pg_authid rol ON (((l.classoid = rol.tableoid) AND (l.objoid = rol.oid)))); ! pg_settings | SELECT a.name, a.setting, a.unit, a.category, a.short_desc, a.extra_desc, a.context, a.vartype, a.source, a.min_val, a.max_val, a.enumvals, a.boot_val, a.reset_val, a.sourcefile, a.sourceline + ! | FROM pg_show_all_settings() a(name, setting, unit, category, short_desc, extra_desc, context, vartype, source, min_val, max_val, enumvals, boot_val, reset_val, sourcefile, sourceline); ! pg_shadow | SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb AS usecreatedb, pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolreplication AS userepl, pg_authid.rolpassword AS passwd, (pg_authid.rolvaliduntil)::abstime AS valuntil, s.setconfig AS useconfig + ! | FROM (pg_authid + ! | LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid)))) + ! | WHERE pg_authid.rolcanlogin; ! pg_stat_activity | SELECT s.datid, d.datname, s.pid, s.usesysid, u.rolname AS usename, s.application_name, s.client_addr, s.client_hostname, s.client_port, s.backend_start, s.xact_start, s.query_start, s.state_change, s.waiting, s.state, s.query + ! | FROM pg_database d, pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port), pg_authid u + ! | WHERE ((s.datid = d.oid) AND (s.usesysid = u.oid)); ! pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch + ! | FROM (((pg_class c + ! | JOIN pg_index x ON ((c.oid = x.indrelid))) + ! | JOIN pg_class i ON ((i.oid = x.indexrelid))) + ! | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + ! | WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])); ! pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan, ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd, pg_stat_get_live_tuples(c.oid) AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, pg_stat_get_last_analyze_time(c.oid) AS last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze, pg_stat_get_vacuum_count(c.oid) AS vacuum_count, pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count, pg_stat_get_analyze_count(c.oid) AS analyze_count, pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count+ ! | FROM ((pg_class c + ! | LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) + ! | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + ! | WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) + ! | GROUP BY c.oid, n.nspname, c.relname; ! pg_stat_bgwriter | SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req, pg_stat_get_checkpoint_write_time() AS checkpoint_write_time, pg_stat_get_checkpoint_sync_time() AS checkpoint_sync_time, pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint, pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean, pg_stat_get_buf_written_backend() AS buffers_backend, pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync, pg_stat_get_buf_alloc() AS buffers_alloc, pg_stat_get_bgwriter_stat_reset_time() AS stats_reset; ! pg_stat_database | SELECT d.oid AS datid, d.datname, pg_stat_get_db_numbackends(d.oid) AS numbackends, pg_stat_get_db_xact_commit(d.oid) AS xact_commit, pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, (pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS blks_read, pg_stat_get_db_blocks_hit(d.oid) AS blks_hit, pg_stat_get_db_tuples_returned(d.oid) AS tup_returned, pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched, pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted, pg_stat_get_db_tuples_updated(d.oid) AS tup_updated, pg_stat_get_db_tuples_deleted(d.oid) AS tup_deleted, pg_stat_get_db_conflict_all(d.oid) AS conflicts, pg_stat_get_db_temp_files(d.oid) AS temp_files, pg_stat_get_db_temp_bytes(d.oid) AS temp_bytes, pg_stat_get_db_deadlocks(d.oid) AS deadlocks, pg_stat_get_db_blk_read_time(d.oid) AS blk_read_time, pg_stat_get_db_blk_write_time(d.oid) AS blk_write_time, pg_stat_get_db_stat_reset_time(d.oid) AS stats_reset + ! | FROM pg_database d; ! pg_stat_database_conflicts | SELECT d.oid AS datid, d.datname, pg_stat_get_db_conflict_tablespace(d.oid) AS confl_tablespace, pg_stat_get_db_conflict_lock(d.oid) AS confl_lock, pg_stat_get_db_conflict_snapshot(d.oid) AS confl_snapshot, pg_stat_get_db_conflict_bufferpin(d.oid) AS confl_bufferpin, pg_stat_get_db_conflict_startup_deadlock(d.oid) AS confl_deadlock + ! | FROM pg_database d; ! pg_stat_replication | SELECT s.pid, s.usesysid, u.rolname AS usename, s.application_name, s.client_addr, s.client_hostname, s.client_port, s.backend_start, w.state, w.sent_location, w.write_location, w.flush_location, w.replay_location, w.sync_priority, w.sync_state + ! | FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port), pg_authid u, pg_stat_get_wal_senders() w(pid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state) + ! | WHERE ((s.usesysid = u.oid) AND (s.pid = w.pid)); ! pg_stat_sys_indexes | SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch + ! | FROM pg_stat_all_indexes + ! | WHERE ((pg_stat_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_indexes.schemaname ~ '^pg_toast'::text)); ! pg_stat_sys_tables | SELECT pg_stat_all_tables.relid, pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd, pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_tup_hot_upd, pg_stat_all_tables.n_live_tup, pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze, pg_stat_all_tables.vacuum_count, pg_stat_all_tables.autovacuum_count, pg_stat_all_tables.analyze_count, pg_stat_all_tables.autoanalyze_count + ! | FROM pg_stat_all_tables + ! | WHERE ((pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_tables.schemaname ~ '^pg_toast'::text)); ! pg_stat_user_functions | SELECT p.oid AS funcid, n.nspname AS schemaname, p.proname AS funcname, pg_stat_get_function_calls(p.oid) AS calls, pg_stat_get_function_total_time(p.oid) AS total_time, pg_stat_get_function_self_time(p.oid) AS self_time + ! | FROM (pg_proc p + ! | LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace))) + ! | WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_function_calls(p.oid) IS NOT NULL)); ! pg_stat_user_indexes | SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch + ! | FROM pg_stat_all_indexes + ! | WHERE ((pg_stat_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_indexes.schemaname !~ '^pg_toast'::text)); ! pg_stat_user_tables | SELECT pg_stat_all_tables.relid, pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd, pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_tup_hot_upd, pg_stat_all_tables.n_live_tup, pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze, pg_stat_all_tables.vacuum_count, pg_stat_all_tables.autovacuum_count, pg_stat_all_tables.analyze_count, pg_stat_all_tables.autoanalyze_count + ! | FROM pg_stat_all_tables + ! | WHERE ((pg_stat_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_tables.schemaname !~ '^pg_toast'::text)); ! pg_stat_xact_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_xact_numscans(c.oid) AS seq_scan, pg_stat_get_xact_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_xact_numscans(i.indexrelid)))::bigint AS idx_scan, ((sum(pg_stat_get_xact_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_xact_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_xact_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_xact_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_xact_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_xact_tuples_hot_updated(c.oid) AS n_tup_hot_upd + ! | FROM ((pg_class c + ! | LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) + ! | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + ! | WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) + ! | GROUP BY c.oid, n.nspname, c.relname; ! pg_stat_xact_sys_tables | SELECT pg_stat_xact_all_tables.relid, pg_stat_xact_all_tables.schemaname, pg_stat_xact_all_tables.relname, pg_stat_xact_all_tables.seq_scan, pg_stat_xact_all_tables.seq_tup_read, pg_stat_xact_all_tables.idx_scan, pg_stat_xact_all_tables.idx_tup_fetch, pg_stat_xact_all_tables.n_tup_ins, pg_stat_xact_all_tables.n_tup_upd, pg_stat_xact_all_tables.n_tup_del, pg_stat_xact_all_tables.n_tup_hot_upd + ! | FROM pg_stat_xact_all_tables + ! | WHERE ((pg_stat_xact_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_xact_all_tables.schemaname ~ '^pg_toast'::text)); ! pg_stat_xact_user_functions | SELECT p.oid AS funcid, n.nspname AS schemaname, p.proname AS funcname, pg_stat_get_xact_function_calls(p.oid) AS calls, pg_stat_get_xact_function_total_time(p.oid) AS total_time, pg_stat_get_xact_function_self_time(p.oid) AS self_time + ! | FROM (pg_proc p + ! | LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace))) + ! | WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_xact_function_calls(p.oid) IS NOT NULL)); ! pg_stat_xact_user_tables | SELECT pg_stat_xact_all_tables.relid, pg_stat_xact_all_tables.schemaname, pg_stat_xact_all_tables.relname, pg_stat_xact_all_tables.seq_scan, pg_stat_xact_all_tables.seq_tup_read, pg_stat_xact_all_tables.idx_scan, pg_stat_xact_all_tables.idx_tup_fetch, pg_stat_xact_all_tables.n_tup_ins, pg_stat_xact_all_tables.n_tup_upd, pg_stat_xact_all_tables.n_tup_del, pg_stat_xact_all_tables.n_tup_hot_upd + ! | FROM pg_stat_xact_all_tables + ! | WHERE ((pg_stat_xact_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_xact_all_tables.schemaname !~ '^pg_toast'::text)); ! pg_statio_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, (pg_stat_get_blocks_fetched(i.oid) - pg_stat_get_blocks_hit(i.oid)) AS idx_blks_read, pg_stat_get_blocks_hit(i.oid) AS idx_blks_hit + ! | FROM (((pg_class c + ! | JOIN pg_index x ON ((c.oid = x.indrelid))) + ! | JOIN pg_class i ON ((i.oid = x.indexrelid))) + ! | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + ! | WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])); ! pg_statio_all_sequences | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS blks_read, pg_stat_get_blocks_hit(c.oid) AS blks_hit + ! | FROM (pg_class c + ! | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + ! | WHERE (c.relkind = 'S'::"char"); ! pg_statio_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS heap_blks_read, pg_stat_get_blocks_hit(c.oid) AS heap_blks_hit, (sum((pg_stat_get_blocks_fetched(i.indexrelid) - pg_stat_get_blocks_hit(i.indexrelid))))::bigint AS idx_blks_read, (sum(pg_stat_get_blocks_hit(i.indexrelid)))::bigint AS idx_blks_hit, (pg_stat_get_blocks_fetched(t.oid) - pg_stat_get_blocks_hit(t.oid)) AS toast_blks_read, pg_stat_get_blocks_hit(t.oid) AS toast_blks_hit, (pg_stat_get_blocks_fetched(x.oid) - pg_stat_get_blocks_hit(x.oid)) AS tidx_blks_read, pg_stat_get_blocks_hit(x.oid) AS tidx_blks_hit + ! | FROM ((((pg_class c + ! | LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) + ! | LEFT JOIN pg_class t ON ((c.reltoastrelid = t.oid))) + ! | LEFT JOIN pg_class x ON ((t.reltoastidxid = x.oid))) + ! | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + ! | WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) + ! | GROUP BY c.oid, n.nspname, c.relname, t.oid, x.oid; ! pg_statio_sys_indexes | SELECT pg_statio_all_indexes.relid, pg_statio_all_indexes.indexrelid, pg_statio_all_indexes.schemaname, pg_statio_all_indexes.relname, pg_statio_all_indexes.indexrelname, pg_statio_all_indexes.idx_blks_read, pg_statio_all_indexes.idx_blks_hit + ! | FROM pg_statio_all_indexes + ! | WHERE ((pg_statio_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_indexes.schemaname ~ '^pg_toast'::text)); ! pg_statio_sys_sequences | SELECT pg_statio_all_sequences.relid, pg_statio_all_sequences.schemaname, pg_statio_all_sequences.relname, pg_statio_all_sequences.blks_read, pg_statio_all_sequences.blks_hit + ! | FROM pg_statio_all_sequences + ! | WHERE ((pg_statio_all_sequences.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_sequences.schemaname ~ '^pg_toast'::text)); ! pg_statio_sys_tables | SELECT pg_statio_all_tables.relid, pg_statio_all_tables.schemaname, pg_statio_all_tables.relname, pg_statio_all_tables.heap_blks_read, pg_statio_all_tables.heap_blks_hit, pg_statio_all_tables.idx_blks_read, pg_statio_all_tables.idx_blks_hit, pg_statio_all_tables.toast_blks_read, pg_statio_all_tables.toast_blks_hit, pg_statio_all_tables.tidx_blks_read, pg_statio_all_tables.tidx_blks_hit + ! | FROM pg_statio_all_tables + ! | WHERE ((pg_statio_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_tables.schemaname ~ '^pg_toast'::text)); ! pg_statio_user_indexes | SELECT pg_statio_all_indexes.relid, pg_statio_all_indexes.indexrelid, pg_statio_all_indexes.schemaname, pg_statio_all_indexes.relname, pg_statio_all_indexes.indexrelname, pg_statio_all_indexes.idx_blks_read, pg_statio_all_indexes.idx_blks_hit + ! | FROM pg_statio_all_indexes + ! | WHERE ((pg_statio_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_indexes.schemaname !~ '^pg_toast'::text)); ! pg_statio_user_sequences | SELECT pg_statio_all_sequences.relid, pg_statio_all_sequences.schemaname, pg_statio_all_sequences.relname, pg_statio_all_sequences.blks_read, pg_statio_all_sequences.blks_hit + ! | FROM pg_statio_all_sequences + ! | WHERE ((pg_statio_all_sequences.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_sequences.schemaname !~ '^pg_toast'::text)); ! pg_statio_user_tables | SELECT pg_statio_all_tables.relid, pg_statio_all_tables.schemaname, pg_statio_all_tables.relname, pg_statio_all_tables.heap_blks_read, pg_statio_all_tables.heap_blks_hit, pg_statio_all_tables.idx_blks_read, pg_statio_all_tables.idx_blks_hit, pg_statio_all_tables.toast_blks_read, pg_statio_all_tables.toast_blks_hit, pg_statio_all_tables.tidx_blks_read, pg_statio_all_tables.tidx_blks_hit + ! | FROM pg_statio_all_tables + ! | WHERE ((pg_statio_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_tables.schemaname !~ '^pg_toast'::text)); ! pg_stats | SELECT n.nspname AS schemaname, c.relname AS tablename, a.attname, s.stainherit AS inherited, s.stanullfrac AS null_frac, s.stawidth AS avg_width, s.stadistinct AS n_distinct, + ! | CASE + ! | WHEN (s.stakind1 = 1) THEN s.stavalues1 + ! | WHEN (s.stakind2 = 1) THEN s.stavalues2 + ! | WHEN (s.stakind3 = 1) THEN s.stavalues3 + ! | WHEN (s.stakind4 = 1) THEN s.stavalues4 + ! | WHEN (s.stakind5 = 1) THEN s.stavalues5 + ! | ELSE NULL::anyarray + ! | END AS most_common_vals, + ! | CASE + ! | WHEN (s.stakind1 = 1) THEN s.stanumbers1 + ! | WHEN (s.stakind2 = 1) THEN s.stanumbers2 + ! | WHEN (s.stakind3 = 1) THEN s.stanumbers3 + ! | WHEN (s.stakind4 = 1) THEN s.stanumbers4 + ! | WHEN (s.stakind5 = 1) THEN s.stanumbers5 + ! | ELSE NULL::real[] + ! | END AS most_common_freqs, + ! | CASE + ! | WHEN (s.stakind1 = 2) THEN s.stavalues1 + ! | WHEN (s.stakind2 = 2) THEN s.stavalues2 + ! | WHEN (s.stakind3 = 2) THEN s.stavalues3 + ! | WHEN (s.stakind4 = 2) THEN s.stavalues4 + ! | WHEN (s.stakind5 = 2) THEN s.stavalues5 + ! | ELSE NULL::anyarray + ! | END AS histogram_bounds, + ! | CASE + ! | WHEN (s.stakind1 = 3) THEN s.stanumbers1[1] + ! | WHEN (s.stakind2 = 3) THEN s.stanumbers2[1] + ! | WHEN (s.stakind3 = 3) THEN s.stanumbers3[1] + ! | WHEN (s.stakind4 = 3) THEN s.stanumbers4[1] + ! | WHEN (s.stakind5 = 3) THEN s.stanumbers5[1] + ! | ELSE NULL::real + ! | END AS correlation, + ! | CASE + ! | WHEN (s.stakind1 = 4) THEN s.stavalues1 + ! | WHEN (s.stakind2 = 4) THEN s.stavalues2 + ! | WHEN (s.stakind3 = 4) THEN s.stavalues3 + ! | WHEN (s.stakind4 = 4) THEN s.stavalues4 + ! | WHEN (s.stakind5 = 4) THEN s.stavalues5 + ! | ELSE NULL::anyarray + ! | END AS most_common_elems, + ! | CASE + ! | WHEN (s.stakind1 = 4) THEN s.stanumbers1 + ! | WHEN (s.stakind2 = 4) THEN s.stanumbers2 + ! | WHEN (s.stakind3 = 4) THEN s.stanumbers3 + ! | WHEN (s.stakind4 = 4) THEN s.stanumbers4 + ! | WHEN (s.stakind5 = 4) THEN s.stanumbers5 + ! | ELSE NULL::real[] + ! | END AS most_common_elem_freqs, + ! | CASE + ! | WHEN (s.stakind1 = 5) THEN s.stanumbers1 + ! | WHEN (s.stakind2 = 5) THEN s.stanumbers2 + ! | WHEN (s.stakind3 = 5) THEN s.stanumbers3 + ! | WHEN (s.stakind4 = 5) THEN s.stanumbers4 + ! | WHEN (s.stakind5 = 5) THEN s.stanumbers5 + ! | ELSE NULL::real[] + ! | END AS elem_count_histogram + ! | FROM (((pg_statistic s + ! | JOIN pg_class c ON ((c.oid = s.starelid))) + ! | JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum)))) + ! | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + ! | WHERE ((NOT a.attisdropped) AND has_column_privilege(c.oid, a.attnum, 'select'::text)); ! pg_tables | SELECT n.nspname AS schemaname, c.relname AS tablename, pg_get_userbyid(c.relowner) AS tableowner, t.spcname AS tablespace, c.relhasindex AS hasindexes, c.relhasrules AS hasrules, c.relhastriggers AS hastriggers + ! | FROM ((pg_class c + ! | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + ! | LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace))) + ! | WHERE (c.relkind = 'r'::"char"); ! pg_timezone_abbrevs | SELECT pg_timezone_abbrevs.abbrev, pg_timezone_abbrevs.utc_offset, pg_timezone_abbrevs.is_dst + ! | FROM pg_timezone_abbrevs() pg_timezone_abbrevs(abbrev, utc_offset, is_dst); ! pg_timezone_names | SELECT pg_timezone_names.name, pg_timezone_names.abbrev, pg_timezone_names.utc_offset, pg_timezone_names.is_dst + ! | FROM pg_timezone_names() pg_timezone_names(name, abbrev, utc_offset, is_dst); ! pg_user | SELECT pg_shadow.usename, pg_shadow.usesysid, pg_shadow.usecreatedb, pg_shadow.usesuper, pg_shadow.usecatupd, pg_shadow.userepl, '********'::text AS passwd, pg_shadow.valuntil, pg_shadow.useconfig + ! | FROM pg_shadow; ! pg_user_mappings | SELECT u.oid AS umid, s.oid AS srvid, s.srvname, u.umuser, + ! | CASE + ! | WHEN (u.umuser = (0)::oid) THEN 'public'::name + ! | ELSE a.rolname + ! | END AS usename, + ! | CASE + ! | WHEN (pg_has_role(s.srvowner, 'USAGE'::text) OR has_server_privilege(s.oid, 'USAGE'::text)) THEN u.umoptions + ! | ELSE NULL::text[] + ! | END AS umoptions + ! | FROM ((pg_user_mapping u + ! | LEFT JOIN pg_authid a ON ((a.oid = u.umuser))) + ! | JOIN pg_foreign_server s ON ((u.umserver = s.oid))); ! pg_views | SELECT n.nspname AS schemaname, c.relname AS viewname, pg_get_userbyid(c.relowner) AS viewowner, pg_get_viewdef(c.oid) AS definition + ! | FROM (pg_class c + ! | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + ! | WHERE (c.relkind = 'v'::"char"); ! rtest_v1 | SELECT rtest_t1.a, rtest_t1.b + ! | FROM rtest_t1; ! rtest_vcomp | SELECT x.part, (x.size * y.factor) AS size_in_cm + ! | FROM rtest_comp x, rtest_unitfact y + ! | WHERE (x.unit = y.unit); ! rtest_vview1 | SELECT x.a, x.b + ! | FROM rtest_view1 x + ! | WHERE (0 < ( SELECT count(*) AS count + ! | FROM rtest_view2 y + ! | WHERE (y.a = x.a))); ! rtest_vview2 | SELECT rtest_view1.a, rtest_view1.b + ! | FROM rtest_view1 + ! | WHERE rtest_view1.v; ! rtest_vview3 | SELECT x.a, x.b + ! | FROM rtest_vview2 x + ! | WHERE (0 < ( SELECT count(*) AS count + ! | FROM rtest_view2 y + ! | WHERE (y.a = x.a))); ! rtest_vview4 | SELECT x.a, x.b, count(y.a) AS refcount + ! | FROM rtest_view1 x, rtest_view2 y + ! | WHERE (x.a = y.a) + ! | GROUP BY x.a, x.b; ! rtest_vview5 | SELECT rtest_view1.a, rtest_view1.b, rtest_viewfunc1(rtest_view1.a) AS refcount + ! | FROM rtest_view1; ! shoe | SELECT sh.shoename, sh.sh_avail, sh.slcolor, sh.slminlen, (sh.slminlen * un.un_fact) AS slminlen_cm, sh.slmaxlen, (sh.slmaxlen * un.un_fact) AS slmaxlen_cm, sh.slunit + ! | FROM shoe_data sh, unit un + ! | WHERE (sh.slunit = un.un_name); ! shoe_ready | SELECT rsh.shoename, rsh.sh_avail, rsl.sl_name, rsl.sl_avail, int4smaller(rsh.sh_avail, rsl.sl_avail) AS total_avail + ! | FROM shoe rsh, shoelace rsl + ! | WHERE (((rsl.sl_color = rsh.slcolor) AND (rsl.sl_len_cm >= rsh.slminlen_cm)) AND (rsl.sl_len_cm <= rsh.slmaxlen_cm)); ! shoelace | SELECT s.sl_name, s.sl_avail, s.sl_color, s.sl_len, s.sl_unit, (s.sl_len * u.un_fact) AS sl_len_cm + ! | FROM shoelace_data s, unit u + ! | WHERE (s.sl_unit = u.un_name); ! shoelace_candelete | SELECT shoelace_obsolete.sl_name, shoelace_obsolete.sl_avail, shoelace_obsolete.sl_color, shoelace_obsolete.sl_len, shoelace_obsolete.sl_unit, shoelace_obsolete.sl_len_cm + ! | FROM shoelace_obsolete + ! | WHERE (shoelace_obsolete.sl_avail = 0); ! 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 *************** *** 1584,1592 **** select * from only t1_2; -- test various flavors of pg_get_viewdef() select pg_get_viewdef('shoe'::regclass) as unpretty; ! unpretty ! ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ! SELECT sh.shoename, sh.sh_avail, sh.slcolor, sh.slminlen, (sh.slminlen * un.un_fact) AS slminlen_cm, sh.slmaxlen, (sh.slmaxlen * un.un_fact) AS slmaxlen_cm, sh.slunit FROM shoe_data sh, unit un WHERE (sh.slunit = un.un_name); (1 row) select pg_get_viewdef('shoe'::regclass,true) as pretty; --- 1883,1893 ---- -- test various flavors of pg_get_viewdef() select pg_get_viewdef('shoe'::regclass) as unpretty; ! unpretty ! ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ! SELECT sh.shoename, sh.sh_avail, sh.slcolor, sh.slminlen, (sh.slminlen * un.un_fact) AS slminlen_cm, sh.slmaxlen, (sh.slmaxlen * un.un_fact) AS slmaxlen_cm, sh.slunit+ ! FROM shoe_data sh, unit un + ! WHERE (sh.slunit = un.un_name); (1 row) select pg_get_viewdef('shoe'::regclass,true) as pretty; *** a/src/test/regress/expected/with.out --- b/src/test/regress/expected/with.out *************** *** 270,278 **** SELECT * FROM vsubdepartment ORDER BY name; -- Check reverse listing SELECT pg_get_viewdef('vsubdepartment'::regclass); ! pg_get_viewdef ! --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ! WITH RECURSIVE subdepartment AS (SELECT department.id, department.parent_department, department.name FROM department WHERE (department.name = 'A'::text) UNION ALL SELECT d.id, d.parent_department, d.name FROM department d, subdepartment sd WHERE (d.parent_department = sd.id)) SELECT subdepartment.id, subdepartment.parent_department, subdepartment.name FROM subdepartment; (1 row) SELECT pg_get_viewdef('vsubdepartment'::regclass, true); --- 270,288 ---- -- Check reverse listing SELECT pg_get_viewdef('vsubdepartment'::regclass); ! pg_get_viewdef ! -------------------------------------------------------------------------------------- ! WITH RECURSIVE subdepartment AS ( + ! SELECT department.id, department.parent_department, department.name+ ! FROM department + ! WHERE (department.name = 'A'::text) + ! UNION ALL + ! SELECT d.id, d.parent_department, d.name + ! FROM department d, subdepartment sd + ! WHERE (d.parent_department = sd.id) + ! ) + ! SELECT subdepartment.id, subdepartment.parent_department, subdepartment.name + ! FROM subdepartment; (1 row) SELECT pg_get_viewdef('vsubdepartment'::regclass, true); *** a/src/test/regress/expected/xml.out --- b/src/test/regress/expected/xml.out *************** *** 3,82 **** CREATE TABLE xmltest ( data xml ); INSERT INTO xmltest VALUES (1, 'one'); INSERT INTO xmltest VALUES (2, 'two'); INSERT INTO xmltest VALUES (3, 'one ! 2 | two ! (2 rows) SELECT xmlcomment('test'); ! xmlcomment ! ------------- ! ! (1 row) ! SELECT xmlcomment('-test'); ! xmlcomment ! -------------- ! ! (1 row) ! SELECT xmlcomment('test-'); ! ERROR: invalid XML comment SELECT xmlcomment('--test'); ! ERROR: invalid XML comment SELECT xmlcomment('te st'); ! xmlcomment ! -------------- ! ! (1 row) ! SELECT xmlconcat(xmlcomment('hello'), xmlelement(NAME qux, 'foo'), xmlcomment('world')); ! xmlconcat ! ---------------------------------------- ! foo ! (1 row) ! SELECT xmlconcat('hello', 'you'); ! xmlconcat ! ----------- ! helloyou ! (1 row) ! SELECT xmlconcat(1, 2); ERROR: argument of XMLCONCAT must be type xml, not type integer LINE 1: SELECT xmlconcat(1, 2); ^ SELECT xmlconcat('bad', '', NULL, ''); ! xmlconcat ! -------------- ! ! (1 row) ! SELECT xmlconcat('', NULL, ''); ! xmlconcat ! ----------------------------------- ! ! (1 row) ! SELECT xmlconcat(NULL); xmlconcat ----------- --- 3,84 ---- data xml ); INSERT INTO xmltest VALUES (1, 'one'); + ERROR: unsupported XML feature + LINE 1: INSERT INTO xmltest VALUES (1, 'one'); + ^ + DETAIL: This functionality requires the server to be built with libxml support. + HINT: You need to rebuild PostgreSQL using --with-libxml. INSERT INTO xmltest VALUES (2, 'two'); + ERROR: unsupported XML feature + LINE 1: INSERT INTO xmltest VALUES (2, 'two'); + ^ + DETAIL: This functionality requires the server to be built with libxml support. + HINT: You need to rebuild PostgreSQL using --with-libxml. INSERT INTO xmltest VALUES (3, '', NULL, ''); ! ERROR: unsupported XML feature ! LINE 1: SELECT xmlconcat('', NULL, '', NULL, ''); ! ERROR: unsupported XML feature ! LINE 1: SELECT xmlconcat('', NULL, 'content ! (1 row) ! SELECT xmlelement(name element, xmlattributes ('unnamed and wrong')); ! ERROR: unnamed XML attribute value must be a column reference ! LINE 2: xmlattributes ('unnamed and wrong')); ! ^ SELECT xmlelement(name element, xmlelement(name nested, 'stuff')); ! xmlelement ! ------------------------------------------- ! stuff ! (1 row) ! SELECT xmlelement(name employee, xmlforest(name, age, salary as pay)) FROM emp; ! xmlelement ! ---------------------------------------------------------------------- ! sharon251000 ! sam302000 ! bill201000 ! jeff23600 ! cim30400 ! linda19100 ! (6 rows) ! SELECT xmlelement(name duplicate, xmlattributes(1 as a, 2 as b, 3 as a)); ! ERROR: XML attribute name "a" appears more than once ! LINE 1: ...ment(name duplicate, xmlattributes(1 as a, 2 as b, 3 as a)); ! ^ SELECT xmlelement(name num, 37); ! xmlelement ! --------------- ! 37 ! (1 row) ! SELECT xmlelement(name foo, text 'bar'); ! xmlelement ! ---------------- ! bar ! (1 row) ! SELECT xmlelement(name foo, xml 'bar'); ! xmlelement ! ---------------- ! bar ! (1 row) ! SELECT xmlelement(name foo, text 'br'); ! xmlelement ! ------------------------- ! b<a/>r ! (1 row) ! SELECT xmlelement(name foo, xml 'br'); ! xmlelement ! ------------------- ! br ! (1 row) ! SELECT xmlelement(name foo, array[1, 2, 3]); ! xmlelement ! ------------------------------------------------------------------------- ! 123 ! (1 row) ! SET xmlbinary TO base64; SELECT xmlelement(name foo, bytea 'bar'); ! xmlelement ! ----------------- ! YmFy ! (1 row) ! SET xmlbinary TO hex; SELECT xmlelement(name foo, bytea 'bar'); ! xmlelement ! ------------------- ! 626172 ! (1 row) ! SELECT xmlelement(name foo, xmlattributes(true as bar)); ! xmlelement ! ------------------- ! ! (1 row) ! SELECT xmlelement(name foo, xmlattributes('2009-04-09 00:24:37'::timestamp as bar)); ! xmlelement ! ---------------------------------- ! ! (1 row) ! SELECT xmlelement(name foo, xmlattributes('infinity'::timestamp as bar)); ! ERROR: timestamp out of range ! DETAIL: XML does not support infinite timestamp values. SELECT xmlelement(name foo, xmlattributes('<>&"''' as funny, xml 'br' as funnier)); ! xmlelement ! ------------------------------------------------------------ ! ! (1 row) ! SELECT xmlparse(content 'abc'); ! xmlparse ! ---------- ! abc ! (1 row) ! SELECT xmlparse(content 'x'); ! xmlparse ! -------------- ! x ! (1 row) ! SELECT xmlparse(content '&'); ! ERROR: invalid XML content ! DETAIL: line 1: xmlParseEntityRef: no name ! & ! ^ ! line 1: chunk is not well balanced ! & ! ^ SELECT xmlparse(content '&idontexist;'); ! ERROR: invalid XML content ! DETAIL: line 1: Entity 'idontexist' not defined ! &idontexist; ! ^ ! line 1: chunk is not well balanced ! &idontexist; ! ^ SELECT xmlparse(content ''); ! xmlparse ! --------------------------- ! ! (1 row) ! SELECT xmlparse(content ''); ! xmlparse ! -------------------------------- ! ! (1 row) ! SELECT xmlparse(content '&idontexist;'); ! ERROR: invalid XML content ! DETAIL: line 1: Entity 'idontexist' not defined ! &idontexist; ! ^ ! line 1: Opening and ending tag mismatch: twoerrors line 1 and unbalanced ! &idontexist; ! ^ ! line 1: chunk is not well balanced ! &idontexist; ! ^ SELECT xmlparse(content ''); ! xmlparse ! --------------------- ! ! (1 row) ! SELECT xmlparse(document 'abc'); ! ERROR: invalid XML document ! DETAIL: line 1: Start tag expected, '<' not found ! abc ! ^ SELECT xmlparse(document 'x'); ! xmlparse ! -------------- ! x ! (1 row) ! SELECT xmlparse(document '&'); ! ERROR: invalid XML document ! DETAIL: line 1: xmlParseEntityRef: no name ! & ! ^ ! line 1: Opening and ending tag mismatch: invalidentity line 1 and abc ! & ! ^ SELECT xmlparse(document '&idontexist;'); ! ERROR: invalid XML document ! DETAIL: line 1: Entity 'idontexist' not defined ! &idontexist; ! ^ ! line 1: Opening and ending tag mismatch: undefinedentity line 1 and abc ! &idontexist; ! ^ SELECT xmlparse(document ''); ! xmlparse ! --------------------------- ! ! (1 row) ! SELECT xmlparse(document ''); ! xmlparse ! -------------------------------- ! ! (1 row) ! SELECT xmlparse(document '&idontexist;'); ! ERROR: invalid XML document ! DETAIL: line 1: Entity 'idontexist' not defined ! &idontexist; ! ^ ! line 1: Opening and ending tag mismatch: twoerrors line 1 and unbalanced ! &idontexist; ! ^ SELECT xmlparse(document ''); ! xmlparse ! --------------------- ! ! (1 row) ! SELECT xmlpi(name foo); ! xmlpi ! --------- ! ! (1 row) ! SELECT xmlpi(name xml); ! ERROR: invalid XML processing instruction ! DETAIL: XML processing instruction target name cannot be "xml". SELECT xmlpi(name xmlstuff); ! xmlpi ! -------------- ! ! (1 row) ! SELECT xmlpi(name foo, 'bar'); ! xmlpi ! ------------- ! ! (1 row) ! SELECT xmlpi(name foo, 'in?>valid'); ! ERROR: invalid XML processing instruction ! DETAIL: XML processing instruction cannot contain "?>". SELECT xmlpi(name foo, null); ! xmlpi ! ------- ! ! (1 row) ! SELECT xmlpi(name xml, null); ! ERROR: invalid XML processing instruction ! DETAIL: XML processing instruction target name cannot be "xml". SELECT xmlpi(name xmlstuff, null); ! xmlpi ! ------- ! ! (1 row) ! SELECT xmlpi(name "xml-stylesheet", 'href="mystyle.css" type="text/css"'); ! xmlpi ! ------------------------------------------------------- ! ! (1 row) ! SELECT xmlpi(name foo, ' bar'); ! xmlpi ! ------------- ! ! (1 row) ! SELECT xmlroot(xml '', version no value, standalone no value); ! xmlroot ! --------- ! ! (1 row) ! SELECT xmlroot(xml '', version '2.0'); ! xmlroot ! ----------------------------- ! ! (1 row) ! SELECT xmlroot(xml '', version no value, standalone yes); ! xmlroot ! ---------------------------------------------- ! ! (1 row) ! SELECT xmlroot(xml '', version no value, standalone yes); ! xmlroot ! ---------------------------------------------- ! ! (1 row) ! SELECT xmlroot(xmlroot(xml '', version '1.0'), version '1.1', standalone no); ! xmlroot ! --------------------------------------------- ! ! (1 row) ! SELECT xmlroot('', version no value, standalone no); ! xmlroot ! --------------------------------------------- ! ! (1 row) ! SELECT xmlroot('', version no value, standalone no value); ! xmlroot ! --------- ! ! (1 row) ! SELECT xmlroot('', version no value); ! xmlroot ! ---------------------------------------------- ! ! (1 row) ! SELECT xmlroot ( xmlelement ( name gazonk, --- 94,321 ---- SELECT xmlelement(name element, xmlattributes (1 as one, 'deuce' as two), 'content'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlelement(name element, xmlattributes ('unnamed and wrong')); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlelement(name element, xmlelement(name nested, 'stuff')); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlelement(name employee, xmlforest(name, age, salary as pay)) FROM emp; ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlelement(name duplicate, xmlattributes(1 as a, 2 as b, 3 as a)); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlelement(name num, 37); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlelement(name foo, text 'bar'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlelement(name foo, xml 'bar'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlelement(name foo, text 'br'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlelement(name foo, xml 'br'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlelement(name foo, array[1, 2, 3]); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SET xmlbinary TO base64; SELECT xmlelement(name foo, bytea 'bar'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SET xmlbinary TO hex; SELECT xmlelement(name foo, bytea 'bar'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlelement(name foo, xmlattributes(true as bar)); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlelement(name foo, xmlattributes('2009-04-09 00:24:37'::timestamp as bar)); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlelement(name foo, xmlattributes('infinity'::timestamp as bar)); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlelement(name foo, xmlattributes('<>&"''' as funny, xml 'br' as funnier)); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlparse(content 'abc'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlparse(content 'x'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlparse(content '&'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlparse(content '&idontexist;'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlparse(content ''); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlparse(content ''); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlparse(content '&idontexist;'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlparse(content ''); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlparse(document 'abc'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlparse(document 'x'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlparse(document '&'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlparse(document '&idontexist;'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlparse(document ''); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlparse(document ''); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlparse(document '&idontexist;'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlparse(document ''); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlpi(name foo); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlpi(name xml); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlpi(name xmlstuff); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlpi(name foo, 'bar'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlpi(name foo, 'in?>valid'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlpi(name foo, null); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlpi(name xml, null); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlpi(name xmlstuff, null); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlpi(name "xml-stylesheet", 'href="mystyle.css" type="text/css"'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlpi(name foo, ' bar'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlroot(xml '', version no value, standalone no value); ! ERROR: unsupported XML feature ! LINE 1: SELECT xmlroot(xml '', version no value, standalone no... ! ^ ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlroot(xml '', version '2.0'); ! ERROR: unsupported XML feature ! LINE 1: SELECT xmlroot(xml '', version '2.0'); ! ^ ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlroot(xml '', version no value, standalone yes); ! ERROR: unsupported XML feature ! LINE 1: SELECT xmlroot(xml '', version no value, standalone ye... ! ^ ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlroot(xml '', version no value, standalone yes); ! ERROR: unsupported XML feature ! LINE 1: SELECT xmlroot(xml '', version no... ! ^ ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlroot(xmlroot(xml '', version '1.0'), version '1.1', standalone no); ! ERROR: unsupported XML feature ! LINE 1: SELECT xmlroot(xmlroot(xml '', version '1.0'), version... ! ^ ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlroot('', version no value, standalone no); ! ERROR: unsupported XML feature ! LINE 1: SELECT xmlroot('... ! ^ ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlroot('', version no value, standalone no value); ! ERROR: unsupported XML feature ! LINE 1: SELECT xmlroot('... ! ^ ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlroot('', version no value); ! ERROR: unsupported XML feature ! LINE 1: SELECT xmlroot('... ! ^ ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlroot ( xmlelement ( name gazonk, *************** *** 418,478 **** SELECT xmlroot ( version '1.0', standalone yes ); ! xmlroot ! ------------------------------------------------------------------------------------------ ! foo ! (1 row) ! SELECT xmlserialize(content data as character varying(20)) FROM xmltest; - xmlserialize - -------------------- - one - two - (2 rows) - - SELECT xmlserialize(content 'good' as char(10)); xmlserialize -------------- ! good ! (1 row) SELECT xmlserialize(document 'bad' as text); ! ERROR: not an XML document SELECT xml 'bar' IS DOCUMENT; ! ?column? ! ---------- ! t ! (1 row) ! SELECT xml 'barfoo' IS DOCUMENT; ! ?column? ! ---------- ! f ! (1 row) ! SELECT xml '' IS NOT DOCUMENT; ! ?column? ! ---------- ! f ! (1 row) ! SELECT xml 'abc' IS NOT DOCUMENT; ! ?column? ! ---------- ! t ! (1 row) ! SELECT '<>' IS NOT DOCUMENT; ! ERROR: invalid XML content LINE 1: SELECT '<>' IS NOT DOCUMENT; ^ ! DETAIL: line 1: StartTag: invalid element name ! <> ! ^ SELECT xmlagg(data) FROM xmltest; ! xmlagg ! -------------------------------------- ! onetwo (1 row) SELECT xmlagg(data) FROM xmltest WHERE id > 10; --- 331,390 ---- version '1.0', standalone yes ); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlserialize(content data as character varying(20)) FROM xmltest; xmlserialize -------------- ! (0 rows) + SELECT xmlserialize(content 'good' as char(10)); + ERROR: unsupported XML feature + LINE 1: SELECT xmlserialize(content 'good' as char(10)); + ^ + DETAIL: This functionality requires the server to be built with libxml support. + HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlserialize(document 'bad' as text); ! ERROR: unsupported XML feature ! LINE 1: SELECT xmlserialize(document 'bad' as text); ! ^ ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xml 'bar' IS DOCUMENT; ! ERROR: unsupported XML feature ! LINE 1: SELECT xml 'bar' IS DOCUMENT; ! ^ ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xml 'barfoo' IS DOCUMENT; ! ERROR: unsupported XML feature ! LINE 1: SELECT xml 'barfoo' IS DOCUMENT; ! ^ ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xml '' IS NOT DOCUMENT; ! ERROR: unsupported XML feature ! LINE 1: SELECT xml '' IS NOT DOCUMENT; ! ^ ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xml 'abc' IS NOT DOCUMENT; ! ERROR: unsupported XML feature ! LINE 1: SELECT xml 'abc' IS NOT DOCUMENT; ! ^ ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT '<>' IS NOT DOCUMENT; ! ERROR: unsupported XML feature LINE 1: SELECT '<>' IS NOT DOCUMENT; ^ ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlagg(data) FROM xmltest; ! xmlagg ! -------- ! (1 row) SELECT xmlagg(data) FROM xmltest WHERE id > 10; *************** *** 482,683 **** SELECT xmlagg(data) FROM xmltest WHERE id > 10; (1 row) SELECT xmlelement(name employees, xmlagg(xmlelement(name name, name))) FROM emp; ! xmlelement ! -------------------------------------------------------------------------------------------------------------------------------- ! sharonsambilljeffcimlinda ! (1 row) ! -- Check mapping SQL identifier to XML name SELECT xmlpi(name ":::_xml_abc135.%-&_"); ! xmlpi ! ------------------------------------------------- ! ! (1 row) ! SELECT xmlpi(name "123"); ! xmlpi ! --------------- ! ! (1 row) ! PREPARE foo (xml) AS SELECT xmlconcat('', $1); SET XML OPTION DOCUMENT; EXECUTE foo (''); ! xmlconcat ! -------------- ! ! (1 row) ! EXECUTE foo ('bad'); ! ERROR: invalid XML document ! LINE 1: EXECUTE foo ('bad'); ! ^ ! DETAIL: line 1: Start tag expected, '<' not found ! bad ! ^ SET XML OPTION CONTENT; EXECUTE foo (''); ! xmlconcat ! -------------- ! ! (1 row) ! EXECUTE foo ('good'); ! xmlconcat ! ------------ ! good ! (1 row) ! -- Test backwards parsing CREATE VIEW xmlview1 AS SELECT xmlcomment('test'); CREATE VIEW xmlview2 AS SELECT xmlconcat('hello', 'you'); CREATE VIEW xmlview3 AS SELECT xmlelement(name element, xmlattributes (1 as ":one:", 'deuce' as two), 'content&'); CREATE VIEW xmlview4 AS SELECT xmlelement(name employee, xmlforest(name, age, salary as pay)) FROM emp; CREATE VIEW xmlview5 AS SELECT xmlparse(content 'x'); CREATE VIEW xmlview6 AS SELECT xmlpi(name foo, 'bar'); CREATE VIEW xmlview7 AS SELECT xmlroot(xml '', version no value, standalone yes); CREATE VIEW xmlview8 AS SELECT xmlserialize(content 'good' as char(10)); CREATE VIEW xmlview9 AS SELECT xmlserialize(content 'good' as text); SELECT table_name, view_definition FROM information_schema.views WHERE table_name LIKE 'xmlview%' ORDER BY 1; ! table_name | view_definition ! ------------+---------------------------------------------------------------------------------------------------------------------------- ! xmlview1 | SELECT xmlcomment('test'::text) AS xmlcomment; ! xmlview2 | SELECT XMLCONCAT('hello'::xml, 'you'::xml) AS "xmlconcat"; ! xmlview3 | SELECT XMLELEMENT(NAME element, XMLATTRIBUTES(1 AS ":one:", 'deuce' AS two), 'content&') AS "xmlelement"; ! xmlview4 | SELECT XMLELEMENT(NAME employee, XMLFOREST(emp.name AS name, emp.age AS age, emp.salary AS pay)) AS "xmlelement" FROM emp; ! xmlview5 | SELECT XMLPARSE(CONTENT 'x'::text STRIP WHITESPACE) AS "xmlparse"; ! xmlview6 | SELECT XMLPI(NAME foo, 'bar'::text) AS "xmlpi"; ! xmlview7 | SELECT XMLROOT(''::xml, VERSION NO VALUE, STANDALONE YES) AS "xmlroot"; ! xmlview8 | SELECT (XMLSERIALIZE(CONTENT 'good'::xml AS character(10)))::character(10) AS "xmlserialize"; ! xmlview9 | SELECT XMLSERIALIZE(CONTENT 'good'::xml AS text) AS "xmlserialize"; ! (9 rows) -- Text XPath expressions evaluation SELECT xpath('/value', data) FROM xmltest; ! xpath ! ---------------------- ! {one} ! {two} ! (2 rows) SELECT xpath(NULL, NULL) IS NULL FROM xmltest; ?column? ---------- ! t ! t ! (2 rows) SELECT xpath('', ''); ! ERROR: empty XPath expression ! CONTEXT: SQL function "xpath" statement 1 SELECT xpath('//text()', 'number one'); ! xpath ! ---------------- ! {"number one"} ! (1 row) ! SELECT xpath('//loc:piece/@id', 'number one', ARRAY[ARRAY['loc', 'http://127.0.0.1']]); ! xpath ! ------- ! {1,2} ! (1 row) ! SELECT xpath('//b', 'one two three etc'); ! xpath ! ------------------------- ! {two,etc} ! (1 row) ! SELECT xpath('//text()', '<'); ! xpath ! -------- ! {<} ! (1 row) ! SELECT xpath('//@value', ''); ! xpath ! -------- ! {<} ! (1 row) ! SELECT xpath('''<>''', ''); ! xpath ! --------------------------- ! {<<invalid>>} ! (1 row) ! SELECT xpath('count(//*)', ''); ! xpath ! ------- ! {3} ! (1 row) ! SELECT xpath('count(//*)=0', ''); ! xpath ! --------- ! {false} ! (1 row) ! SELECT xpath('count(//*)=3', ''); ! xpath ! -------- ! {true} ! (1 row) ! SELECT xpath('name(/*)', ''); ! xpath ! -------- ! {root} ! (1 row) ! SELECT xpath('/nosuchtag', ''); ! xpath ! ------- ! {} ! (1 row) ! -- Test xmlexists and xpath_exists SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY REF 'Bidford-on-AvonCwmbranBristol'); ! xmlexists ! ----------- ! f ! (1 row) ! SELECT xmlexists('//town[text() = ''Cwmbran'']' PASSING BY REF 'Bidford-on-AvonCwmbranBristol'); ! xmlexists ! ----------- ! t ! (1 row) ! SELECT xmlexists('count(/nosuchtag)' PASSING BY REF ''); ! xmlexists ! ----------- ! t ! (1 row) ! SELECT xpath_exists('//town[text() = ''Toronto'']','Bidford-on-AvonCwmbranBristol'::xml); ! xpath_exists ! -------------- ! f ! (1 row) ! SELECT xpath_exists('//town[text() = ''Cwmbran'']','Bidford-on-AvonCwmbranBristol'::xml); ! xpath_exists ! -------------- ! t ! (1 row) ! SELECT xpath_exists('count(/nosuchtag)', ''::xml); ! xpath_exists ! -------------- ! t ! (1 row) ! INSERT INTO xmltest VALUES (4, 'BudvarfreeCarlinglots'::xml); INSERT INTO xmltest VALUES (5, 'MolsonfreeCarlinglots'::xml); INSERT INTO xmltest VALUES (6, 'BudvarfreeCarlinglots'::xml); INSERT INTO xmltest VALUES (7, 'MolsonfreeCarlinglots'::xml); SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beer' PASSING data); count ------- --- 394,618 ---- (1 row) SELECT xmlelement(name employees, xmlagg(xmlelement(name name, name))) FROM emp; ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. -- Check mapping SQL identifier to XML name SELECT xmlpi(name ":::_xml_abc135.%-&_"); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xmlpi(name "123"); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. PREPARE foo (xml) AS SELECT xmlconcat('', $1); + ERROR: unsupported XML feature + LINE 1: PREPARE foo (xml) AS SELECT xmlconcat('', $1); + ^ + DETAIL: This functionality requires the server to be built with libxml support. + HINT: You need to rebuild PostgreSQL using --with-libxml. SET XML OPTION DOCUMENT; EXECUTE foo (''); ! ERROR: prepared statement "foo" does not exist EXECUTE foo ('bad'); ! ERROR: prepared statement "foo" does not exist SET XML OPTION CONTENT; EXECUTE foo (''); ! ERROR: prepared statement "foo" does not exist EXECUTE foo ('good'); ! ERROR: prepared statement "foo" does not exist -- Test backwards parsing CREATE VIEW xmlview1 AS SELECT xmlcomment('test'); CREATE VIEW xmlview2 AS SELECT xmlconcat('hello', 'you'); + ERROR: unsupported XML feature + LINE 1: CREATE VIEW xmlview2 AS SELECT xmlconcat('hello', 'you'); + ^ + DETAIL: This functionality requires the server to be built with libxml support. + HINT: You need to rebuild PostgreSQL using --with-libxml. CREATE VIEW xmlview3 AS SELECT xmlelement(name element, xmlattributes (1 as ":one:", 'deuce' as two), 'content&'); + ERROR: unsupported XML feature + DETAIL: This functionality requires the server to be built with libxml support. + HINT: You need to rebuild PostgreSQL using --with-libxml. CREATE VIEW xmlview4 AS SELECT xmlelement(name employee, xmlforest(name, age, salary as pay)) FROM emp; + ERROR: unsupported XML feature + DETAIL: This functionality requires the server to be built with libxml support. + HINT: You need to rebuild PostgreSQL using --with-libxml. CREATE VIEW xmlview5 AS SELECT xmlparse(content 'x'); CREATE VIEW xmlview6 AS SELECT xmlpi(name foo, 'bar'); + ERROR: unsupported XML feature + DETAIL: This functionality requires the server to be built with libxml support. + HINT: You need to rebuild PostgreSQL using --with-libxml. CREATE VIEW xmlview7 AS SELECT xmlroot(xml '', version no value, standalone yes); + ERROR: unsupported XML feature + LINE 1: CREATE VIEW xmlview7 AS SELECT xmlroot(xml '', version... + ^ + DETAIL: This functionality requires the server to be built with libxml support. + HINT: You need to rebuild PostgreSQL using --with-libxml. CREATE VIEW xmlview8 AS SELECT xmlserialize(content 'good' as char(10)); + ERROR: unsupported XML feature + LINE 1: ...EATE VIEW xmlview8 AS SELECT xmlserialize(content 'good' as ... + ^ + DETAIL: This functionality requires the server to be built with libxml support. + HINT: You need to rebuild PostgreSQL using --with-libxml. CREATE VIEW xmlview9 AS SELECT xmlserialize(content 'good' as text); + ERROR: unsupported XML feature + LINE 1: ...EATE VIEW xmlview9 AS SELECT xmlserialize(content 'good' as ... + ^ + DETAIL: This functionality requires the server to be built with libxml support. + HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT table_name, view_definition FROM information_schema.views WHERE table_name LIKE 'xmlview%' ORDER BY 1; ! table_name | view_definition ! ------------+-------------------------------------------------------------------------------- ! xmlview1 | SELECT xmlcomment('test'::text) AS xmlcomment; ! xmlview5 | SELECT XMLPARSE(CONTENT 'x'::text STRIP WHITESPACE) AS "xmlparse"; ! (2 rows) -- Text XPath expressions evaluation SELECT xpath('/value', data) FROM xmltest; ! xpath ! ------- ! (0 rows) SELECT xpath(NULL, NULL) IS NULL FROM xmltest; ?column? ---------- ! (0 rows) SELECT xpath('', ''); ! ERROR: unsupported XML feature ! LINE 1: SELECT xpath('', ''); ! ^ ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xpath('//text()', 'number one'); ! ERROR: unsupported XML feature ! LINE 1: SELECT xpath('//text()', 'number one', ARRAY[ARRAY['loc', 'http://127.0.0.1']]); ! ERROR: unsupported XML feature ! LINE 1: SELECT xpath('//loc:piece/@id', ''); ! ERROR: unsupported XML feature ! LINE 1: SELECT xpath('//@value', ''); ! ^ ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xpath('''<>''', ''); ! ERROR: unsupported XML feature ! LINE 1: SELECT xpath('''<>''', ''); ! ^ ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xpath('count(//*)', ''); ! ERROR: unsupported XML feature ! LINE 1: SELECT xpath('count(//*)', ''); ! ^ ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xpath('count(//*)=0', ''); ! ERROR: unsupported XML feature ! LINE 1: SELECT xpath('count(//*)=0', ''); ! ^ ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xpath('count(//*)=3', ''); ! ERROR: unsupported XML feature ! LINE 1: SELECT xpath('count(//*)=3', ''); ! ^ ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xpath('name(/*)', ''); ! ERROR: unsupported XML feature ! LINE 1: SELECT xpath('name(/*)', ''); ! ^ ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xpath('/nosuchtag', ''); ! ERROR: unsupported XML feature ! LINE 1: SELECT xpath('/nosuchtag', ''); ! ^ ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. -- Test xmlexists and xpath_exists SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY REF 'Bidford-on-AvonCwmbranBristol'); ! ERROR: unsupported XML feature ! LINE 1: ...sts('//town[text() = ''Toronto'']' PASSING BY REF 'Bidford-on-AvonCwmbranBristol'); ! ERROR: unsupported XML feature ! LINE 1: ...sts('//town[text() = ''Cwmbran'']' PASSING BY REF ''); ! ERROR: unsupported XML feature ! LINE 1: ...LECT xmlexists('count(/nosuchtag)' PASSING BY REF '')... ! ^ ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xpath_exists('//town[text() = ''Toronto'']','Bidford-on-AvonCwmbranBristol'::xml); ! ERROR: unsupported XML feature ! LINE 1: ...ELECT xpath_exists('//town[text() = ''Toronto'']','Bidford-on-AvonCwmbranBristol'::xml); ! ERROR: unsupported XML feature ! LINE 1: ...ELECT xpath_exists('//town[text() = ''Cwmbran'']',''::xml); ! ERROR: unsupported XML feature ! LINE 1: SELECT xpath_exists('count(/nosuchtag)', ''::xml); ! ^ ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. INSERT INTO xmltest VALUES (4, 'BudvarfreeCarlinglots'::xml); + ERROR: unsupported XML feature + LINE 1: INSERT INTO xmltest VALUES (4, 'BudvarMolsonfreeCarlinglots'::xml); + ERROR: unsupported XML feature + LINE 1: INSERT INTO xmltest VALUES (5, 'MolsonBudvarfreeCarlinglots'::xml); + ERROR: unsupported XML feature + LINE 1: INSERT INTO xmltest VALUES (6, 'MolsonfreeCarlinglots'::xml); + ERROR: unsupported XML feature + LINE 1: INSERT INTO xmltest VALUES (7, 'number one'); ! xml_is_well_formed ! -------------------- ! t ! (1 row) ! SELECT xml_is_well_formed('bar'); ! xml_is_well_formed ! -------------------- ! f ! (1 row) ! SELECT xml_is_well_formed('bar'); ! xml_is_well_formed ! -------------------- ! t ! (1 row) ! SELECT xml_is_well_formed('&'); ! xml_is_well_formed ! -------------------- ! f ! (1 row) ! SELECT xml_is_well_formed('&idontexist;'); ! xml_is_well_formed ! -------------------- ! f ! (1 row) ! SELECT xml_is_well_formed(''); ! xml_is_well_formed ! -------------------- ! t ! (1 row) ! SELECT xml_is_well_formed(''); ! xml_is_well_formed ! -------------------- ! t ! (1 row) ! SELECT xml_is_well_formed('&idontexist;'); ! xml_is_well_formed ! -------------------- ! f ! (1 row) ! SET xmloption TO CONTENT; SELECT xml_is_well_formed('abc'); ! xml_is_well_formed ! -------------------- ! t ! (1 row) ! -- Since xpath() deals with namespaces, it's a bit stricter about -- what's well-formed and what's not. If we don't obey these rules -- (i.e. ignore namespace-related errors from libxml), xpath() --- 678,765 ---- SELECT COUNT(id) FROM xmltest, query WHERE xmlexists(expr PASSING BY REF data); count ------- ! 0 (1 row) -- Test xml_is_well_formed and variants SELECT xml_is_well_formed_document('bar'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xml_is_well_formed_document('abc'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xml_is_well_formed_content('bar'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xml_is_well_formed_content('abc'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SET xmloption TO DOCUMENT; SELECT xml_is_well_formed('abc'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xml_is_well_formed('<>'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xml_is_well_formed(''); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xml_is_well_formed('bar'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xml_is_well_formed('barbaz'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xml_is_well_formed('number one'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xml_is_well_formed('bar'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xml_is_well_formed('bar'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xml_is_well_formed('&'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xml_is_well_formed('&idontexist;'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xml_is_well_formed(''); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xml_is_well_formed(''); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT xml_is_well_formed('&idontexist;'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SET xmloption TO CONTENT; SELECT xml_is_well_formed('abc'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. -- Since xpath() deals with namespaces, it's a bit stricter about -- what's well-formed and what's not. If we don't obey these rules -- (i.e. ignore namespace-related errors from libxml), xpath() *************** *** 875,918 **** SELECT xml_is_well_formed('abc'); -- error messages, we suppress the DETAIL in this test. \set VERBOSITY terse SELECT xpath('/*', ''); ! ERROR: could not parse XML document \set VERBOSITY default -- Again, the XML isn't well-formed for namespace purposes SELECT xpath('/*', ''); ! ERROR: could not parse XML document ! DETAIL: line 1: Namespace prefix nosuchprefix on tag is not defined ! ! ^ ! CONTEXT: SQL function "xpath" statement 1 -- XPath deprecates relative namespaces, but they're not supposed to -- throw an error, only a warning. SELECT xpath('/*', ''); ! WARNING: line 1: xmlns: URI relative is not absolute ! ! ^ ! CONTEXT: SQL function "xpath" statement 1 ! xpath ! -------------------------------------- ! {""} ! (1 row) ! -- External entity references should not leak filesystem information. SELECT XMLPARSE(DOCUMENT ']>&c;'); ! xmlparse ! ----------------------------------------------------------------- ! ]>&c; ! (1 row) ! SELECT XMLPARSE(DOCUMENT ']>&c;'); ! xmlparse ! ----------------------------------------------------------------------- ! ]>&c; ! (1 row) ! -- This might or might not load the requested DTD, but it mustn't throw error. SELECT XMLPARSE(DOCUMENT ' '); ! xmlparse ! ------------------------------------------------------------------------------------------------------------------------------------------------------ !   ! (1 row) ! --- 772,805 ---- -- error messages, we suppress the DETAIL in this test. \set VERBOSITY terse SELECT xpath('/*', ''); ! ERROR: unsupported XML feature at character 20 \set VERBOSITY default -- Again, the XML isn't well-formed for namespace purposes SELECT xpath('/*', ''); ! ERROR: unsupported XML feature ! LINE 1: SELECT xpath('/*', ''); ! ^ ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. -- XPath deprecates relative namespaces, but they're not supposed to -- throw an error, only a warning. SELECT xpath('/*', ''); ! ERROR: unsupported XML feature ! LINE 1: SELECT xpath('/*', ''); ! ^ ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. -- External entity references should not leak filesystem information. SELECT XMLPARSE(DOCUMENT ']>&c;'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. SELECT XMLPARSE(DOCUMENT ']>&c;'); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml. -- This might or might not load the requested DTD, but it mustn't throw error. SELECT XMLPARSE(DOCUMENT ' '); ! ERROR: unsupported XML feature ! DETAIL: This functionality requires the server to be built with libxml support. ! HINT: You need to rebuild PostgreSQL using --with-libxml.