prettyprint_v2.patch
application/octet-stream
Filename: prettyprint_v2.patch
Type: application/octet-stream
Part: 0
Message:
Re: pg_dump --pretty-print-views
Patch
Format: context
Series: patch v2
| File | + | − |
|---|---|---|
| src/backend/utils/adt/ruleutils.c | 3 | 0 |
| src/test/regress/expected/aggregates.out | 28 | 0 |
| src/test/regress/expected/collate.out | 10 | 0 |
| src/test/regress/expected/rules.out | 366 | 0 |
| src/test/regress/expected/with.out | 13 | 0 |
| src/test/regress/expected/xml.out | 513 | 0 |
*** 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, '<value>one</value>');
INSERT INTO xmltest VALUES (2, '<value>two</value>');
INSERT INTO xmltest VALUES (3, '<wrong');
! ERROR: invalid XML content
LINE 1: INSERT INTO xmltest VALUES (3, '<wrong');
^
! DETAIL: line 1: Couldn't find end of Start Tag wrong line 1
! <wrong
! ^
SELECT * FROM xmltest;
! id | data
! ----+--------------------
! 1 | <value>one</value>
! 2 | <value>two</value>
! (2 rows)
SELECT xmlcomment('test');
! xmlcomment
! -------------
! <!--test-->
! (1 row)
!
SELECT xmlcomment('-test');
! xmlcomment
! --------------
! <!---test-->
! (1 row)
!
SELECT xmlcomment('test-');
! ERROR: invalid XML comment
SELECT xmlcomment('--test');
! ERROR: invalid XML comment
SELECT xmlcomment('te st');
! xmlcomment
! --------------
! <!--te st-->
! (1 row)
!
SELECT xmlconcat(xmlcomment('hello'),
xmlelement(NAME qux, 'foo'),
xmlcomment('world'));
! xmlconcat
! ----------------------------------------
! <!--hello--><qux>foo</qux><!--world-->
! (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', '<syntax');
! ERROR: invalid XML content
LINE 1: SELECT xmlconcat('bad', '<syntax');
! ^
! DETAIL: line 1: Couldn't find end of Start Tag syntax line 1
! <syntax
! ^
SELECT xmlconcat('<foo/>', NULL, '<?xml version="1.1" standalone="no"?><bar/>');
! xmlconcat
! --------------
! <foo/><bar/>
! (1 row)
!
SELECT xmlconcat('<?xml version="1.1"?><foo/>', NULL, '<?xml version="1.1" standalone="no"?><bar/>');
! xmlconcat
! -----------------------------------
! <?xml version="1.1"?><foo/><bar/>
! (1 row)
!
SELECT xmlconcat(NULL);
xmlconcat
-----------
--- 3,84 ----
data xml
);
INSERT INTO xmltest VALUES (1, '<value>one</value>');
+ ERROR: unsupported XML feature
+ LINE 1: INSERT INTO xmltest VALUES (1, '<value>one</value>');
+ ^
+ 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, '<value>two</value>');
+ ERROR: unsupported XML feature
+ LINE 1: INSERT INTO xmltest VALUES (2, '<value>two</value>');
+ ^
+ 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, '<wrong');
! ERROR: unsupported XML feature
LINE 1: INSERT INTO xmltest VALUES (3, '<wrong');
^
! DETAIL: This functionality requires the server to be built with libxml support.
! HINT: You need to rebuild PostgreSQL using --with-libxml.
SELECT * FROM xmltest;
! id | data
! ----+------
! (0 rows)
SELECT xmlcomment('test');
! 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 xmlcomment('-test');
! 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 xmlcomment('test-');
! 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 xmlcomment('--test');
! 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 xmlcomment('te st');
! 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 xmlconcat(xmlcomment('hello'),
xmlelement(NAME qux, 'foo'),
xmlcomment('world'));
! 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 xmlconcat('hello', 'you');
! ERROR: unsupported XML feature
! LINE 1: 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.
SELECT xmlconcat(1, 2);
ERROR: argument of XMLCONCAT must be type xml, not type integer
LINE 1: SELECT xmlconcat(1, 2);
^
SELECT xmlconcat('bad', '<syntax');
! ERROR: unsupported XML feature
LINE 1: SELECT xmlconcat('bad', '<syntax');
! ^
! DETAIL: This functionality requires the server to be built with libxml support.
! HINT: You need to rebuild PostgreSQL using --with-libxml.
SELECT xmlconcat('<foo/>', NULL, '<?xml version="1.1" standalone="no"?><bar/>');
! ERROR: unsupported XML feature
! LINE 1: SELECT xmlconcat('<foo/>', NULL, '<?xml version="1.1" standa...
! ^
! DETAIL: This functionality requires the server to be built with libxml support.
! HINT: You need to rebuild PostgreSQL using --with-libxml.
SELECT xmlconcat('<?xml version="1.1"?><foo/>', NULL, '<?xml version="1.1" standalone="no"?><bar/>');
! ERROR: unsupported XML feature
! LINE 1: SELECT xmlconcat('<?xml version="1.1"?><foo/>', NULL, '<?xml...
! ^
! DETAIL: This functionality requires the server to be built with libxml support.
! HINT: You need to rebuild PostgreSQL using --with-libxml.
SELECT xmlconcat(NULL);
xmlconcat
-----------
***************
*** 92,408 **** SELECT xmlconcat(NULL, NULL);
SELECT xmlelement(name element,
xmlattributes (1 as one, 'deuce' as two),
'content');
! xmlelement
! ------------------------------------------------
! <element one="1" two="deuce">content</element>
! (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
! -------------------------------------------
! <element><nested>stuff</nested></element>
! (1 row)
!
SELECT xmlelement(name employee, xmlforest(name, age, salary as pay)) FROM emp;
! xmlelement
! ----------------------------------------------------------------------
! <employee><name>sharon</name><age>25</age><pay>1000</pay></employee>
! <employee><name>sam</name><age>30</age><pay>2000</pay></employee>
! <employee><name>bill</name><age>20</age><pay>1000</pay></employee>
! <employee><name>jeff</name><age>23</age><pay>600</pay></employee>
! <employee><name>cim</name><age>30</age><pay>400</pay></employee>
! <employee><name>linda</name><age>19</age><pay>100</pay></employee>
! (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
! ---------------
! <num>37</num>
! (1 row)
!
SELECT xmlelement(name foo, text 'bar');
! xmlelement
! ----------------
! <foo>bar</foo>
! (1 row)
!
SELECT xmlelement(name foo, xml 'bar');
! xmlelement
! ----------------
! <foo>bar</foo>
! (1 row)
!
SELECT xmlelement(name foo, text 'b<a/>r');
! xmlelement
! -------------------------
! <foo>b<a/>r</foo>
! (1 row)
!
SELECT xmlelement(name foo, xml 'b<a/>r');
! xmlelement
! -------------------
! <foo>b<a/>r</foo>
! (1 row)
!
SELECT xmlelement(name foo, array[1, 2, 3]);
! xmlelement
! -------------------------------------------------------------------------
! <foo><element>1</element><element>2</element><element>3</element></foo>
! (1 row)
!
SET xmlbinary TO base64;
SELECT xmlelement(name foo, bytea 'bar');
! xmlelement
! -----------------
! <foo>YmFy</foo>
! (1 row)
!
SET xmlbinary TO hex;
SELECT xmlelement(name foo, bytea 'bar');
! xmlelement
! -------------------
! <foo>626172</foo>
! (1 row)
!
SELECT xmlelement(name foo, xmlattributes(true as bar));
! xmlelement
! -------------------
! <foo bar="true"/>
! (1 row)
!
SELECT xmlelement(name foo, xmlattributes('2009-04-09 00:24:37'::timestamp as bar));
! xmlelement
! ----------------------------------
! <foo bar="2009-04-09T00:24:37"/>
! (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 'b<a/>r' as funnier));
! xmlelement
! ------------------------------------------------------------
! <foo funny="<>&"'" funnier="b<a/>r"/>
! (1 row)
!
SELECT xmlparse(content 'abc');
! xmlparse
! ----------
! abc
! (1 row)
!
SELECT xmlparse(content '<abc>x</abc>');
! xmlparse
! --------------
! <abc>x</abc>
! (1 row)
!
SELECT xmlparse(content '<invalidentity>&</invalidentity>');
! ERROR: invalid XML content
! DETAIL: line 1: xmlParseEntityRef: no name
! <invalidentity>&</invalidentity>
! ^
! line 1: chunk is not well balanced
! <invalidentity>&</invalidentity>
! ^
SELECT xmlparse(content '<undefinedentity>&idontexist;</undefinedentity>');
! ERROR: invalid XML content
! DETAIL: line 1: Entity 'idontexist' not defined
! <undefinedentity>&idontexist;</undefinedentity>
! ^
! line 1: chunk is not well balanced
! <undefinedentity>&idontexist;</undefinedentity>
! ^
SELECT xmlparse(content '<invalidns xmlns=''<''/>');
! xmlparse
! ---------------------------
! <invalidns xmlns='<'/>
! (1 row)
!
SELECT xmlparse(content '<relativens xmlns=''relative''/>');
! xmlparse
! --------------------------------
! <relativens xmlns='relative'/>
! (1 row)
!
SELECT xmlparse(content '<twoerrors>&idontexist;</unbalanced>');
! ERROR: invalid XML content
! DETAIL: line 1: Entity 'idontexist' not defined
! <twoerrors>&idontexist;</unbalanced>
! ^
! line 1: Opening and ending tag mismatch: twoerrors line 1 and unbalanced
! <twoerrors>&idontexist;</unbalanced>
! ^
! line 1: chunk is not well balanced
! <twoerrors>&idontexist;</unbalanced>
! ^
SELECT xmlparse(content '<nosuchprefix:tag/>');
! xmlparse
! ---------------------
! <nosuchprefix:tag/>
! (1 row)
!
SELECT xmlparse(document 'abc');
! ERROR: invalid XML document
! DETAIL: line 1: Start tag expected, '<' not found
! abc
! ^
SELECT xmlparse(document '<abc>x</abc>');
! xmlparse
! --------------
! <abc>x</abc>
! (1 row)
!
SELECT xmlparse(document '<invalidentity>&</abc>');
! ERROR: invalid XML document
! DETAIL: line 1: xmlParseEntityRef: no name
! <invalidentity>&</abc>
! ^
! line 1: Opening and ending tag mismatch: invalidentity line 1 and abc
! <invalidentity>&</abc>
! ^
SELECT xmlparse(document '<undefinedentity>&idontexist;</abc>');
! ERROR: invalid XML document
! DETAIL: line 1: Entity 'idontexist' not defined
! <undefinedentity>&idontexist;</abc>
! ^
! line 1: Opening and ending tag mismatch: undefinedentity line 1 and abc
! <undefinedentity>&idontexist;</abc>
! ^
SELECT xmlparse(document '<invalidns xmlns=''<''/>');
! xmlparse
! ---------------------------
! <invalidns xmlns='<'/>
! (1 row)
!
SELECT xmlparse(document '<relativens xmlns=''relative''/>');
! xmlparse
! --------------------------------
! <relativens xmlns='relative'/>
! (1 row)
!
SELECT xmlparse(document '<twoerrors>&idontexist;</unbalanced>');
! ERROR: invalid XML document
! DETAIL: line 1: Entity 'idontexist' not defined
! <twoerrors>&idontexist;</unbalanced>
! ^
! line 1: Opening and ending tag mismatch: twoerrors line 1 and unbalanced
! <twoerrors>&idontexist;</unbalanced>
! ^
SELECT xmlparse(document '<nosuchprefix:tag/>');
! xmlparse
! ---------------------
! <nosuchprefix:tag/>
! (1 row)
!
SELECT xmlpi(name foo);
! xmlpi
! ---------
! <?foo?>
! (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
! --------------
! <?xmlstuff?>
! (1 row)
!
SELECT xmlpi(name foo, 'bar');
! xmlpi
! -------------
! <?foo bar?>
! (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
! -------------------------------------------------------
! <?xml-stylesheet href="mystyle.css" type="text/css"?>
! (1 row)
!
SELECT xmlpi(name foo, ' bar');
! xmlpi
! -------------
! <?foo bar?>
! (1 row)
!
SELECT xmlroot(xml '<foo/>', version no value, standalone no value);
! xmlroot
! ---------
! <foo/>
! (1 row)
!
SELECT xmlroot(xml '<foo/>', version '2.0');
! xmlroot
! -----------------------------
! <?xml version="2.0"?><foo/>
! (1 row)
!
SELECT xmlroot(xml '<foo/>', version no value, standalone yes);
! xmlroot
! ----------------------------------------------
! <?xml version="1.0" standalone="yes"?><foo/>
! (1 row)
!
SELECT xmlroot(xml '<?xml version="1.1"?><foo/>', version no value, standalone yes);
! xmlroot
! ----------------------------------------------
! <?xml version="1.0" standalone="yes"?><foo/>
! (1 row)
!
SELECT xmlroot(xmlroot(xml '<foo/>', version '1.0'), version '1.1', standalone no);
! xmlroot
! ---------------------------------------------
! <?xml version="1.1" standalone="no"?><foo/>
! (1 row)
!
SELECT xmlroot('<?xml version="1.1" standalone="yes"?><foo/>', version no value, standalone no);
! xmlroot
! ---------------------------------------------
! <?xml version="1.0" standalone="no"?><foo/>
! (1 row)
!
SELECT xmlroot('<?xml version="1.1" standalone="yes"?><foo/>', version no value, standalone no value);
! xmlroot
! ---------
! <foo/>
! (1 row)
!
SELECT xmlroot('<?xml version="1.1" standalone="yes"?><foo/>', version no value);
! xmlroot
! ----------------------------------------------
! <?xml version="1.0" standalone="yes"?><foo/>
! (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 'b<a/>r');
! 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 'b<a/>r');
! 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 'b<a/>r' 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 '<abc>x</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 '<invalidentity>&</invalidentity>');
! 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 '<undefinedentity>&idontexist;</undefinedentity>');
! 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 '<invalidns xmlns=''<''/>');
! 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 '<relativens xmlns=''relative''/>');
! 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 '<twoerrors>&idontexist;</unbalanced>');
! 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 '<nosuchprefix:tag/>');
! 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 '<abc>x</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 '<invalidentity>&</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 '<undefinedentity>&idontexist;</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 '<invalidns xmlns=''<''/>');
! 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 '<relativens xmlns=''relative''/>');
! 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 '<twoerrors>&idontexist;</unbalanced>');
! 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 '<nosuchprefix:tag/>');
! 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 '<foo/>', version no value, standalone no value);
! ERROR: unsupported XML feature
! LINE 1: SELECT xmlroot(xml '<foo/>', 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 '<foo/>', version '2.0');
! ERROR: unsupported XML feature
! LINE 1: SELECT xmlroot(xml '<foo/>', 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 '<foo/>', version no value, standalone yes);
! ERROR: unsupported XML feature
! LINE 1: SELECT xmlroot(xml '<foo/>', 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 '<?xml version="1.1"?><foo/>', version no value, standalone yes);
! ERROR: unsupported XML feature
! LINE 1: SELECT xmlroot(xml '<?xml version="1.1"?><foo/>', 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 '<foo/>', version '1.0'), version '1.1', standalone no);
! ERROR: unsupported XML feature
! LINE 1: SELECT xmlroot(xmlroot(xml '<foo/>', 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('<?xml version="1.1" standalone="yes"?><foo/>', version no value, standalone no);
! ERROR: unsupported XML feature
! LINE 1: SELECT xmlroot('<?xml version="1.1" standalone="yes"?><foo/>...
! ^
! 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="1.1" standalone="yes"?><foo/>', version no value, standalone no value);
! ERROR: unsupported XML feature
! LINE 1: SELECT xmlroot('<?xml version="1.1" standalone="yes"?><foo/>...
! ^
! 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="1.1" standalone="yes"?><foo/>', version no value);
! ERROR: unsupported XML feature
! LINE 1: SELECT xmlroot('<?xml version="1.1" standalone="yes"?><foo/>...
! ^
! 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
! ------------------------------------------------------------------------------------------
! <?xml version="1.0" standalone="yes"?><gazonk name="val" num="2"><qux>foo</qux></gazonk>
! (1 row)
!
SELECT xmlserialize(content data as character varying(20)) FROM xmltest;
- xmlserialize
- --------------------
- <value>one</value>
- <value>two</value>
- (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 '<foo>bar</foo>' IS DOCUMENT;
! ?column?
! ----------
! t
! (1 row)
!
SELECT xml '<foo>bar</foo><bar>foo</bar>' IS DOCUMENT;
! ?column?
! ----------
! f
! (1 row)
!
SELECT xml '<abc/>' 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
! --------------------------------------
! <value>one</value><value>two</value>
(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 '<foo>bar</foo>' IS DOCUMENT;
! ERROR: unsupported XML feature
! LINE 1: SELECT xml '<foo>bar</foo>' 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 '<foo>bar</foo><bar>foo</bar>' IS DOCUMENT;
! ERROR: unsupported XML feature
! LINE 1: SELECT xml '<foo>bar</foo><bar>foo</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 '<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 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
! --------------------------------------------------------------------------------------------------------------------------------
! <employees><name>sharon</name><name>sam</name><name>bill</name><name>jeff</name><name>cim</name><name>linda</name></employees>
! (1 row)
!
-- Check mapping SQL identifier to XML name
SELECT xmlpi(name ":::_xml_abc135.%-&_");
! xmlpi
! -------------------------------------------------
! <?_x003A_::_x005F_xml_abc135._x0025_-_x0026__?>
! (1 row)
!
SELECT xmlpi(name "123");
! xmlpi
! ---------------
! <?_x0031_23?>
! (1 row)
!
PREPARE foo (xml) AS SELECT xmlconcat('<foo/>', $1);
SET XML OPTION DOCUMENT;
EXECUTE foo ('<bar/>');
! xmlconcat
! --------------
! <foo/><bar/>
! (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 ('<bar/>');
! xmlconcat
! --------------
! <foo/><bar/>
! (1 row)
!
EXECUTE foo ('good');
! xmlconcat
! ------------
! <foo/>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 '<abc>x</abc>');
CREATE VIEW xmlview6 AS SELECT xmlpi(name foo, 'bar');
CREATE VIEW xmlview7 AS SELECT xmlroot(xml '<foo/>', 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 '<abc>x</abc>'::text STRIP WHITESPACE) AS "xmlparse";
! xmlview6 | SELECT XMLPI(NAME foo, 'bar'::text) AS "xmlpi";
! xmlview7 | SELECT XMLROOT('<foo/>'::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
! ----------------------
! {<value>one</value>}
! {<value>two</value>}
! (2 rows)
SELECT xpath(NULL, NULL) IS NULL FROM xmltest;
?column?
----------
! t
! t
! (2 rows)
SELECT xpath('', '<!-- error -->');
! ERROR: empty XPath expression
! CONTEXT: SQL function "xpath" statement 1
SELECT xpath('//text()', '<local:data xmlns:local="http://127.0.0.1"><local:piece id="1">number one</local:piece><local:piece id="2" /></local:data>');
! xpath
! ----------------
! {"number one"}
! (1 row)
!
SELECT xpath('//loc:piece/@id', '<local:data xmlns:local="http://127.0.0.1"><local:piece id="1">number one</local:piece><local:piece id="2" /></local:data>', ARRAY[ARRAY['loc', 'http://127.0.0.1']]);
! xpath
! -------
! {1,2}
! (1 row)
!
SELECT xpath('//b', '<a>one <b>two</b> three <b>etc</b></a>');
! xpath
! -------------------------
! {<b>two</b>,<b>etc</b>}
! (1 row)
!
SELECT xpath('//text()', '<root><</root>');
! xpath
! --------
! {<}
! (1 row)
!
SELECT xpath('//@value', '<root value="<"/>');
! xpath
! --------
! {<}
! (1 row)
!
SELECT xpath('''<<invalid>>''', '<root/>');
! xpath
! ---------------------------
! {<<invalid>>}
! (1 row)
!
SELECT xpath('count(//*)', '<root><sub/><sub/></root>');
! xpath
! -------
! {3}
! (1 row)
!
SELECT xpath('count(//*)=0', '<root><sub/><sub/></root>');
! xpath
! ---------
! {false}
! (1 row)
!
SELECT xpath('count(//*)=3', '<root><sub/><sub/></root>');
! xpath
! --------
! {true}
! (1 row)
!
SELECT xpath('name(/*)', '<root><sub/><sub/></root>');
! xpath
! --------
! {root}
! (1 row)
!
SELECT xpath('/nosuchtag', '<root/>');
! xpath
! -------
! {}
! (1 row)
!
-- Test xmlexists and xpath_exists
SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY REF '<towns><town>Bidford-on-Avon</town><town>Cwmbran</town><town>Bristol</town></towns>');
! xmlexists
! -----------
! f
! (1 row)
!
SELECT xmlexists('//town[text() = ''Cwmbran'']' PASSING BY REF '<towns><town>Bidford-on-Avon</town><town>Cwmbran</town><town>Bristol</town></towns>');
! xmlexists
! -----------
! t
! (1 row)
!
SELECT xmlexists('count(/nosuchtag)' PASSING BY REF '<root/>');
! xmlexists
! -----------
! t
! (1 row)
!
SELECT xpath_exists('//town[text() = ''Toronto'']','<towns><town>Bidford-on-Avon</town><town>Cwmbran</town><town>Bristol</town></towns>'::xml);
! xpath_exists
! --------------
! f
! (1 row)
!
SELECT xpath_exists('//town[text() = ''Cwmbran'']','<towns><town>Bidford-on-Avon</town><town>Cwmbran</town><town>Bristol</town></towns>'::xml);
! xpath_exists
! --------------
! t
! (1 row)
!
SELECT xpath_exists('count(/nosuchtag)', '<root/>'::xml);
! xpath_exists
! --------------
! t
! (1 row)
!
INSERT INTO xmltest VALUES (4, '<menu><beers><name>Budvar</name><cost>free</cost><name>Carling</name><cost>lots</cost></beers></menu>'::xml);
INSERT INTO xmltest VALUES (5, '<menu><beers><name>Molson</name><cost>free</cost><name>Carling</name><cost>lots</cost></beers></menu>'::xml);
INSERT INTO xmltest VALUES (6, '<myns:menu xmlns:myns="http://myns.com"><myns:beers><myns:name>Budvar</myns:name><myns:cost>free</myns:cost><myns:name>Carling</myns:name><myns:cost>lots</myns:cost></myns:beers></myns:menu>'::xml);
INSERT INTO xmltest VALUES (7, '<myns:menu xmlns:myns="http://myns.com"><myns:beers><myns:name>Molson</myns:name><myns:cost>free</myns:cost><myns:name>Carling</myns:name><myns:cost>lots</myns:cost></myns:beers></myns:menu>'::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('<foo/>', $1);
+ ERROR: unsupported XML feature
+ LINE 1: PREPARE foo (xml) AS SELECT xmlconcat('<foo/>', $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 ('<bar/>');
! ERROR: prepared statement "foo" does not exist
EXECUTE foo ('bad');
! ERROR: prepared statement "foo" does not exist
SET XML OPTION CONTENT;
EXECUTE foo ('<bar/>');
! 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 '<abc>x</abc>');
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 '<foo/>', version no value, standalone yes);
+ ERROR: unsupported XML feature
+ LINE 1: CREATE VIEW xmlview7 AS SELECT xmlroot(xml '<foo/>', 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 '<abc>x</abc>'::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 -->');
! ERROR: unsupported XML feature
! LINE 1: SELECT xpath('', '<!-- error -->');
! ^
! DETAIL: This functionality requires the server to be built with libxml support.
! HINT: You need to rebuild PostgreSQL using --with-libxml.
SELECT xpath('//text()', '<local:data xmlns:local="http://127.0.0.1"><local:piece id="1">number one</local:piece><local:piece id="2" /></local:data>');
! ERROR: unsupported XML feature
! LINE 1: SELECT xpath('//text()', '<local:data xmlns:local="http://12...
! ^
! DETAIL: This functionality requires the server to be built with libxml support.
! HINT: You need to rebuild PostgreSQL using --with-libxml.
SELECT xpath('//loc:piece/@id', '<local:data xmlns:local="http://127.0.0.1"><local:piece id="1">number one</local:piece><local:piece id="2" /></local:data>', ARRAY[ARRAY['loc', 'http://127.0.0.1']]);
! ERROR: unsupported XML feature
! LINE 1: SELECT xpath('//loc:piece/@id', '<local:data xmlns:local="ht...
! ^
! DETAIL: This functionality requires the server to be built with libxml support.
! HINT: You need to rebuild PostgreSQL using --with-libxml.
SELECT xpath('//b', '<a>one <b>two</b> three <b>etc</b></a>');
! ERROR: unsupported XML feature
! LINE 1: SELECT xpath('//b', '<a>one <b>two</b> three <b>etc</b></a>'...
! ^
! DETAIL: This functionality requires the server to be built with libxml support.
! HINT: You need to rebuild PostgreSQL using --with-libxml.
SELECT xpath('//text()', '<root><</root>');
! ERROR: unsupported XML feature
! LINE 1: SELECT xpath('//text()', '<root><</root>');
! ^
! DETAIL: This functionality requires the server to be built with libxml support.
! HINT: You need to rebuild PostgreSQL using --with-libxml.
SELECT xpath('//@value', '<root value="<"/>');
! ERROR: unsupported XML feature
! LINE 1: SELECT xpath('//@value', '<root value="<"/>');
! ^
! DETAIL: This functionality requires the server to be built with libxml support.
! HINT: You need to rebuild PostgreSQL using --with-libxml.
SELECT xpath('''<<invalid>>''', '<root/>');
! ERROR: unsupported XML feature
! LINE 1: SELECT xpath('''<<invalid>>''', '<root/>');
! ^
! DETAIL: This functionality requires the server to be built with libxml support.
! HINT: You need to rebuild PostgreSQL using --with-libxml.
SELECT xpath('count(//*)', '<root><sub/><sub/></root>');
! ERROR: unsupported XML feature
! LINE 1: SELECT xpath('count(//*)', '<root><sub/><sub/></root>');
! ^
! 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', '<root><sub/><sub/></root>');
! ERROR: unsupported XML feature
! LINE 1: SELECT xpath('count(//*)=0', '<root><sub/><sub/></root>');
! ^
! 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', '<root><sub/><sub/></root>');
! ERROR: unsupported XML feature
! LINE 1: SELECT xpath('count(//*)=3', '<root><sub/><sub/></root>');
! ^
! DETAIL: This functionality requires the server to be built with libxml support.
! HINT: You need to rebuild PostgreSQL using --with-libxml.
SELECT xpath('name(/*)', '<root><sub/><sub/></root>');
! ERROR: unsupported XML feature
! LINE 1: SELECT xpath('name(/*)', '<root><sub/><sub/></root>');
! ^
! DETAIL: This functionality requires the server to be built with libxml support.
! HINT: You need to rebuild PostgreSQL using --with-libxml.
SELECT xpath('/nosuchtag', '<root/>');
! ERROR: unsupported XML feature
! LINE 1: SELECT xpath('/nosuchtag', '<root/>');
! ^
! 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 '<towns><town>Bidford-on-Avon</town><town>Cwmbran</town><town>Bristol</town></towns>');
! ERROR: unsupported XML feature
! LINE 1: ...sts('//town[text() = ''Toronto'']' PASSING BY REF '<towns><t...
! ^
! DETAIL: This functionality requires the server to be built with libxml support.
! HINT: You need to rebuild PostgreSQL using --with-libxml.
SELECT xmlexists('//town[text() = ''Cwmbran'']' PASSING BY REF '<towns><town>Bidford-on-Avon</town><town>Cwmbran</town><town>Bristol</town></towns>');
! ERROR: unsupported XML feature
! LINE 1: ...sts('//town[text() = ''Cwmbran'']' PASSING BY REF '<towns><t...
! ^
! DETAIL: This functionality requires the server to be built with libxml support.
! HINT: You need to rebuild PostgreSQL using --with-libxml.
SELECT xmlexists('count(/nosuchtag)' PASSING BY REF '<root/>');
! ERROR: unsupported XML feature
! LINE 1: ...LECT xmlexists('count(/nosuchtag)' PASSING BY REF '<root/>')...
! ^
! 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'']','<towns><town>Bidford-on-Avon</town><town>Cwmbran</town><town>Bristol</town></towns>'::xml);
! ERROR: unsupported XML feature
! LINE 1: ...ELECT xpath_exists('//town[text() = ''Toronto'']','<towns><t...
! ^
! 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() = ''Cwmbran'']','<towns><town>Bidford-on-Avon</town><town>Cwmbran</town><town>Bristol</town></towns>'::xml);
! ERROR: unsupported XML feature
! LINE 1: ...ELECT xpath_exists('//town[text() = ''Cwmbran'']','<towns><t...
! ^
! DETAIL: This functionality requires the server to be built with libxml support.
! HINT: You need to rebuild PostgreSQL using --with-libxml.
SELECT xpath_exists('count(/nosuchtag)', '<root/>'::xml);
! ERROR: unsupported XML feature
! LINE 1: SELECT xpath_exists('count(/nosuchtag)', '<root/>'::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, '<menu><beers><name>Budvar</name><cost>free</cost><name>Carling</name><cost>lots</cost></beers></menu>'::xml);
+ ERROR: unsupported XML feature
+ LINE 1: INSERT INTO xmltest VALUES (4, '<menu><beers><name>Budvar</n...
+ ^
+ 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 (5, '<menu><beers><name>Molson</name><cost>free</cost><name>Carling</name><cost>lots</cost></beers></menu>'::xml);
+ ERROR: unsupported XML feature
+ LINE 1: INSERT INTO xmltest VALUES (5, '<menu><beers><name>Molson</n...
+ ^
+ 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 (6, '<myns:menu xmlns:myns="http://myns.com"><myns:beers><myns:name>Budvar</myns:name><myns:cost>free</myns:cost><myns:name>Carling</myns:name><myns:cost>lots</myns:cost></myns:beers></myns:menu>'::xml);
+ ERROR: unsupported XML feature
+ LINE 1: INSERT INTO xmltest VALUES (6, '<myns:menu xmlns:myns="http:...
+ ^
+ 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 (7, '<myns:menu xmlns:myns="http://myns.com"><myns:beers><myns:name>Molson</myns:name><myns:cost>free</myns:cost><myns:name>Carling</myns:name><myns:cost>lots</myns:cost></myns:beers></myns:menu>'::xml);
+ ERROR: unsupported XML feature
+ LINE 1: INSERT INTO xmltest VALUES (7, '<myns:menu xmlns:myns="http:...
+ ^
+ DETAIL: This functionality requires the server to be built with libxml support.
+ HINT: You need to rebuild PostgreSQL using --with-libxml.
SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beer' PASSING data);
count
-------
***************
*** 693,705 **** SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beer' PASSING BY REF data B
SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beers' PASSING BY REF data);
count
-------
! 2
(1 row)
SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beers/name[text() = ''Molson'']' PASSING BY REF data);
count
-------
! 1
(1 row)
SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/menu/beer',data);
--- 628,640 ----
SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beers' PASSING BY REF data);
count
-------
! 0
(1 row)
SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beers/name[text() = ''Molson'']' PASSING BY REF data);
count
-------
! 0
(1 row)
SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/menu/beer',data);
***************
*** 711,723 **** SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/menu/beer',data);
SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/menu/beers',data);
count
-------
! 2
(1 row)
SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/menu/beers/name[text() = ''Molson'']',data);
count
-------
! 1
(1 row)
SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/myns:menu/myns:beer',data,ARRAY[ARRAY['myns','http://myns.com']]);
--- 646,658 ----
SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/menu/beers',data);
count
-------
! 0
(1 row)
SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/menu/beers/name[text() = ''Molson'']',data);
count
-------
! 0
(1 row)
SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/myns:menu/myns:beer',data,ARRAY[ARRAY['myns','http://myns.com']]);
***************
*** 729,741 **** SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/myns:menu/myns:beer',data,ARR
SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/myns:menu/myns:beers',data,ARRAY[ARRAY['myns','http://myns.com']]);
count
-------
! 2
(1 row)
SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/myns:menu/myns:beers/myns:name[text() = ''Molson'']',data,ARRAY[ARRAY['myns','http://myns.com']]);
count
-------
! 1
(1 row)
CREATE TABLE query ( expr TEXT );
--- 664,676 ----
SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/myns:menu/myns:beers',data,ARRAY[ARRAY['myns','http://myns.com']]);
count
-------
! 0
(1 row)
SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/myns:menu/myns:beers/myns:name[text() = ''Molson'']',data,ARRAY[ARRAY['myns','http://myns.com']]);
count
-------
! 0
(1 row)
CREATE TABLE query ( expr TEXT );
***************
*** 743,868 **** INSERT INTO query VALUES ('/menu/beers/cost[text() = ''lots'']');
SELECT COUNT(id) FROM xmltest, query WHERE xmlexists(expr PASSING BY REF data);
count
-------
! 2
(1 row)
-- Test xml_is_well_formed and variants
SELECT xml_is_well_formed_document('<foo>bar</foo>');
! xml_is_well_formed_document
! -----------------------------
! t
! (1 row)
!
SELECT xml_is_well_formed_document('abc');
! xml_is_well_formed_document
! -----------------------------
! f
! (1 row)
!
SELECT xml_is_well_formed_content('<foo>bar</foo>');
! xml_is_well_formed_content
! ----------------------------
! t
! (1 row)
!
SELECT xml_is_well_formed_content('abc');
! xml_is_well_formed_content
! ----------------------------
! t
! (1 row)
!
SET xmloption TO DOCUMENT;
SELECT xml_is_well_formed('abc');
! xml_is_well_formed
! --------------------
! f
! (1 row)
!
SELECT xml_is_well_formed('<>');
! xml_is_well_formed
! --------------------
! f
! (1 row)
!
SELECT xml_is_well_formed('<abc/>');
! xml_is_well_formed
! --------------------
! t
! (1 row)
!
SELECT xml_is_well_formed('<foo>bar</foo>');
! xml_is_well_formed
! --------------------
! t
! (1 row)
!
SELECT xml_is_well_formed('<foo>bar</foo');
! xml_is_well_formed
! --------------------
! f
! (1 row)
!
SELECT xml_is_well_formed('<foo><bar>baz</foo>');
! xml_is_well_formed
! --------------------
! f
! (1 row)
!
SELECT xml_is_well_formed('<local:data xmlns:local="http://127.0.0.1"><local:piece id="1">number one</local:piece><local:piece id="2" /></local:data>');
! xml_is_well_formed
! --------------------
! t
! (1 row)
!
SELECT xml_is_well_formed('<pg:foo xmlns:pg="http://postgresql.org/stuff">bar</my:foo>');
! xml_is_well_formed
! --------------------
! f
! (1 row)
!
SELECT xml_is_well_formed('<pg:foo xmlns:pg="http://postgresql.org/stuff">bar</pg:foo>');
! xml_is_well_formed
! --------------------
! t
! (1 row)
!
SELECT xml_is_well_formed('<invalidentity>&</abc>');
! xml_is_well_formed
! --------------------
! f
! (1 row)
!
SELECT xml_is_well_formed('<undefinedentity>&idontexist;</abc>');
! xml_is_well_formed
! --------------------
! f
! (1 row)
!
SELECT xml_is_well_formed('<invalidns xmlns=''<''/>');
! xml_is_well_formed
! --------------------
! t
! (1 row)
!
SELECT xml_is_well_formed('<relativens xmlns=''relative''/>');
! xml_is_well_formed
! --------------------
! t
! (1 row)
!
SELECT xml_is_well_formed('<twoerrors>&idontexist;</unbalanced>');
! 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('<foo>bar</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 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('<foo>bar</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 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('<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('<foo>bar</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 xml_is_well_formed('<foo>bar</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 xml_is_well_formed('<foo><bar>baz</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 xml_is_well_formed('<local:data xmlns:local="http://127.0.0.1"><local:piece id="1">number one</local:piece><local:piece id="2" /></local:data>');
! 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('<pg:foo xmlns:pg="http://postgresql.org/stuff">bar</my: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 xml_is_well_formed('<pg:foo xmlns:pg="http://postgresql.org/stuff">bar</pg: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 xml_is_well_formed('<invalidentity>&</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('<undefinedentity>&idontexist;</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('<invalidns xmlns=''<''/>');
! 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('<relativens xmlns=''relative''/>');
! 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('<twoerrors>&idontexist;</unbalanced>');
! 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('/*', '<invalidns xmlns=''<''/>');
! ERROR: could not parse XML document
\set VERBOSITY default
-- Again, the XML isn't well-formed for namespace purposes
SELECT xpath('/*', '<nosuchprefix:tag/>');
! ERROR: could not parse XML document
! DETAIL: line 1: Namespace prefix nosuchprefix on tag is not defined
! <nosuchprefix:tag/>
! ^
! CONTEXT: SQL function "xpath" statement 1
-- XPath deprecates relative namespaces, but they're not supposed to
-- throw an error, only a warning.
SELECT xpath('/*', '<relativens xmlns=''relative''/>');
! WARNING: line 1: xmlns: URI relative is not absolute
! <relativens xmlns='relative'/>
! ^
! CONTEXT: SQL function "xpath" statement 1
! xpath
! --------------------------------------
! {"<relativens xmlns=\"relative\"/>"}
! (1 row)
!
-- External entity references should not leak filesystem information.
SELECT XMLPARSE(DOCUMENT '<!DOCTYPE foo [<!ENTITY c SYSTEM "/etc/passwd">]><foo>&c;</foo>');
! xmlparse
! -----------------------------------------------------------------
! <!DOCTYPE foo [<!ENTITY c SYSTEM "/etc/passwd">]><foo>&c;</foo>
! (1 row)
!
SELECT XMLPARSE(DOCUMENT '<!DOCTYPE foo [<!ENTITY c SYSTEM "/etc/no.such.file">]><foo>&c;</foo>');
! xmlparse
! -----------------------------------------------------------------------
! <!DOCTYPE foo [<!ENTITY c SYSTEM "/etc/no.such.file">]><foo>&c;</foo>
! (1 row)
!
-- This might or might not load the requested DTD, but it mustn't throw error.
SELECT XMLPARSE(DOCUMENT '<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.1.2//EN" "http://www.oasis-open.org/docbook/xml/4.1.2/docbookx.dtd"><chapter> </chapter>');
! xmlparse
! ------------------------------------------------------------------------------------------------------------------------------------------------------
! <!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.1.2//EN" "http://www.oasis-open.org/docbook/xml/4.1.2/docbookx.dtd"><chapter> </chapter>
! (1 row)
!
--- 772,805 ----
-- error messages, we suppress the DETAIL in this test.
\set VERBOSITY terse
SELECT xpath('/*', '<invalidns xmlns=''<''/>');
! ERROR: unsupported XML feature at character 20
\set VERBOSITY default
-- Again, the XML isn't well-formed for namespace purposes
SELECT xpath('/*', '<nosuchprefix:tag/>');
! ERROR: unsupported XML feature
! LINE 1: SELECT xpath('/*', '<nosuchprefix:tag/>');
! ^
! 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('/*', '<relativens xmlns=''relative''/>');
! ERROR: unsupported XML feature
! LINE 1: SELECT xpath('/*', '<relativens xmlns=''relative''/>');
! ^
! 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 '<!DOCTYPE foo [<!ENTITY c SYSTEM "/etc/passwd">]><foo>&c;</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 XMLPARSE(DOCUMENT '<!DOCTYPE foo [<!ENTITY c SYSTEM "/etc/no.such.file">]><foo>&c;</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.
-- This might or might not load the requested DTD, but it mustn't throw error.
SELECT XMLPARSE(DOCUMENT '<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.1.2//EN" "http://www.oasis-open.org/docbook/xml/4.1.2/docbookx.dtd"><chapter> </chapter>');
! 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.