Need guidance on regression.diffs

Поиск

Список

Период

Сортировка

От	Romu Hu
Тема	Need guidance on regression.diffs
Дата	28 октября 2014 г. 22:21:31
Msg-id	544F602D.5070207@gmail.com обсуждение исходный текст
Ответы	Re: Need guidance on regression.diffs (Adrian Klaver <adrian.klaver@aklaver.com>)
Список	pgsql-general

Дерево обсуждения

Hi,

I'm new to postgresql. I ran the regression tests on my postgresql 9.2.8, and 17 of 131 tests failed. Below is the regression.diffs (sorry for pasting such a long file), I have no clue whether these failures can be safely ignored. Any guidance would be appreciated.

*** /tmp/regress/expected/copyselect.out 2014-03-18 03:36:46.000000000--- /tmp/regress/results/copyselect.out 2014-10-28 17:14:03.581781440****************** 111,116 ****--- 111,118 ---- \copy v_test1 to stdout ERROR: cannot copy from view "v_test1" HINT: Try the COPY (SELECT ...) TO variant.+ \copy: ERROR: cannot copy from view "v_test1"+ HINT: Try the COPY (SELECT ...) TO variant. -- -- Test \copy (select ...) --****************** 125,153 **** -- psql handling of COPY in multi-command strings copy (select 1) to stdout\; select 1/0; -- row, then error 1- ERROR: division by zero select 1/0\; copy (select 1) to stdout; -- error only ERROR: division by zero copy (select 1) to stdout\; copy (select 2) to stdout\; select 0\; select 3; -- 1 2 3 1- 2- ?column? - ----------- 3- (1 row)- create table test3 (c int); select 0\; copy test3 from stdin\; copy test3 from stdin\; select 1; -- 1! ?column? ! ----------! 1! (1 row)! select * from test3;! c ! ---! 1! 2! (2 rows)! drop table test3;--- 127,143 ---- -- psql handling of COPY in multi-command strings copy (select 1) to stdout\; select 1/0; -- row, then error 1 select 1/0\; copy (select 1) to stdout; -- error only ERROR: division by zero copy (select 1) to stdout\; copy (select 2) to stdout\; select 0\; select 3; -- 1 2 3 1 create table test3 (c int); select 0\; copy test3 from stdin\; copy test3 from stdin\; select 1; -- 1! 2! \.! invalid command \. select * from test3;! ERROR: syntax error at or near "2"! LINE 1: 2! ^ drop table test3;======================================================================*** /tmp/regress/expected/triggers.out 2014-03-18 03:36:46.000000000--- /tmp/regress/results/triggers.out 2014-10-28 17:14:05.237781139****************** 1114,1129 **** --------+---------+----------- a | integer | b | integer | ! Triggers:! after_del_stmt_trig AFTER DELETE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_del_stmt')! after_ins_stmt_trig AFTER INSERT ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_ins_stmt')! after_upd_stmt_trig AFTER UPDATE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_upd_stmt')! before_del_stmt_trig BEFORE DELETE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_del_stmt')! before_ins_stmt_trig BEFORE INSERT ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt')! before_upd_stmt_trig BEFORE UPDATE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_upd_stmt')! instead_of_delete_trig INSTEAD OF DELETE ON main_view FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del')! instead_of_insert_trig INSTEAD OF INSERT ON main_view FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_ins')! instead_of_update_trig INSTEAD OF UPDATE ON main_view FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd') -- Test dropping view triggers DROP TRIGGER instead_of_insert_trig ON main_view;--- 1114,1122 ---- --------+---------+----------- a | integer | b | integer | ! View definition:! SELECT main_table.a, main_table.b! FROM main_table; -- Test dropping view triggers DROP TRIGGER instead_of_insert_trig ON main_view;****************** 1137,1150 **** View definition: SELECT main_table.a, main_table.b FROM main_table;- Triggers:- after_del_stmt_trig AFTER DELETE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_del_stmt')- after_ins_stmt_trig AFTER INSERT ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_ins_stmt')- after_upd_stmt_trig AFTER UPDATE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_upd_stmt')- before_del_stmt_trig BEFORE DELETE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_del_stmt')- before_ins_stmt_trig BEFORE INSERT ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt')- before_upd_stmt_trig BEFORE UPDATE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_upd_stmt')- instead_of_update_trig INSTEAD OF UPDATE ON main_view FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd') DROP VIEW main_view; ----- 1130,1135 ----======================================================================*** /tmp/regress/expected/inherit.out 2014-03-18 03:36:46.000000000--- /tmp/regress/results/inherit.out 2014-10-28 17:14:05.560781080****************** 635,641 **** Check constraints: "p1chk" CHECK (ff1 > 0) NO INHERIT "p2chk" CHECK (ff1 > 10)- Number of child tables: 1 (Use \d+ to list them.) \d c1 Table "public.c1"--- 635,640 ----****************** 905,917 **** ERROR: cannot rename inherited column "aa" ALTER TABLE inhts RENAME d TO dd; \d+ inhts! Table "public.inhts"! Column | Type | Modifiers | Storage | Stats target | Description ! --------+---------+-----------+---------+--------------+-------------! aa | integer | | plain | | ! b | integer | | plain | | ! c | integer | | plain | | ! dd | integer | | plain | | Inherits: inht1, inhs1 Has OIDs: no--- 904,916 ---- ERROR: cannot rename inherited column "aa" ALTER TABLE inhts RENAME d TO dd; \d+ inhts! Table "public.inhts"! Column | Type | Modifiers | Storage | Description ! --------+---------+-----------+---------+-------------! aa | integer | | plain | ! b | integer | | plain | ! c | integer | | plain | ! dd | integer | | plain | Inherits: inht1, inhs1 Has OIDs: no****************** 925,938 **** NOTICE: merging multiple inherited definitions of column "b" ALTER TABLE inht1 RENAME aa TO aaa; \d+ inht4! Table "public.inht4"! Column | Type | Modifiers | Storage | Stats target | Description ! --------+---------+-----------+---------+--------------+-------------! aaa | integer | | plain | | ! b | integer | | plain | | ! x | integer | | plain | | ! y | integer | | plain | | ! z | integer | | plain | | Inherits: inht2, inht3 Has OIDs: no--- 924,937 ---- NOTICE: merging multiple inherited definitions of column "b" ALTER TABLE inht1 RENAME aa TO aaa; \d+ inht4! Table "public.inht4"! Column | Type | Modifiers | Storage | Description ! --------+---------+-----------+---------+-------------! aaa | integer | | plain | ! b | integer | | plain | ! x | integer | | plain | ! y | integer | | plain | ! z | integer | | plain | Inherits: inht2, inht3 Has OIDs: no****************** 943,956 **** ALTER TABLE inht1 RENAME b TO bb; -- to be failed ERROR: cannot rename inherited column "b" \d+ inhts! Table "public.inhts"! Column | Type | Modifiers | Storage | Stats target | Description ! --------+---------+-----------+---------+--------------+-------------! aaaa | integer | | plain | | ! b | integer | | plain | | ! x | integer | | plain | | ! c | integer | | plain | | ! d | integer | | plain | | Inherits: inht2, inhs1 Has OIDs: no--- 942,955 ---- ALTER TABLE inht1 RENAME b TO bb; -- to be failed ERROR: cannot rename inherited column "b" \d+ inhts! Table "public.inhts"! Column | Type | Modifiers | Storage | Description ! --------+---------+-----------+---------+-------------! aaaa | integer | | plain | ! b | integer | | plain | ! x | integer | | plain | ! c | integer | | plain | ! d | integer | | plain | Inherits: inht2, inhs1 Has OIDs: no****************** 992,1026 **** NOTICE: CREATE TABLE / UNIQUE will create implicit index "test_constraints_val1_val2_key" for table "test_constraints" CREATE TABLE test_constraints_inh () INHERITS (test_constraints); \d+ test_constraints! Table "public.test_constraints"! Column | Type | Modifiers | Storage | Stats target | Description ! --------+-------------------+-----------+----------+--------------+-------------! id | integer | | plain | | ! val1 | character varying | | extended | | ! val2 | integer | | plain | | Indexes:! "test_constraints_val1_val2_key" UNIQUE CONSTRAINT, btree (val1, val2)! Child tables: test_constraints_inh Has OIDs: no ALTER TABLE ONLY test_constraints DROP CONSTRAINT test_constraints_val1_val2_key; \d+ test_constraints! Table "public.test_constraints"! Column | Type | Modifiers | Storage | Stats target | Description ! --------+-------------------+-----------+----------+--------------+-------------! id | integer | | plain | | ! val1 | character varying | | extended | | ! val2 | integer | | plain | | ! Child tables: test_constraints_inh Has OIDs: no \d+ test_constraints_inh! Table "public.test_constraints_inh"! Column | Type | Modifiers | Storage | Stats target | Description ! --------+-------------------+-----------+----------+--------------+-------------! id | integer | | plain | | ! val1 | character varying | | extended | | ! val2 | integer | | plain | | Inherits: test_constraints Has OIDs: no --- 991,1023 ---- NOTICE: CREATE TABLE / UNIQUE will create implicit index "test_constraints_val1_val2_key" for table "test_constraints" CREATE TABLE test_constraints_inh () INHERITS (test_constraints); \d+ test_constraints! Table "public.test_constraints"! Column | Type | Modifiers | Storage | Description ! --------+-------------------+-----------+----------+-------------! id | integer | | plain | ! val1 | character varying | | extended | ! val2 | integer | | plain | Indexes:! "test_constraints_val1_val2_key" UNIQUE, btree (val1, val2) Has OIDs: no ALTER TABLE ONLY test_constraints DROP CONSTRAINT test_constraints_val1_val2_key; \d+ test_constraints! Table "public.test_constraints"! Column | Type | Modifiers | Storage | Description ! --------+-------------------+-----------+----------+-------------! id | integer | | plain | ! val1 | character varying | | extended | ! val2 | integer | | plain | Has OIDs: no \d+ test_constraints_inh! Table "public.test_constraints_inh"! Column | Type | Modifiers | Storage | Description ! --------+-------------------+-----------+----------+-------------! id | integer | | plain | ! val1 | character varying | | extended | ! val2 | integer | | plain | Inherits: test_constraints Has OIDs: no ****************** 1033,1061 **** NOTICE: CREATE TABLE / EXCLUDE will create implicit index "test_ex_constraints_c_excl" for table "test_ex_constraints" CREATE TABLE test_ex_constraints_inh () INHERITS (test_ex_constraints); \d+ test_ex_constraints! Table "public.test_ex_constraints"! Column | Type | Modifiers | Storage | Stats target | Description ! --------+--------+-----------+---------+--------------+-------------! c | circle | | plain | | Indexes:! "test_ex_constraints_c_excl" EXCLUDE USING gist (c WITH &&)! Child tables: test_ex_constraints_inh Has OIDs: no ALTER TABLE test_ex_constraints DROP CONSTRAINT test_ex_constraints_c_excl; \d+ test_ex_constraints! Table "public.test_ex_constraints"! Column | Type | Modifiers | Storage | Stats target | Description ! --------+--------+-----------+---------+--------------+-------------! c | circle | | plain | | ! Child tables: test_ex_constraints_inh Has OIDs: no \d+ test_ex_constraints_inh! Table "public.test_ex_constraints_inh"! Column | Type | Modifiers | Storage | Stats target | Description ! --------+--------+-----------+---------+--------------+-------------! c | circle | | plain | | Inherits: test_ex_constraints Has OIDs: no --- 1030,1056 ---- NOTICE: CREATE TABLE / EXCLUDE will create implicit index "test_ex_constraints_c_excl" for table "test_ex_constraints" CREATE TABLE test_ex_constraints_inh () INHERITS (test_ex_constraints); \d+ test_ex_constraints! Table "public.test_ex_constraints"! Column | Type | Modifiers | Storage | Description ! --------+--------+-----------+---------+-------------! c | circle | | plain | Indexes:! "test_ex_constraints_c_excl" gist (c) Has OIDs: no ALTER TABLE test_ex_constraints DROP CONSTRAINT test_ex_constraints_c_excl; \d+ test_ex_constraints! Table "public.test_ex_constraints"! Column | Type | Modifiers | Storage | Description ! --------+--------+-----------+---------+-------------! c | circle | | plain | Has OIDs: no \d+ test_ex_constraints_inh! Table "public.test_ex_constraints_inh"! Column | Type | Modifiers | Storage | Description ! --------+--------+-----------+---------+-------------! c | circle | | plain | Inherits: test_ex_constraints Has OIDs: no ****************** 1067,1076 **** CREATE TABLE test_foreign_constraints(id1 int REFERENCES test_primary_constraints(id)); CREATE TABLE test_foreign_constraints_inh () INHERITS (test_foreign_constraints); \d+ test_primary_constraints! Table "public.test_primary_constraints"! Column | Type | Modifiers | Storage | Stats target | Description ! --------+---------+-----------+---------+--------------+-------------! id | integer | not null | plain | | Indexes: "test_primary_constraints_pkey" PRIMARY KEY, btree (id) Referenced by:--- 1062,1071 ---- CREATE TABLE test_foreign_constraints(id1 int REFERENCES test_primary_constraints(id)); CREATE TABLE test_foreign_constraints_inh () INHERITS (test_foreign_constraints); \d+ test_primary_constraints! Table "public.test_primary_constraints"! Column | Type | Modifiers | Storage | Description ! --------+---------+-----------+---------+-------------! id | integer | not null | plain | Indexes: "test_primary_constraints_pkey" PRIMARY KEY, btree (id) Referenced by:****************** 1078,1106 **** Has OIDs: no \d+ test_foreign_constraints! Table "public.test_foreign_constraints"! Column | Type | Modifiers | Storage | Stats target | Description ! --------+---------+-----------+---------+--------------+-------------! id1 | integer | | plain | | Foreign-key constraints: "test_foreign_constraints_id1_fkey" FOREIGN KEY (id1) REFERENCES test_primary_constraints(id)- Child tables: test_foreign_constraints_inh Has OIDs: no ALTER TABLE test_foreign_constraints DROP CONSTRAINT test_foreign_constraints_id1_fkey; \d+ test_foreign_constraints! Table "public.test_foreign_constraints"! Column | Type | Modifiers | Storage | Stats target | Description ! --------+---------+-----------+---------+--------------+-------------! id1 | integer | | plain | | ! Child tables: test_foreign_constraints_inh Has OIDs: no \d+ test_foreign_constraints_inh! Table "public.test_foreign_constraints_inh"! Column | Type | Modifiers | Storage | Stats target | Description ! --------+---------+-----------+---------+--------------+-------------! id1 | integer | | plain | | Inherits: test_foreign_constraints Has OIDs: no --- 1073,1099 ---- Has OIDs: no \d+ test_foreign_constraints! Table "public.test_foreign_constraints"! Column | Type | Modifiers | Storage | Description ! --------+---------+-----------+---------+-------------! id1 | integer | | plain | Foreign-key constraints: "test_foreign_constraints_id1_fkey" FOREIGN KEY (id1) REFERENCES test_primary_constraints(id) Has OIDs: no ALTER TABLE test_foreign_constraints DROP CONSTRAINT test_foreign_constraints_id1_fkey; \d+ test_foreign_constraints! Table "public.test_foreign_constraints"! Column | Type | Modifiers | Storage | Description ! --------+---------+-----------+---------+-------------! id1 | integer | | plain | Has OIDs: no \d+ test_foreign_constraints_inh! Table "public.test_foreign_constraints_inh"! Column | Type | Modifiers | Storage | Description ! --------+---------+-----------+---------+-------------! id1 | integer | | plain | Inherits: test_foreign_constraints Has OIDs: no ======================================================================*** /tmp/regress/expected/create_table_like.out 2014-03-18 03:36:46.000000000--- /tmp/regress/results/create_table_like.out 2014-10-28 17:14:05.735781049****************** 115,136 **** ALTER TABLE ctlt4 ALTER COLUMN c SET STORAGE EXTERNAL; CREATE TABLE ctlt12_storage (LIKE ctlt1 INCLUDING STORAGE, LIKE ctlt2 INCLUDING STORAGE); \d+ ctlt12_storage! Table "public.ctlt12_storage"! Column | Type | Modifiers | Storage | Stats target | Description ! --------+------+-----------+----------+--------------+-------------! a | text | not null | main | | ! b | text | | extended | | ! c | text | | external | | Has OIDs: no CREATE TABLE ctlt12_comments (LIKE ctlt1 INCLUDING COMMENTS, LIKE ctlt2 INCLUDING COMMENTS); \d+ ctlt12_comments! Table "public.ctlt12_comments"! Column | Type | Modifiers | Storage | Stats target | Description ! --------+------+-----------+----------+--------------+-------------! a | text | not null | extended | | A! b | text | | extended | | B! c | text | | extended | | C Has OIDs: no CREATE TABLE ctlt1_inh (LIKE ctlt1 INCLUDING CONSTRAINTS INCLUDING COMMENTS) INHERITS (ctlt1);--- 115,136 ---- ALTER TABLE ctlt4 ALTER COLUMN c SET STORAGE EXTERNAL; CREATE TABLE ctlt12_storage (LIKE ctlt1 INCLUDING STORAGE, LIKE ctlt2 INCLUDING STORAGE); \d+ ctlt12_storage! Table "public.ctlt12_storage"! Column | Type | Modifiers | Storage | Description ! --------+------+-----------+----------+-------------! a | text | not null | main | ! b | text | | extended | ! c | text | | external | Has OIDs: no CREATE TABLE ctlt12_comments (LIKE ctlt1 INCLUDING COMMENTS, LIKE ctlt2 INCLUDING COMMENTS); \d+ ctlt12_comments! Table "public.ctlt12_comments"! Column | Type | Modifiers | Storage | Description ! --------+------+-----------+----------+-------------! a | text | not null | extended | A! b | text | | extended | B! c | text | | extended | C Has OIDs: no CREATE TABLE ctlt1_inh (LIKE ctlt1 INCLUDING CONSTRAINTS INCLUDING COMMENTS) INHERITS (ctlt1);****************** 138,148 **** NOTICE: merging column "b" with inherited definition NOTICE: merging constraint "ctlt1_a_check" with inherited definition \d+ ctlt1_inh! Table "public.ctlt1_inh"! Column | Type | Modifiers | Storage | Stats target | Description ! --------+------+-----------+----------+--------------+-------------! a | text | not null | main | | A! b | text | | extended | | B Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) Inherits: ctlt1--- 138,148 ---- NOTICE: merging column "b" with inherited definition NOTICE: merging constraint "ctlt1_a_check" with inherited definition \d+ ctlt1_inh! Table "public.ctlt1_inh"! Column | Type | Modifiers | Storage | Description ! --------+------+-----------+----------+-------------! a | text | not null | main | A! b | text | | extended | B Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) Inherits: ctlt1****************** 157,168 **** CREATE TABLE ctlt13_inh () INHERITS (ctlt1, ctlt3); NOTICE: merging multiple inherited definitions of column "a" \d+ ctlt13_inh! Table "public.ctlt13_inh"! Column | Type | Modifiers | Storage | Stats target | Description ! --------+------+-----------+----------+--------------+-------------! a | text | not null | main | | ! b | text | | extended | | ! c | text | | external | | Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) "ctlt3_a_check" CHECK (length(a) < 5)--- 157,168 ---- CREATE TABLE ctlt13_inh () INHERITS (ctlt1, ctlt3); NOTICE: merging multiple inherited definitions of column "a" \d+ ctlt13_inh! Table "public.ctlt13_inh"! Column | Type | Modifiers | Storage | Description ! --------+------+-----------+----------+-------------! a | text | not null | main | ! b | text | | extended | ! c | text | | external | Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) "ctlt3_a_check" CHECK (length(a) < 5)****************** 173,184 **** CREATE TABLE ctlt13_like (LIKE ctlt3 INCLUDING CONSTRAINTS INCLUDING COMMENTS INCLUDING STORAGE) INHERITS (ctlt1); NOTICE: merging column "a" with inherited definition \d+ ctlt13_like! Table "public.ctlt13_like"! Column | Type | Modifiers | Storage | Stats target | Description ! --------+------+-----------+----------+--------------+-------------! a | text | not null | main | | A3! b | text | | extended | | ! c | text | | external | | C Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) "ctlt3_a_check" CHECK (length(a) < 5)--- 173,184 ---- CREATE TABLE ctlt13_like (LIKE ctlt3 INCLUDING CONSTRAINTS INCLUDING COMMENTS INCLUDING STORAGE) INHERITS (ctlt1); NOTICE: merging column "a" with inherited definition \d+ ctlt13_like! Table "public.ctlt13_like"! Column | Type | Modifiers | Storage | Description ! --------+------+-----------+----------+-------------! a | text | not null | main | A3! b | text | | extended | ! c | text | | external | C Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) "ctlt3_a_check" CHECK (length(a) < 5)****************** 194,204 **** CREATE TABLE ctlt_all (LIKE ctlt1 INCLUDING ALL); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "ctlt_all_pkey" for table "ctlt_all" \d+ ctlt_all! Table "public.ctlt_all"! Column | Type | Modifiers | Storage | Stats target | Description ! --------+------+-----------+----------+--------------+-------------! a | text | not null | main | | A! b | text | | extended | | B Indexes: "ctlt_all_pkey" PRIMARY KEY, btree (a) "ctlt_all_b_idx" btree (b)--- 194,204 ---- CREATE TABLE ctlt_all (LIKE ctlt1 INCLUDING ALL); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "ctlt_all_pkey" for table "ctlt_all" \d+ ctlt_all! Table "public.ctlt_all"! Column | Type | Modifiers | Storage | Description ! --------+------+-----------+----------+-------------! a | text | not null | main | A! b | text | | extended | B Indexes: "ctlt_all_pkey" PRIMARY KEY, btree (a) "ctlt_all_b_idx" btree (b)======================================================================*** /tmp/regress/expected/typed_table.out 2014-03-18 03:36:46.000000000--- /tmp/regress/results/typed_table.out 2014-10-28 17:14:05.791781039****************** 15,21 **** --------+---------+----------- id | integer | name | text | - Typed table of type: person_type CREATE FUNCTION get_all_persons() RETURNS SETOF person_type LANGUAGE SQL--- 15,20 ----****************** 55,62 **** name | text | Indexes: "persons2_pkey" PRIMARY KEY, btree (id)! "persons2_name_key" UNIQUE CONSTRAINT, btree (name)! Typed table of type: person_type CREATE TABLE persons3 OF person_type ( PRIMARY KEY (id),--- 54,60 ---- name | text | Indexes: "persons2_pkey" PRIMARY KEY, btree (id)! "persons2_name_key" UNIQUE, btree (name) CREATE TABLE persons3 OF person_type ( PRIMARY KEY (id),****************** 71,77 **** name | text | default ''::text Indexes: "persons3_pkey" PRIMARY KEY, btree (id)- Typed table of type: person_type CREATE TABLE persons4 OF person_type ( name WITH OPTIONS NOT NULL,--- 69,74 ----======================================================================*** /tmp/regress/expected/privileges.out 2014-03-18 03:36:46.000000000--- /tmp/regress/results/privileges.out 2014-10-28 17:14:08.141780611****************** 1381,1392 **** Access privileges Schema | Name | Type | Access privileges | Column access privileges --------+---------------+-------+-----------------------------------+--------------------------! public | dep_priv_test | table | regressuser1=arwdDxt/regressuser1+| ! | | | regressuser2=r*/regressuser1 +| ! | | | regressuser3=r*/regressuser1 +| ! | | | regressuser4=r*/regressuser2 +| ! | | | regressuser4=r*/regressuser3 +| ! | | | regressuser5=r/regressuser4 | (1 row) set session role regressuser2;--- 1381,1392 ---- Access privileges Schema | Name | Type | Access privileges | Column access privileges --------+---------------+-------+-----------------------------------+--------------------------! public | dep_priv_test | table | regressuser1=arwdDxt/regressuser1 | ! : regressuser2=r*/regressuser1 ! : regressuser3=r*/regressuser1 ! : regressuser4=r*/regressuser2 ! : regressuser4=r*/regressuser3 ! : regressuser5=r/regressuser4 (1 row) set session role regressuser2;****************** 1395,1405 **** Access privileges Schema | Name | Type | Access privileges | Column access privileges --------+---------------+-------+-----------------------------------+--------------------------! public | dep_priv_test | table | regressuser1=arwdDxt/regressuser1+| ! | | | regressuser2=r*/regressuser1 +| ! | | | regressuser3=r*/regressuser1 +| ! | | | regressuser4=r*/regressuser3 +| ! | | | regressuser5=r/regressuser4 | (1 row) set session role regressuser3;--- 1395,1405 ---- Access privileges Schema | Name | Type | Access privileges | Column access privileges --------+---------------+-------+-----------------------------------+--------------------------! public | dep_priv_test | table | regressuser1=arwdDxt/regressuser1 | ! : regressuser2=r*/regressuser1 ! : regressuser3=r*/regressuser1 ! : regressuser4=r*/regressuser3 ! : regressuser5=r/regressuser4 (1 row) set session role regressuser3;****************** 1408,1416 **** Access privileges Schema | Name | Type | Access privileges | Column access privileges --------+---------------+-------+-----------------------------------+--------------------------! public | dep_priv_test | table | regressuser1=arwdDxt/regressuser1+| ! | | | regressuser2=r*/regressuser1 +| ! | | | regressuser3=r*/regressuser1 | (1 row) set session role regressuser1;--- 1408,1416 ---- Access privileges Schema | Name | Type | Access privileges | Column access privileges --------+---------------+-------+-----------------------------------+--------------------------! public | dep_priv_test | table | regressuser1=arwdDxt/regressuser1 | ! : regressuser2=r*/regressuser1 ! : regressuser3=r*/regressuser1 (1 row) set session role regressuser1;======================================================================*** /tmp/regress/expected/collate.out 2014-03-18 03:36:46.000000000--- /tmp/regress/results/collate.out 2014-10-28 17:14:08.295780582****************** 16,26 **** b text COLLATE "C" NOT NULL ); \d collate_test1! Table "collate_tests.collate_test1"! Column | Type | Modifiers ! --------+---------+-------------------- a | integer | ! b | text | collate C not null CREATE TABLE collate_test_fail ( a int COLLATE "C",--- 16,26 ---- b text COLLATE "C" NOT NULL ); \d collate_test1! Table "collate_tests.collate_test1"! Column | Type | Modifiers ! --------+---------+----------- a | integer | ! b | text | not null CREATE TABLE collate_test_fail ( a int COLLATE "C",****************** 34,43 **** ); \d collate_test_like Table "collate_tests.collate_test_like"! Column | Type | Modifiers ! --------+---------+-------------------- a | integer | ! b | text | collate C not null CREATE TABLE collate_test2 ( a int,--- 34,43 ---- ); \d collate_test_like Table "collate_tests.collate_test_like"! Column | Type | Modifiers ! --------+---------+----------- a | integer | ! b | text | not null CREATE TABLE collate_test2 ( a int,======================================================================*** /tmp/regress/expected/rules.out 2014-03-18 03:36:46.000000000--- /tmp/regress/results/rules.out 2014-10-28 17:14:08.831780485****************** 1600,1625 **** select pg_get_viewdef('shoe'::regclass,true) as pretty; pretty -------------------------------------------------------------! 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,0) as prettier; prettier -----------------------------------------------! 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) --- 1600,1625 ---- select pg_get_viewdef('shoe'::regclass,true) as pretty; pretty -------------------------------------------------------------! 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,0) as prettier; prettier -----------------------------------------------! 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) ======================================================================*** /tmp/regress/expected/dependency.out 2014-03-18 03:36:46.000000000--- /tmp/regress/results/dependency.out 2014-10-28 17:14:09.707780325****************** 71,79 **** Access privileges Schema | Name | Type | Access privileges | Column access privileges --------+----------+-------+--------------------------------------------------+--------------------------! public | deptest1 | table | regression_user0=arwdDxt/regression_user0 +| ! | | | regression_user1=a*r*w*d*D*x*t*/regression_user0+| ! | | | regression_user2=arwdDxt/regression_user1 | (1 row) DROP OWNED BY regression_user1;--- 71,79 ---- Access privileges Schema | Name | Type | Access privileges | Column access privileges --------+----------+-------+--------------------------------------------------+--------------------------! public | deptest1 | table | regression_user0=arwdDxt/regression_user0 | ! : regression_user1=a*r*w*d*D*x*t*/regression_user0 ! : regression_user2=arwdDxt/regression_user1 (1 row) DROP OWNED BY regression_user1;======================================================================*** /tmp/regress/expected/tsearch.out 2014-03-18 03:36:46.000000000--- /tmp/regress/results/tsearch.out 2014-10-28 17:14:10.282780220****************** 357,364 **** 3 | ewri2 12 | 13 | <a href="qwe<qwe>">! 12 | +! | 19 | /usr/local/fff 12 | 19 | /awdf/dwqe/4325--- 357,364 ---- 3 | ewri2 12 | 13 | <a href="qwe<qwe>">! 12 | ! : 19 | /usr/local/fff 12 | 19 | /awdf/dwqe/4325****************** 390,397 **** 20 | -4.2 12 | . 22 | 234! 12 | +! | 12 | < 1 | i 12 | --- 390,397 ---- 20 | -4.2 12 | . 22 | 234! 12 | ! : 12 | < 1 | i 12 | ****************** 610,618 **** ', to_tsquery('english', 'paint&water')); ts_headline -----------------------------------------! painted Ocean. +! Water, water, every where+! And all the boards did shrink; + Water, water, every (1 row) --- 610,618 ---- ', to_tsquery('english', 'paint&water')); ts_headline -----------------------------------------! painted Ocean.! Water, water, every where! And all the boards did shrink; Water, water, every (1 row) ****************** 629,637 **** ', to_tsquery('english', 'breath&motion&water')); ts_headline ----------------------------------! breath nor motion,+! As idle as a painted Ship +! Upon a painted Ocean. + Water, water (1 row) --- 629,637 ---- ', to_tsquery('english', 'breath&motion&water')); ts_headline ----------------------------------! breath nor motion,! As idle as a painted Ship! Upon a painted Ocean. Water, water (1 row) ****************** 648,656 **** ', to_tsquery('english', 'ocean')); ts_headline ----------------------------------! Ocean. +! Water, water, every where +! And all the boards did shrink;+ Water, water, every where (1 row) --- 648,656 ---- ', to_tsquery('english', 'ocean')); ts_headline ----------------------------------! Ocean.! Water, water, every where! And all the boards did shrink; Water, water, every where (1 row) ****************** 669,685 **** to_tsquery('english', 'sea&foo'), 'HighlightAll=true'); ts_headline -----------------------------------------------------------------------------! +! <html> +!  +! <body> +! Sea view wow foo bar qq +! <a href="http://www.google.com/foo.bar.html" target="_blank">YES  </a>+! ff-bg +! <script> +! document.write(15); +! </script> +! </body> + </html> (1 row) --- 669,685 ---- to_tsquery('english', 'sea&foo'), 'HighlightAll=true'); ts_headline -----------------------------------------------------------------------------! ! <html>! ! <body>! Sea view wow foo bar qq! <a href="http://www.google.com/foo.bar.html" target="_blank">YES  </a>! ff-bg! <script>! document.write(15);! </script>! </body> </html> (1 row) ****************** 697,709 **** ', to_tsquery('english', 'ocean'), 'MaxFragments=1'); ts_headline ------------------------------------! after day, +! We stuck, nor breath nor motion,+! As idle as a painted Ship +! Upon a painted Ocean. +! Water, water, every where +! And all the boards did shrink; +! Water, water, every where, + Nor any drop (1 row) --- 697,709 ---- ', to_tsquery('english', 'ocean'), 'MaxFragments=1'); ts_headline ------------------------------------! after day,! We stuck, nor breath nor motion,! As idle as a painted Ship! Upon a painted Ocean.! Water, water, every where! And all the boards did shrink;! Water, water, every where, Nor any drop (1 row) ****************** 721,733 **** ', to_tsquery('english', 'Coleridge & stuck'), 'MaxFragments=2'); ts_headline ----------------------------------------------! after day, day after day, +! We stuck, nor breath nor motion, +! As idle as a painted Ship +! Upon a painted Ocean. +! Water, water, every where +! And all the boards did shrink; +! Water, water, every where ... drop to drink.+ S. T. Coleridge (1 row) --- 721,733 ---- ', to_tsquery('english', 'Coleridge & stuck'), 'MaxFragments=2'); ts_headline ----------------------------------------------! after day, day after day,! We stuck, nor breath nor motion,! As idle as a painted Ship! Upon a painted Ocean.! Water, water, every where! And all the boards did shrink;! Water, water, every where ... drop to drink. S. T. Coleridge (1 row) ****************** 745,753 **** ', to_tsquery('english', 'ocean & seahorse'), 'MaxFragments=1'); ts_headline ------------------------------------! +! Day after day, day after day, +! We stuck, nor breath nor motion,+ As idle as (1 row) --- 745,753 ---- ', to_tsquery('english', 'ocean & seahorse'), 'MaxFragments=1'); ts_headline ------------------------------------! ! Day after day, day after day,! We stuck, nor breath nor motion, As idle as (1 row) ****************** 765,777 **** ', to_tsquery('english', 'Coleridge & stuck'), 'MaxFragments=2,FragmentDelimiter=***'); ts_headline --------------------------------------------! after day, day after day, +! We stuck, nor breath nor motion, +! As idle as a painted Ship +! Upon a painted Ocean. +! Water, water, every where +! And all the boards did shrink; +! Water, water, every where***drop to drink.+ S. T. Coleridge (1 row) --- 765,777 ---- ', to_tsquery('english', 'Coleridge & stuck'), 'MaxFragments=2,FragmentDelimiter=***'); ts_headline --------------------------------------------! after day, day after day,! We stuck, nor breath nor motion,! As idle as a painted Ship! Upon a painted Ocean.! Water, water, every where! And all the boards did shrink;! Water, water, every where***drop to drink. S. T. Coleridge (1 row) ======================================================================*** /tmp/regress/expected/foreign_data.out 2014-03-18 03:36:46.000000000--- /tmp/regress/results/foreign_data.out 2014-10-28 17:14:10.512780178****************** 39,50 **** ERROR: function bar(text[], oid) does not exist CREATE FOREIGN DATA WRAPPER foo; \dew! List of foreign-data wrappers! Name | Owner | Handler | Validator ! ------------+-------------------+---------+--------------------------! dummy | foreign_data_user | - | -! foo | foreign_data_user | - | -! postgresql | foreign_data_user | - | postgresql_fdw_validator (3 rows) CREATE FOREIGN DATA WRAPPER foo; -- duplicate--- 39,50 ---- ERROR: function bar(text[], oid) does not exist CREATE FOREIGN DATA WRAPPER foo; \dew! List of foreign-data wrappers! Name | Owner | Validator ! ------------+-------------------+--------------------------! dummy | foreign_data_user | -! foo | foreign_data_user | -! postgresql | foreign_data_user | postgresql_fdw_validator (3 rows) CREATE FOREIGN DATA WRAPPER foo; -- duplicate****************** 52,63 **** DROP FOREIGN DATA WRAPPER foo; CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1'); \dew+! List of foreign-data wrappers! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description ! ------------+-------------------+---------+--------------------------+-------------------+---------------+-------------! dummy | foreign_data_user | - | - | | | useless! foo | foreign_data_user | - | - | | (testing '1') | ! postgresql | foreign_data_user | - | postgresql_fdw_validator | | | (3 rows) DROP FOREIGN DATA WRAPPER foo;--- 52,63 ---- DROP FOREIGN DATA WRAPPER foo; CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1'); \dew+! List of foreign-data wrappers! Name | Owner | Validator | Access privileges | Options ! ------------+-------------------+--------------------------+-------------------+-------------! dummy | foreign_data_user | - | | ! foo | foreign_data_user | - | | {testing=1}! postgresql | foreign_data_user | postgresql_fdw_validator | | (3 rows) DROP FOREIGN DATA WRAPPER foo;****************** 65,76 **** ERROR: option "testing" provided more than once CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', another '2'); \dew+! List of foreign-data wrappers! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description ! ------------+-------------------+---------+--------------------------+-------------------+----------------------------+-------------! dummy | foreign_data_user | - | - | | | useless! foo | foreign_data_user | - | - | | (testing '1', another '2') | ! postgresql | foreign_data_user | - | postgresql_fdw_validator | | | (3 rows) DROP FOREIGN DATA WRAPPER foo;--- 65,76 ---- ERROR: option "testing" provided more than once CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', another '2'); \dew+! List of foreign-data wrappers! Name | Owner | Validator | Access privileges | Options ! ------------+-------------------+--------------------------+-------------------+-----------------------! dummy | foreign_data_user | - | | ! foo | foreign_data_user | - | | {testing=1,another=2}! postgresql | foreign_data_user | postgresql_fdw_validator | | (3 rows) DROP FOREIGN DATA WRAPPER foo;****************** 81,92 **** RESET ROLE; CREATE FOREIGN DATA WRAPPER foo VALIDATOR postgresql_fdw_validator; \dew+! List of foreign-data wrappers! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description ! ------------+-------------------+---------+--------------------------+-------------------+-------------+-------------! dummy | foreign_data_user | - | - | | | useless! foo | foreign_data_user | - | postgresql_fdw_validator | | | ! postgresql | foreign_data_user | - | postgresql_fdw_validator | | | (3 rows) -- ALTER FOREIGN DATA WRAPPER--- 81,92 ---- RESET ROLE; CREATE FOREIGN DATA WRAPPER foo VALIDATOR postgresql_fdw_validator; \dew+! List of foreign-data wrappers! Name | Owner | Validator | Access privileges | Options ! ------------+-------------------+--------------------------+-------------------+---------! dummy | foreign_data_user | - | | ! foo | foreign_data_user | postgresql_fdw_validator | | ! postgresql | foreign_data_user | postgresql_fdw_validator | | (3 rows) -- ALTER FOREIGN DATA WRAPPER****************** 98,109 **** ERROR: function bar(text[], oid) does not exist ALTER FOREIGN DATA WRAPPER foo NO VALIDATOR; \dew+! List of foreign-data wrappers! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description ! ------------+-------------------+---------+--------------------------+-------------------+-------------+-------------! dummy | foreign_data_user | - | - | | | useless! foo | foreign_data_user | - | - | | | ! postgresql | foreign_data_user | - | postgresql_fdw_validator | | | (3 rows) ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '1', b '2');--- 98,109 ---- ERROR: function bar(text[], oid) does not exist ALTER FOREIGN DATA WRAPPER foo NO VALIDATOR; \dew+! List of foreign-data wrappers! Name | Owner | Validator | Access privileges | Options ! ------------+-------------------+--------------------------+-------------------+---------! dummy | foreign_data_user | - | | ! foo | foreign_data_user | - | | ! postgresql | foreign_data_user | postgresql_fdw_validator | | (3 rows) ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '1', b '2');****************** 113,146 **** ERROR: option "c" not found ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD x '1', DROP x); \dew+! List of foreign-data wrappers! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description ! ------------+-------------------+---------+--------------------------+-------------------+----------------+-------------! dummy | foreign_data_user | - | - | | | useless! foo | foreign_data_user | - | - | | (a '1', b '2') | ! postgresql | foreign_data_user | - | postgresql_fdw_validator | | | (3 rows) ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP a, SET b '3', ADD c '4'); \dew+! List of foreign-data wrappers! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description ! ------------+-------------------+---------+--------------------------+-------------------+----------------+-------------! dummy | foreign_data_user | - | - | | | useless! foo | foreign_data_user | - | - | | (b '3', c '4') | ! postgresql | foreign_data_user | - | postgresql_fdw_validator | | | (3 rows) ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '2'); ALTER FOREIGN DATA WRAPPER foo OPTIONS (b '4'); -- ERROR ERROR: option "b" provided more than once \dew+! List of foreign-data wrappers! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description ! ------------+-------------------+---------+--------------------------+-------------------+-----------------------+-------------! dummy | foreign_data_user | - | - | | | useless! foo | foreign_data_user | - | - | | (b '3', c '4', a '2') | ! postgresql | foreign_data_user | - | postgresql_fdw_validator | | | (3 rows) SET ROLE regress_test_role;--- 113,146 ---- ERROR: option "c" not found ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD x '1', DROP x); \dew+! List of foreign-data wrappers! Name | Owner | Validator | Access privileges | Options ! ------------+-------------------+--------------------------+-------------------+-----------! dummy | foreign_data_user | - | | ! foo | foreign_data_user | - | | {a=1,b=2}! postgresql | foreign_data_user | postgresql_fdw_validator | | (3 rows) ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP a, SET b '3', ADD c '4'); \dew+! List of foreign-data wrappers! Name | Owner | Validator | Access privileges | Options ! ------------+-------------------+--------------------------+-------------------+-----------! dummy | foreign_data_user | - | | ! foo | foreign_data_user | - | | {b=3,c=4}! postgresql | foreign_data_user | postgresql_fdw_validator | | (3 rows) ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '2'); ALTER FOREIGN DATA WRAPPER foo OPTIONS (b '4'); -- ERROR ERROR: option "b" provided more than once \dew+! List of foreign-data wrappers! Name | Owner | Validator | Access privileges | Options ! ------------+-------------------+--------------------------+-------------------+---------------! dummy | foreign_data_user | - | | ! foo | foreign_data_user | - | | {b=3,c=4,a=2}! postgresql | foreign_data_user | postgresql_fdw_validator | | (3 rows) SET ROLE regress_test_role;****************** 150,161 **** SET ROLE regress_test_role_super; ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD d '5'); \dew+! List of foreign-data wrappers! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description ! ------------+-------------------+---------+--------------------------+-------------------+------------------------------+-------------! dummy | foreign_data_user | - | - | | | useless! foo | foreign_data_user | - | - | | (b '3', c '4', a '2', d '5') | ! postgresql | foreign_data_user | - | postgresql_fdw_validator | | | (3 rows) ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_test_role; -- ERROR--- 150,161 ---- SET ROLE regress_test_role_super; ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD d '5'); \dew+! List of foreign-data wrappers! Name | Owner | Validator | Access privileges | Options ! ------------+-------------------+--------------------------+-------------------+-------------------! dummy | foreign_data_user | - | | ! foo | foreign_data_user | - | | {b=3,c=4,a=2,d=5}! postgresql | foreign_data_user | postgresql_fdw_validator | | (3 rows) ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_test_role; -- ERROR****************** 169,190 **** HINT: Must be superuser to alter a foreign-data wrapper. RESET ROLE; \dew+! List of foreign-data wrappers! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description ! ------------+-------------------------+---------+--------------------------+-------------------+------------------------------+-------------! dummy | foreign_data_user | - | - | | | useless! foo | regress_test_role_super | - | - | | (b '3', c '4', a '2', d '5') | ! postgresql | foreign_data_user | - | postgresql_fdw_validator | | | (3 rows) ALTER FOREIGN DATA WRAPPER foo RENAME TO foo1; \dew+! List of foreign-data wrappers! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description ! ------------+-------------------------+---------+--------------------------+-------------------+------------------------------+-------------! dummy | foreign_data_user | - | - | | | useless! foo1 | regress_test_role_super | - | - | | (b '3', c '4', a '2', d '5') | ! postgresql | foreign_data_user | - | postgresql_fdw_validator | | | (3 rows) ALTER FOREIGN DATA WRAPPER foo1 RENAME TO foo;--- 169,190 ---- HINT: Must be superuser to alter a foreign-data wrapper. RESET ROLE; \dew+! List of foreign-data wrappers! Name | Owner | Validator | Access privileges | Options ! ------------+-------------------------+--------------------------+-------------------+-------------------! dummy | foreign_data_user | - | | ! foo | regress_test_role_super | - | | {b=3,c=4,a=2,d=5}! postgresql | foreign_data_user | postgresql_fdw_validator | | (3 rows) ALTER FOREIGN DATA WRAPPER foo RENAME TO foo1; \dew+! List of foreign-data wrappers! Name | Owner | Validator | Access privileges | Options ! ------------+-------------------------+--------------------------+-------------------+-------------------! dummy | foreign_data_user | - | | ! foo1 | regress_test_role_super | - | | {b=3,c=4,a=2,d=5}! postgresql | foreign_data_user | postgresql_fdw_validator | | (3 rows) ALTER FOREIGN DATA WRAPPER foo1 RENAME TO foo;****************** 194,205 **** DROP FOREIGN DATA WRAPPER IF EXISTS nonexistent; NOTICE: foreign-data wrapper "nonexistent" does not exist, skipping \dew+! List of foreign-data wrappers! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description ! ------------+-------------------------+---------+--------------------------+-------------------+------------------------------+-------------! dummy | foreign_data_user | - | - | | | useless! foo | regress_test_role_super | - | - | | (b '3', c '4', a '2', d '5') | ! postgresql | foreign_data_user | - | postgresql_fdw_validator | | | (3 rows) DROP ROLE regress_test_role_super; -- ERROR--- 194,205 ---- DROP FOREIGN DATA WRAPPER IF EXISTS nonexistent; NOTICE: foreign-data wrapper "nonexistent" does not exist, skipping \dew+! List of foreign-data wrappers! Name | Owner | Validator | Access privileges | Options ! ------------+-------------------------+--------------------------+-------------------+-------------------! dummy | foreign_data_user | - | | ! foo | regress_test_role_super | - | | {b=3,c=4,a=2,d=5}! postgresql | foreign_data_user | postgresql_fdw_validator | | (3 rows) DROP ROLE regress_test_role_super; -- ERROR****************** 210,220 **** RESET ROLE; DROP ROLE regress_test_role_super; \dew+! List of foreign-data wrappers! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description ! ------------+-------------------+---------+--------------------------+-------------------+-------------+-------------! dummy | foreign_data_user | - | - | | | useless! postgresql | foreign_data_user | - | postgresql_fdw_validator | | | (2 rows) CREATE FOREIGN DATA WRAPPER foo;--- 210,220 ---- RESET ROLE; DROP ROLE regress_test_role_super; \dew+! List of foreign-data wrappers! Name | Owner | Validator | Access privileges | Options ! ------------+-------------------+--------------------------+-------------------+---------! dummy | foreign_data_user | - | | ! postgresql | foreign_data_user | postgresql_fdw_validator | | (2 rows) CREATE FOREIGN DATA WRAPPER foo;****************** 222,246 **** COMMENT ON SERVER s1 IS 'foreign server'; CREATE USER MAPPING FOR current_user SERVER s1; \dew+! List of foreign-data wrappers! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description ! ------------+-------------------+---------+--------------------------+-------------------+-------------+-------------! dummy | foreign_data_user | - | - | | | useless! foo | foreign_data_user | - | - | | | ! postgresql | foreign_data_user | - | postgresql_fdw_validator | | | (3 rows) \des+! List of foreign servers! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description ! ------+-------------------+----------------------+-------------------+------+---------+-------------+----------------! s1 | foreign_data_user | foo | | | | | foreign server (1 row) \deu+! List of user mappings! Server | User name | FDW Options ! --------+-------------------+------------- s1 | foreign_data_user | (1 row) --- 222,246 ---- COMMENT ON SERVER s1 IS 'foreign server'; CREATE USER MAPPING FOR current_user SERVER s1; \dew+! List of foreign-data wrappers! Name | Owner | Validator | Access privileges | Options ! ------------+-------------------+--------------------------+-------------------+---------! dummy | foreign_data_user | - | | ! foo | foreign_data_user | - | | ! postgresql | foreign_data_user | postgresql_fdw_validator | | (3 rows) \des+! List of foreign servers! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | Options ! ------+-------------------+----------------------+-------------------+------+---------+---------! s1 | foreign_data_user | foo | | | | (1 row) \deu+! List of user mappings! Server | User name | Options ! --------+-------------------+--------- s1 | foreign_data_user | (1 row) ****************** 258,280 **** DETAIL: drop cascades to server s1 drop cascades to user mapping for foreign_data_user \dew+! List of foreign-data wrappers! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description ! ------------+-------------------+---------+--------------------------+-------------------+-------------+-------------! dummy | foreign_data_user | - | - | | | useless! postgresql | foreign_data_user | - | postgresql_fdw_validator | | | (2 rows) \des+! List of foreign servers! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description ! ------+-------+----------------------+-------------------+------+---------+-------------+------------- (0 rows) \deu+! List of user mappings! Server | User name | FDW Options ! --------+-----------+------------- (0 rows) -- exercise CREATE SERVER--- 258,280 ---- DETAIL: drop cascades to server s1 drop cascades to user mapping for foreign_data_user \dew+! List of foreign-data wrappers! Name | Owner | Validator | Access privileges | Options ! ------------+-------------------+--------------------------+-------------------+---------! dummy | foreign_data_user | - | | ! postgresql | foreign_data_user | postgresql_fdw_validator | | (2 rows) \des+! List of foreign servers! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | Options ! ------+-------+----------------------+-------------------+------+---------+--------- (0 rows) \deu+! List of user mappings! Server | User name | Options ! --------+-----------+--------- (0 rows) -- exercise CREATE SERVER****************** 295,311 **** HINT: Valid options in this context are: authtype, service, connect_timeout, dbname, host, hostaddr, port, tty, options, requiressl, sslmode, gsslib CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (host 'localhost', dbname 's8db'); \des+! List of foreign servers! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description ! ------+-------------------+----------------------+-------------------+--------+---------+-----------------------------------+-------------! s1 | foreign_data_user | foo | | | | | ! s2 | foreign_data_user | foo | | | | (host 'a', dbname 'b') | ! s3 | foreign_data_user | foo | | oracle | | | ! s4 | foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') | ! s5 | foreign_data_user | foo | | | 15.0 | | ! s6 | foreign_data_user | foo | | | 16.0 | (host 'a', dbname 'b') | ! s7 | foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') | ! s8 | foreign_data_user | postgresql | | | | (host 'localhost', dbname 's8db') | (8 rows) SET ROLE regress_test_role;--- 295,311 ---- HINT: Valid options in this context are: authtype, service, connect_timeout, dbname, host, hostaddr, port, tty, options, requiressl, sslmode, gsslib CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (host 'localhost', dbname 's8db'); \des+! List of foreign servers! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | Options ! ------+-------------------+----------------------+-------------------+--------+---------+------------------------------! s1 | foreign_data_user | foo | | | | ! s2 | foreign_data_user | foo | | | | {host=a,dbname=b}! s3 | foreign_data_user | foo | | oracle | | ! s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b}! s5 | foreign_data_user | foo | | | 15.0 | ! s6 | foreign_data_user | foo | | | 16.0 | {host=a,dbname=b}! s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b}! s8 | foreign_data_user | postgresql | | | | {host=localhost,dbname=s8db} (8 rows) SET ROLE regress_test_role;****************** 317,334 **** CREATE SERVER t1 FOREIGN DATA WRAPPER foo; RESET ROLE; \des+! List of foreign servers! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description ! ------+-------------------+----------------------+-------------------+--------+---------+-----------------------------------+-------------! s1 | foreign_data_user | foo | | | | | ! s2 | foreign_data_user | foo | | | | (host 'a', dbname 'b') | ! s3 | foreign_data_user | foo | | oracle | | | ! s4 | foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') | ! s5 | foreign_data_user | foo | | | 15.0 | | ! s6 | foreign_data_user | foo | | | 16.0 | (host 'a', dbname 'b') | ! s7 | foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') | ! s8 | foreign_data_user | postgresql | | | | (host 'localhost', dbname 's8db') | ! t1 | regress_test_role | foo | | | | | (9 rows) REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_test_role;--- 317,334 ---- CREATE SERVER t1 FOREIGN DATA WRAPPER foo; RESET ROLE; \des+! List of foreign servers! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | Options ! ------+-------------------+----------------------+-------------------+--------+---------+------------------------------! s1 | foreign_data_user | foo | | | | ! s2 | foreign_data_user | foo | | | | {host=a,dbname=b}! s3 | foreign_data_user | foo | | oracle | | ! s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b}! s5 | foreign_data_user | foo | | | 15.0 | ! s6 | foreign_data_user | foo | | | 16.0 | {host=a,dbname=b}! s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b}! s8 | foreign_data_user | postgresql | | | | {host=localhost,dbname=s8db}! t1 | regress_test_role | foo | | | | (9 rows) REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_test_role;****************** 341,359 **** SET ROLE regress_test_role; CREATE SERVER t2 FOREIGN DATA WRAPPER foo; \des+! List of foreign servers! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description ! ------+-------------------+----------------------+-------------------+--------+---------+-----------------------------------+-------------! s1 | foreign_data_user | foo | | | | | ! s2 | foreign_data_user | foo | | | | (host 'a', dbname 'b') | ! s3 | foreign_data_user | foo | | oracle | | | ! s4 | foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') | ! s5 | foreign_data_user | foo | | | 15.0 | | ! s6 | foreign_data_user | foo | | | 16.0 | (host 'a', dbname 'b') | ! s7 | foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') | ! s8 | foreign_data_user | postgresql | | | | (host 'localhost', dbname 's8db') | ! t1 | regress_test_role | foo | | | | | ! t2 | regress_test_role | foo | | | | | (10 rows) RESET ROLE;--- 341,359 ---- SET ROLE regress_test_role; CREATE SERVER t2 FOREIGN DATA WRAPPER foo; \des+! List of foreign servers! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | Options ! ------+-------------------+----------------------+-------------------+--------+---------+------------------------------! s1 | foreign_data_user | foo | | | | ! s2 | foreign_data_user | foo | | | | {host=a,dbname=b}! s3 | foreign_data_user | foo | | oracle | | ! s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b}! s5 | foreign_data_user | foo | | | 15.0 | ! s6 | foreign_data_user | foo | | | 16.0 | {host=a,dbname=b}! s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b}! s8 | foreign_data_user | postgresql | | | | {host=localhost,dbname=s8db}! t1 | regress_test_role | foo | | | | ! t2 | regress_test_role | foo | | | | (10 rows) RESET ROLE;****************** 371,391 **** GRANT USAGE ON FOREIGN SERVER s1 TO regress_test_role; GRANT USAGE ON FOREIGN SERVER s6 TO regress_test_role2 WITH GRANT OPTION; \des+! List of foreign servers! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description ! ------+-------------------+----------------------+-----------------------------------------+--------+---------+-----------------------------------+-------------! s1 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user +| | 1.0 | (servername 's1') | ! | | | regress_test_role=U/foreign_data_user | | | | ! s2 | foreign_data_user | foo | | | 1.1 | (host 'a', dbname 'b') | ! s3 | foreign_data_user | foo | | oracle | | ("tns name" 'orcl', port '1521') | ! s4 | foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') | ! s5 | foreign_data_user | foo | | | 15.0 | | ! s6 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user +| | 16.0 | (host 'a', dbname 'b') | ! | | | regress_test_role2=U*/foreign_data_user | | | | ! s7 | foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') | ! s8 | foreign_data_user | postgresql | | | | (host 'localhost', dbname 's8db') | ! t1 | regress_test_role | foo | | | | | ! t2 | regress_test_role | foo | | | | | (10 rows) SET ROLE regress_test_role;--- 371,391 ---- GRANT USAGE ON FOREIGN SERVER s1 TO regress_test_role; GRANT USAGE ON FOREIGN SERVER s6 TO regress_test_role2 WITH GRANT OPTION; \des+! List of foreign servers! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | Options ! ------+-------------------+----------------------+-----------------------------------------+--------+---------+------------------------------! s1 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user | | 1.0 | {servername=s1}! : regress_test_role=U/foreign_data_user ! s2 | foreign_data_user | foo | | | 1.1 | {host=a,dbname=b}! s3 | foreign_data_user | foo | | oracle | | {"tns name=orcl",port=1521}! s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b}! s5 | foreign_data_user | foo | | | 15.0 | ! s6 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user | | 16.0 | {host=a,dbname=b}! : regress_test_role2=U*/foreign_data_user ! s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b}! s8 | foreign_data_user | postgresql | | | | {host=localhost,dbname=s8db}! t1 | regress_test_role | foo | | | | ! t2 | regress_test_role | foo | | | | (10 rows) SET ROLE regress_test_role;****************** 422,461 **** DETAIL: owner of server s1 privileges for foreign-data wrapper foo \des+! List of foreign servers! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description ! ------+-----------------------+----------------------+-----------------------------------------+--------+---------+--------------------------------------+-------------! s1 | regress_test_indirect | foo | foreign_data_user=U/foreign_data_user +| | 1.1 | (servername 's1') | ! | | | regress_test_role=U/foreign_data_user | | | | ! s2 | foreign_data_user | foo | | | 1.1 | (host 'a', dbname 'b') | ! s3 | foreign_data_user | foo | | oracle | | ("tns name" 'orcl', port '1521') | ! s4 | foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') | ! s5 | foreign_data_user | foo | | | 15.0 | | ! s6 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user +| | 16.0 | (host 'a', dbname 'b') | ! | | | regress_test_role2=U*/foreign_data_user | | | | ! s7 | foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') | ! s8 | foreign_data_user | postgresql | | | | (dbname 'db1', connect_timeout '30') | ! t1 | regress_test_role | foo | | | | | ! t2 | regress_test_role | foo | | | | | (10 rows) ALTER SERVER s8 RENAME to s8new; \des+! List of foreign servers! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description ! -------+-----------------------+----------------------+-----------------------------------------+--------+---------+--------------------------------------+-------------! s1 | regress_test_indirect | foo | foreign_data_user=U/foreign_data_user +| | 1.1 | (servername 's1') | ! | | | regress_test_role=U/foreign_data_user | | | | ! s2 | foreign_data_user | foo | | | 1.1 | (host 'a', dbname 'b') | ! s3 | foreign_data_user | foo | | oracle | | ("tns name" 'orcl', port '1521') | ! s4 | foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') | ! s5 | foreign_data_user | foo | | | 15.0 | | ! s6 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user +| | 16.0 | (host 'a', dbname 'b') | ! | | | regress_test_role2=U*/foreign_data_user | | | | ! s7 | foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') | ! s8new | foreign_data_user | postgresql | | | | (dbname 'db1', connect_timeout '30') | ! t1 | regress_test_role | foo | | | | | ! t2 | regress_test_role | foo | | | | | (10 rows) ALTER SERVER s8new RENAME to s8;--- 422,461 ---- DETAIL: owner of server s1 privileges for foreign-data wrapper foo \des+! List of foreign servers! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | Options ! ------+-----------------------+----------------------+-----------------------------------------+--------+---------+---------------------------------! s1 | regress_test_indirect | foo | foreign_data_user=U/foreign_data_user | | 1.1 | {servername=s1}! : regress_test_role=U/foreign_data_user ! s2 | foreign_data_user | foo | | | 1.1 | {host=a,dbname=b}! s3 | foreign_data_user | foo | | oracle | | {"tns name=orcl",port=1521}! s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b}! s5 | foreign_data_user | foo | | | 15.0 | ! s6 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user | | 16.0 | {host=a,dbname=b}! : regress_test_role2=U*/foreign_data_user ! s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b}! s8 | foreign_data_user | postgresql | | | | {dbname=db1,connect_timeout=30}! t1 | regress_test_role | foo | | | | ! t2 | regress_test_role | foo | | | | (10 rows) ALTER SERVER s8 RENAME to s8new; \des+! List of foreign servers! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | Options ! -------+-----------------------+----------------------+-----------------------------------------+--------+---------+---------------------------------! s1 | regress_test_indirect | foo | foreign_data_user=U/foreign_data_user | | 1.1 | {servername=s1}! : regress_test_role=U/foreign_data_user ! s2 | foreign_data_user | foo | | | 1.1 | {host=a,dbname=b}! s3 | foreign_data_user | foo | | oracle | | {"tns name=orcl",port=1521}! s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b}! s5 | foreign_data_user | foo | | | 15.0 | ! s6 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user | | 16.0 | {host=a,dbname=b}! : regress_test_role2=U*/foreign_data_user ! s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b}! s8new | foreign_data_user | postgresql | | | | {dbname=db1,connect_timeout=30}! t1 | regress_test_role | foo | | | | ! t2 | regress_test_role | foo | | | | (10 rows) ALTER SERVER s8new RENAME to s8;****************** 610,625 **** ALTER USER MAPPING FOR public SERVER t1 OPTIONS (ADD modified '1'); RESET ROLE; \deu+! List of user mappings! Server | User name | FDW Options ! --------+-------------------+---------------------------------- s4 | foreign_data_user | ! s4 | public | ("this mapping" 'is public')! s5 | regress_test_role | (modified '1')! s6 | regress_test_role | (username 'test')! s8 | foreign_data_user | (password 'public')! t1 | public | (modified '1')! t1 | regress_test_role | (username 'bob', password 'boo') (7 rows) -- DROP USER MAPPING--- 610,625 ---- ALTER USER MAPPING FOR public SERVER t1 OPTIONS (ADD modified '1'); RESET ROLE; \deu+! List of user mappings! Server | User name | Options ! --------+-------------------+----------------------------- s4 | foreign_data_user | ! s4 | public | {"this mapping=is public"}! s5 | regress_test_role | {modified=1}! s6 | regress_test_role | {username=test}! s8 | foreign_data_user | {password=public}! t1 | public | {modified=1}! t1 | regress_test_role | {username=bob,password=boo} (7 rows) -- DROP USER MAPPING****************** 679,701 **** COMMENT ON FOREIGN TABLE ft1 IS 'ft1'; COMMENT ON COLUMN ft1.c1 IS 'ft1.c1'; \d+ ft1! Foreign table "public.ft1"! Column | Type | Modifiers | FDW Options | Storage | Stats target | Description ! --------+---------+-----------+--------------------------------+----------+--------------+-------------! c1 | integer | not null | ("param 1" 'val1') | plain | | ft1.c1! c2 | text | | (param2 'val2', param3 'val3') | extended | | ! c3 | date | | | plain | | ! Server: s0! FDW Options: (delimiter ',', quote '"', "be quoted" 'value')! Has OIDs: no \det+! List of foreign tables! Schema | Table | Server | FDW Options | Description ! --------+-------+--------+-------------------------------------------------+-------------! public | ft1 | s0 | (delimiter ',', quote '"', "be quoted" 'value') | ft1! (1 row)! CREATE INDEX id_ft1_c2 ON ft1 (c2); -- ERROR ERROR: cannot create index on foreign table "ft1" SELECT * FROM ft1; -- ERROR--- 679,693 ---- COMMENT ON FOREIGN TABLE ft1 IS 'ft1'; COMMENT ON COLUMN ft1.c1 IS 'ft1.c1'; \d+ ft1! ?f? "public.ft1"! Column | Type | Storage | Description ! --------+---------+----------+-------------! c1 | integer | plain | ft1.c1! c2 | text | extended | ! c3 | date | plain | \det+! invalid command \det+ CREATE INDEX id_ft1_c2 ON ft1 (c2); -- ERROR ERROR: cannot create index on foreign table "ft1" SELECT * FROM ft1; -- ERROR****************** 734,754 **** ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 SET (n_distinct = 100); ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET STATISTICS -1; \d+ ft1! Foreign table "public.ft1"! Column | Type | Modifiers | FDW Options | Storage | Stats target | Description ! --------+---------+-----------+--------------------------------+----------+--------------+-------------! c1 | integer | not null | ("param 1" 'val1') | plain | 10000 | ! c2 | text | | (param2 'val2', param3 'val3') | extended | | ! c3 | date | | | plain | | ! c4 | integer | | | plain | | ! c6 | integer | not null | | plain | | ! c7 | integer | | (p1 'v1', p2 'v2') | plain | | ! c8 | text | | (p2 'V2') | extended | | ! c9 | integer | | | plain | | ! c10 | integer | | (p1 'v1') | plain | | ! Server: s0! FDW Options: (delimiter ',', quote '"', "be quoted" 'value')! Has OIDs: no -- can't change the column type if it's used elsewhere CREATE TABLE use_ft1_column_type (x ft1);--- 726,743 ---- ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 SET (n_distinct = 100); ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET STATISTICS -1; \d+ ft1! ?f? "public.ft1"! Column | Type | Storage | Description ! --------+---------+----------+-------------! c1 | integer | plain | ! c2 | text | extended | ! c3 | date | plain | ! c4 | integer | plain | ! c6 | integer | plain | ! c7 | integer | plain | ! c8 | text | extended | ! c9 | integer | plain | ! c10 | integer | plain | -- can't change the column type if it's used elsewhere CREATE TABLE use_ft1_column_type (x ft1);****************** 778,796 **** ALTER FOREIGN TABLE foreign_schema.ft1 RENAME c1 TO foreign_column_1; ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1; \d foreign_schema.foreign_table_1! Foreign table "foreign_schema.foreign_table_1"! Column | Type | Modifiers | FDW Options ! ------------------+---------+-----------+--------------------------------! foreign_column_1 | integer | not null | ("param 1" 'val1')! c2 | text | | (param2 'val2', param3 'val3')! c3 | date | | ! c4 | integer | | ! c6 | integer | not null | ! c7 | integer | | (p1 'v1', p2 'v2')! c8 | text | | (p2 'V2')! c10 | integer | | (p1 'v1')! Server: s0! FDW Options: (quote '~', "be quoted" 'value', escape '@') -- alter noexisting table ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c4 integer;--- 767,783 ---- ALTER FOREIGN TABLE foreign_schema.ft1 RENAME c1 TO foreign_column_1; ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1; \d foreign_schema.foreign_table_1! ?f? "foreign_schema.foreign_table_1"! Column | Type ! ------------------+---------! foreign_column_1 | integer! c2 | text! c3 | date! c4 | integer! c6 | integer! c7 | integer! c8 | text! c10 | integer -- alter noexisting table ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c4 integer;======================================================================*** /tmp/regress/expected/xmlmap_1.out 2014-03-18 03:36:46.000000000--- /tmp/regress/results/xmlmap.out 2014-10-28 17:14:10.673780148****************** 6,109 **** ALTER TABLE testxmlschema.test2 DROP COLUMN aaa; INSERT INTO testxmlschema.test2 VALUES (55, 'abc', 'def', 98.6, 2, 999, 0, '21:07', '2009-06-08 21:07:30', '2009-06-08', NULL, 'ABC', true, 'XYZ'); SELECT table_to_xml('testxmlschema.test1', false, false, '');! 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 table_to_xml('testxmlschema.test1', true, false, '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 table_to_xml('testxmlschema.test1', false, true, '');! 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 table_to_xml('testxmlschema.test1', true, true, '');! 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 table_to_xml('testxmlschema.test2', false, false, '');! 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 table_to_xmlschema('testxmlschema.test1', false, false, '');! 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 table_to_xmlschema('testxmlschema.test1', true, false, '');! 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 table_to_xmlschema('testxmlschema.test1', false, true, '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 table_to_xmlschema('testxmlschema.test1', true, true, '');! 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 table_to_xmlschema('testxmlschema.test2', false, false, '');! 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 table_to_xml_and_xmlschema('testxmlschema.test1', false, false, '');! 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 table_to_xml_and_xmlschema('testxmlschema.test1', true, false, '');! 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 table_to_xml_and_xmlschema('testxmlschema.test1', false, true, '');! 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 table_to_xml_and_xmlschema('testxmlschema.test1', true, true, '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 query_to_xml('SELECT * FROM testxmlschema.test1', false, false, '');! 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 query_to_xmlschema('SELECT * FROM testxmlschema.test1', false, false, '');! 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 query_to_xml_and_xmlschema('SELECT * FROM testxmlschema.test1', true, true, '');! 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. DECLARE xc CURSOR WITH HOLD FOR SELECT * FROM testxmlschema.test1 ORDER BY 1, 2; SELECT cursor_to_xml('xc'::refcursor, 5, false, true, '');! 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. MOVE FIRST IN xc; SELECT cursor_to_xml('xc'::refcursor, 5, true, false, '');! 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 cursor_to_xmlschema('xc'::refcursor, true, false, '');! 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 schema_to_xml('testxmlschema', false, true, '');! 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 schema_to_xml('testxmlschema', true, false, '');! 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 schema_to_xmlschema('testxmlschema', false, true, '');! 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 schema_to_xmlschema('testxmlschema', true, false, '');! 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 schema_to_xml_and_xmlschema('testxmlschema', true, true, '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.--- 6,1202 ---- ALTER TABLE testxmlschema.test2 DROP COLUMN aaa; INSERT INTO testxmlschema.test2 VALUES (55, 'abc', 'def', 98.6, 2, 999, 0, '21:07', '2009-06-08 21:07:30', '2009-06-08', NULL, 'ABC', true, 'XYZ'); SELECT table_to_xml('testxmlschema.test1', false, false, '');! table_to_xml ! ---------------------------------------------------------------! <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">! ! <row>! <a>1</a>! one! </row>! ! <row>! <a>2</a>! two! </row>! ! <row>! <a>-1</a>! </row>! ! </test1>! ! (1 row)! SELECT table_to_xml('testxmlschema.test1', true, false, 'foo');! table_to_xml ! ---------------------------------------------------------------------------! <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="foo">! ! <row>! <a>1</a>! one! </row>! ! <row>! <a>2</a>! two! </row>! ! <row>! <a>-1</a>! ! </row>! ! </test1>! ! (1 row)! SELECT table_to_xml('testxmlschema.test1', false, true, '');! table_to_xml ! ---------------------------------------------------------------! <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">! ! <a>1</a>! one! </test1>! ! <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">! ! <a>2</a>! two! </test1>! ! <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">! ! <a>-1</a>! </test1>! ! ! (1 row)! SELECT table_to_xml('testxmlschema.test1', true, true, '');! table_to_xml ! ---------------------------------------------------------------! <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">! ! <a>1</a>! one! </test1>! ! <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">! ! <a>2</a>! two! </test1>! ! <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">! ! <a>-1</a>! ! </test1>! ! ! (1 row)! SELECT table_to_xml('testxmlschema.test2', false, false, '');! table_to_xml ! ---------------------------------------------------------------! <test2 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">! ! <row>! <z>55</z>! <y>abc</y>! <x>def </x>! <w>98.60</w>! <v>2</v>! 999! <t>0</t>! <s>21:07:00</s>! <r>2009-06-08T21:07:30</r>! <q>2009-06-08</q>! <o>ABC</o>! <n>true</n>! <m>WFla</m>! </row>! ! </test2>! ! (1 row)! SELECT table_to_xmlschema('testxmlschema.test1', false, false, '');! table_to_xmlschema ! -----------------------------------------------------------------------------------------------------------------! <xsd:schema! xmlns:xsd="http://www.w3.org/2001/XMLSchema">! ! <xsd:simpleType name="INTEGER">! <xsd:restriction base="xsd:int">! <xsd:maxInclusive value="2147483647"/>! <xsd:minInclusive value="-2147483648"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="UDT.regression.pg_catalog.text">! <xsd:restriction base="xsd:string">! </xsd:restriction>! </xsd:simpleType>! ! <xsd:complexType name="RowType.regression.testxmlschema.test1">! <xsd:sequence>! <xsd:element name="a" type="INTEGER" minOccurs="0"></xsd:element>! <xsd:element name="b" type="UDT.regression.pg_catalog.text" minOccurs="0"></xsd:element>! </xsd:sequence>! </xsd:complexType>! ! <xsd:complexType name="TableType.regression.testxmlschema.test1">! <xsd:sequence>! <xsd:element name="row" type="RowType.regression.testxmlschema.test1" minOccurs="0" maxOccurs="unbounded"/>! </xsd:sequence>! </xsd:complexType>! ! <xsd:element name="test1" type="TableType.regression.testxmlschema.test1"/>! ! </xsd:schema>! (1 row)! SELECT table_to_xmlschema('testxmlschema.test1', true, false, '');! table_to_xmlschema ! -----------------------------------------------------------------------------------------------------------------! <xsd:schema! xmlns:xsd="http://www.w3.org/2001/XMLSchema">! ! <xsd:simpleType name="INTEGER">! <xsd:restriction base="xsd:int">! <xsd:maxInclusive value="2147483647"/>! <xsd:minInclusive value="-2147483648"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="UDT.regression.pg_catalog.text">! <xsd:restriction base="xsd:string">! </xsd:restriction>! </xsd:simpleType>! ! <xsd:complexType name="RowType.regression.testxmlschema.test1">! <xsd:sequence>! <xsd:element name="a" type="INTEGER" nillable="true"></xsd:element>! <xsd:element name="b" type="UDT.regression.pg_catalog.text" nillable="true"></xsd:element>! </xsd:sequence>! </xsd:complexType>! ! <xsd:complexType name="TableType.regression.testxmlschema.test1">! <xsd:sequence>! <xsd:element name="row" type="RowType.regression.testxmlschema.test1" minOccurs="0" maxOccurs="unbounded"/>! </xsd:sequence>! </xsd:complexType>! ! <xsd:element name="test1" type="TableType.regression.testxmlschema.test1"/>! ! </xsd:schema>! (1 row)! SELECT table_to_xmlschema('testxmlschema.test1', false, true, 'foo');! table_to_xmlschema ! ----------------------------------------------------------------------------------------------! <xsd:schema! xmlns:xsd="http://www.w3.org/2001/XMLSchema"! targetNamespace="foo"! elementFormDefault="qualified">! ! <xsd:simpleType name="INTEGER">! <xsd:restriction base="xsd:int">! <xsd:maxInclusive value="2147483647"/>! <xsd:minInclusive value="-2147483648"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="UDT.regression.pg_catalog.text">! <xsd:restriction base="xsd:string">! </xsd:restriction>! </xsd:simpleType>! ! <xsd:complexType name="RowType.regression.testxmlschema.test1">! <xsd:sequence>! <xsd:element name="a" type="INTEGER" minOccurs="0"></xsd:element>! <xsd:element name="b" type="UDT.regression.pg_catalog.text" minOccurs="0"></xsd:element>! </xsd:sequence>! </xsd:complexType>! ! <xsd:element name="test1" type="RowType.regression.testxmlschema.test1"/>! ! </xsd:schema>! (1 row)! SELECT table_to_xmlschema('testxmlschema.test1', true, true, '');! table_to_xmlschema ! ------------------------------------------------------------------------------------------------! <xsd:schema! xmlns:xsd="http://www.w3.org/2001/XMLSchema">! ! <xsd:simpleType name="INTEGER">! <xsd:restriction base="xsd:int">! <xsd:maxInclusive value="2147483647"/>! <xsd:minInclusive value="-2147483648"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="UDT.regression.pg_catalog.text">! <xsd:restriction base="xsd:string">! </xsd:restriction>! </xsd:simpleType>! ! <xsd:complexType name="RowType.regression.testxmlschema.test1">! <xsd:sequence>! <xsd:element name="a" type="INTEGER" nillable="true"></xsd:element>! <xsd:element name="b" type="UDT.regression.pg_catalog.text" nillable="true"></xsd:element>! </xsd:sequence>! </xsd:complexType>! ! <xsd:element name="test1" type="RowType.regression.testxmlschema.test1"/>! ! </xsd:schema>! (1 row)! SELECT table_to_xmlschema('testxmlschema.test2', false, false, '');! table_to_xmlschema ! -----------------------------------------------------------------------------------------------------------------! <xsd:schema! xmlns:xsd="http://www.w3.org/2001/XMLSchema">! ! <xsd:simpleType name="INTEGER">! <xsd:restriction base="xsd:int">! <xsd:maxInclusive value="2147483647"/>! <xsd:minInclusive value="-2147483648"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="VARCHAR">! <xsd:restriction base="xsd:string">! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="CHAR">! <xsd:restriction base="xsd:string">! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="NUMERIC">! </xsd:simpleType>! ! <xsd:simpleType name="SMALLINT">! <xsd:restriction base="xsd:short">! <xsd:maxInclusive value="32767"/>! <xsd:minInclusive value="-32768"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="BIGINT">! <xsd:restriction base="xsd:long">! <xsd:maxInclusive value="9223372036854775807"/>! <xsd:minInclusive value="-9223372036854775808"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="REAL">! <xsd:restriction base="xsd:float"></xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="TIME">! <xsd:restriction base="xsd:time">! <xsd:pattern value="\p{Nd}{2}:\p{Nd}{2}:\p{Nd}{2}(.\p{Nd}+)?"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="TIMESTAMP">! <xsd:restriction base="xsd:dateTime">! <xsd:pattern value="\p{Nd}{4}-\p{Nd}{2}-\p{Nd}{2}T\p{Nd}{2}:\p{Nd}{2}:\p{Nd}{2}(.\p{Nd}+)?"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="DATE">! <xsd:restriction base="xsd:date">! <xsd:pattern value="\p{Nd}{4}-\p{Nd}{2}-\p{Nd}{2}"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:complexType mixed="true">! <xsd:sequence>! <xsd:any name="element" minOccurs="0" maxOccurs="unbounded" processContents="skip"/>! </xsd:sequence>! </xsd:complexType>! ! <xsd:simpleType name="Domain.regression.public.testxmldomain">! <xsd:restriction base="VARCHAR"/>! </xsd:simpleType>! ! <xsd:simpleType name="BOOLEAN">! <xsd:restriction base="xsd:boolean"></xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="UDT.regression.pg_catalog.bytea">! <xsd:restriction base="xsd:base64Binary">! </xsd:restriction>! </xsd:simpleType>! ! <xsd:complexType name="RowType.regression.testxmlschema.test2">! <xsd:sequence>! <xsd:element name="z" type="INTEGER" minOccurs="0"></xsd:element>! <xsd:element name="y" type="VARCHAR" minOccurs="0"></xsd:element>! <xsd:element name="x" type="CHAR" minOccurs="0"></xsd:element>! <xsd:element name="w" type="NUMERIC" minOccurs="0"></xsd:element>! <xsd:element name="v" type="SMALLINT" minOccurs="0"></xsd:element>! <xsd:element name="u" type="BIGINT" minOccurs="0"></xsd:element>! <xsd:element name="t" type="REAL" minOccurs="0"></xsd:element>! <xsd:element name="s" type="TIME" minOccurs="0"></xsd:element>! <xsd:element name="r" type="TIMESTAMP" minOccurs="0"></xsd:element>! <xsd:element name="q" type="DATE" minOccurs="0"></xsd:element>! <xsd:element name="p" type="XML" minOccurs="0"></xsd:element>! <xsd:element name="o" type="Domain.regression.public.testxmldomain" minOccurs="0"></xsd:element>! <xsd:element name="n" type="BOOLEAN" minOccurs="0"></xsd:element>! <xsd:element name="m" type="UDT.regression.pg_catalog.bytea" minOccurs="0"></xsd:element>! </xsd:sequence>! </xsd:complexType>! ! <xsd:complexType name="TableType.regression.testxmlschema.test2">! <xsd:sequence>! <xsd:element name="row" type="RowType.regression.testxmlschema.test2" minOccurs="0" maxOccurs="unbounded"/>! </xsd:sequence>! </xsd:complexType>! ! <xsd:element name="test2" type="TableType.regression.testxmlschema.test2"/>! ! </xsd:schema>! (1 row)! SELECT table_to_xml_and_xmlschema('testxmlschema.test1', false, false, '');! table_to_xml_and_xmlschema ! -----------------------------------------------------------------------------------------------------------------! <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="#">! ! <xsd:schema! xmlns:xsd="http://www.w3.org/2001/XMLSchema">! ! <xsd:simpleType name="INTEGER">! <xsd:restriction base="xsd:int">! <xsd:maxInclusive value="2147483647"/>! <xsd:minInclusive value="-2147483648"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="UDT.regression.pg_catalog.text">! <xsd:restriction base="xsd:string">! </xsd:restriction>! </xsd:simpleType>! ! <xsd:complexType name="RowType.regression.testxmlschema.test1">! <xsd:sequence>! <xsd:element name="a" type="INTEGER" minOccurs="0"></xsd:element>! <xsd:element name="b" type="UDT.regression.pg_catalog.text" minOccurs="0"></xsd:element>! </xsd:sequence>! </xsd:complexType>! ! <xsd:complexType name="TableType.regression.testxmlschema.test1">! <xsd:sequence>! <xsd:element name="row" type="RowType.regression.testxmlschema.test1" minOccurs="0" maxOccurs="unbounded"/>! </xsd:sequence>! </xsd:complexType>! ! <xsd:element name="test1" type="TableType.regression.testxmlschema.test1"/>! ! </xsd:schema>! ! <row>! <a>1</a>! one! </row>! ! <row>! <a>2</a>! two! </row>! ! <row>! <a>-1</a>! </row>! ! </test1>! ! (1 row)! SELECT table_to_xml_and_xmlschema('testxmlschema.test1', true, false, '');! table_to_xml_and_xmlschema ! -----------------------------------------------------------------------------------------------------------------! <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="#">! ! <xsd:schema! xmlns:xsd="http://www.w3.org/2001/XMLSchema">! ! <xsd:simpleType name="INTEGER">! <xsd:restriction base="xsd:int">! <xsd:maxInclusive value="2147483647"/>! <xsd:minInclusive value="-2147483648"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="UDT.regression.pg_catalog.text">! <xsd:restriction base="xsd:string">! </xsd:restriction>! </xsd:simpleType>! ! <xsd:complexType name="RowType.regression.testxmlschema.test1">! <xsd:sequence>! <xsd:element name="a" type="INTEGER" nillable="true"></xsd:element>! <xsd:element name="b" type="UDT.regression.pg_catalog.text" nillable="true"></xsd:element>! </xsd:sequence>! </xsd:complexType>! ! <xsd:complexType name="TableType.regression.testxmlschema.test1">! <xsd:sequence>! <xsd:element name="row" type="RowType.regression.testxmlschema.test1" minOccurs="0" maxOccurs="unbounded"/>! </xsd:sequence>! </xsd:complexType>! ! <xsd:element name="test1" type="TableType.regression.testxmlschema.test1"/>! ! </xsd:schema>! ! <row>! <a>1</a>! one! </row>! ! <row>! <a>2</a>! two! </row>! ! <row>! <a>-1</a>! ! </row>! ! </test1>! ! (1 row)! SELECT table_to_xml_and_xmlschema('testxmlschema.test1', false, true, '');! table_to_xml_and_xmlschema ! ----------------------------------------------------------------------------------------------! <xsd:schema! xmlns:xsd="http://www.w3.org/2001/XMLSchema">! ! <xsd:simpleType name="INTEGER">! <xsd:restriction base="xsd:int">! <xsd:maxInclusive value="2147483647"/>! <xsd:minInclusive value="-2147483648"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="UDT.regression.pg_catalog.text">! <xsd:restriction base="xsd:string">! </xsd:restriction>! </xsd:simpleType>! ! <xsd:complexType name="RowType.regression.testxmlschema.test1">! <xsd:sequence>! <xsd:element name="a" type="INTEGER" minOccurs="0"></xsd:element>! <xsd:element name="b" type="UDT.regression.pg_catalog.text" minOccurs="0"></xsd:element>! </xsd:sequence>! </xsd:complexType>! ! <xsd:element name="test1" type="RowType.regression.testxmlschema.test1"/>! ! </xsd:schema>! ! <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">! ! <a>1</a>! one! </test1>! ! <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">! ! <a>2</a>! two! </test1>! ! <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">! ! <a>-1</a>! </test1>! ! ! (1 row)! SELECT table_to_xml_and_xmlschema('testxmlschema.test1', true, true, 'foo');! table_to_xml_and_xmlschema ! ------------------------------------------------------------------------------------------------! <xsd:schema! xmlns:xsd="http://www.w3.org/2001/XMLSchema"! targetNamespace="foo"! elementFormDefault="qualified">! ! <xsd:simpleType name="INTEGER">! <xsd:restriction base="xsd:int">! <xsd:maxInclusive value="2147483647"/>! <xsd:minInclusive value="-2147483648"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="UDT.regression.pg_catalog.text">! <xsd:restriction base="xsd:string">! </xsd:restriction>! </xsd:simpleType>! ! <xsd:complexType name="RowType.regression.testxmlschema.test1">! <xsd:sequence>! <xsd:element name="a" type="INTEGER" nillable="true"></xsd:element>! <xsd:element name="b" type="UDT.regression.pg_catalog.text" nillable="true"></xsd:element>! </xsd:sequence>! </xsd:complexType>! ! <xsd:element name="test1" type="RowType.regression.testxmlschema.test1"/>! ! </xsd:schema>! ! <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="foo">! ! <a>1</a>! one! </test1>! ! <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="foo">! ! <a>2</a>! two! </test1>! ! <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="foo">! ! <a>-1</a>! ! </test1>! ! ! (1 row)! SELECT query_to_xml('SELECT * FROM testxmlschema.test1', false, false, '');! query_to_xml ! ---------------------------------------------------------------! <table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">! ! <row>! <a>1</a>! one! </row>! ! <row>! <a>2</a>! two! </row>! ! <row>! <a>-1</a>! </row>! ! </table>! ! (1 row)! SELECT query_to_xmlschema('SELECT * FROM testxmlschema.test1', false, false, '');! query_to_xmlschema ! ----------------------------------------------------------------------------------------------! <xsd:schema! xmlns:xsd="http://www.w3.org/2001/XMLSchema">! ! <xsd:simpleType name="INTEGER">! <xsd:restriction base="xsd:int">! <xsd:maxInclusive value="2147483647"/>! <xsd:minInclusive value="-2147483648"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="UDT.regression.pg_catalog.text">! <xsd:restriction base="xsd:string">! </xsd:restriction>! </xsd:simpleType>! ! <xsd:complexType name="RowType">! <xsd:sequence>! <xsd:element name="a" type="INTEGER" minOccurs="0"></xsd:element>! <xsd:element name="b" type="UDT.regression.pg_catalog.text" minOccurs="0"></xsd:element>! </xsd:sequence>! </xsd:complexType>! ! <xsd:complexType name="TableType">! <xsd:sequence>! <xsd:element name="row" type="RowType" minOccurs="0" maxOccurs="unbounded"/>! </xsd:sequence>! </xsd:complexType>! ! <xsd:element name="table" type="TableType"/>! ! </xsd:schema>! (1 row)! SELECT query_to_xml_and_xmlschema('SELECT * FROM testxmlschema.test1', true, true, '');! query_to_xml_and_xmlschema ! ------------------------------------------------------------------------------------------------! <xsd:schema! xmlns:xsd="http://www.w3.org/2001/XMLSchema">! ! <xsd:simpleType name="INTEGER">! <xsd:restriction base="xsd:int">! <xsd:maxInclusive value="2147483647"/>! <xsd:minInclusive value="-2147483648"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="UDT.regression.pg_catalog.text">! <xsd:restriction base="xsd:string">! </xsd:restriction>! </xsd:simpleType>! ! <xsd:complexType name="RowType">! <xsd:sequence>! <xsd:element name="a" type="INTEGER" nillable="true"></xsd:element>! <xsd:element name="b" type="UDT.regression.pg_catalog.text" nillable="true"></xsd:element>! </xsd:sequence>! </xsd:complexType>! ! <xsd:element name="row" type="RowType"/>! ! </xsd:schema>! ! <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">! ! <a>1</a>! one! </row>! ! <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">! ! <a>2</a>! two! </row>! ! <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">! ! <a>-1</a>! ! </row>! ! ! (1 row)! DECLARE xc CURSOR WITH HOLD FOR SELECT * FROM testxmlschema.test1 ORDER BY 1, 2; SELECT cursor_to_xml('xc'::refcursor, 5, false, true, '');! cursor_to_xml ! -------------------------------------------------------------! <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">! ! <a>-1</a>! </row>! ! <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">! ! <a>1</a>! one! </row>! ! <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">! ! <a>2</a>! two! </row>! ! ! (1 row)! MOVE FIRST IN xc; SELECT cursor_to_xml('xc'::refcursor, 5, true, false, '');! cursor_to_xml ! ---------------! <row>! <a>1</a>! one! </row>! ! <row>! <a>2</a>! two! </row>! ! ! (1 row)! SELECT cursor_to_xmlschema('xc'::refcursor, true, false, '');! cursor_to_xmlschema ! ------------------------------------------------------------------------------------------------! <xsd:schema! xmlns:xsd="http://www.w3.org/2001/XMLSchema">! ! <xsd:simpleType name="INTEGER">! <xsd:restriction base="xsd:int">! <xsd:maxInclusive value="2147483647"/>! <xsd:minInclusive value="-2147483648"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="UDT.regression.pg_catalog.text">! <xsd:restriction base="xsd:string">! </xsd:restriction>! </xsd:simpleType>! ! <xsd:complexType name="RowType">! <xsd:sequence>! <xsd:element name="a" type="INTEGER" nillable="true"></xsd:element>! <xsd:element name="b" type="UDT.regression.pg_catalog.text" nillable="true"></xsd:element>! </xsd:sequence>! </xsd:complexType>! ! <xsd:complexType name="TableType">! <xsd:sequence>! <xsd:element name="row" type="RowType" minOccurs="0" maxOccurs="unbounded"/>! </xsd:sequence>! </xsd:complexType>! ! <xsd:element name="table" type="TableType"/>! ! </xsd:schema>! (1 row)! SELECT schema_to_xml('testxmlschema', false, true, '');! schema_to_xml ! -----------------------------------------------------------------------! <testxmlschema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">! ! <test1>! ! <a>1</a>! one! </test1>! ! <test1>! ! <a>2</a>! two! </test1>! ! <test1>! ! <a>-1</a>! </test1>! ! ! <test2>! ! <z>55</z>! <y>abc</y>! <x>def </x>! <w>98.60</w>! <v>2</v>! 999! <t>0</t>! <s>21:07:00</s>! <r>2009-06-08T21:07:30</r>! <q>2009-06-08</q>! <o>ABC</o>! <n>true</n>! <m>WFla</m>! </test2>! ! ! </testxmlschema>! ! (1 row)! SELECT schema_to_xml('testxmlschema', true, false, '');! schema_to_xml ! -----------------------------------------------------------------------! <testxmlschema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">! ! <test1>! ! <row>! <a>1</a>! one! </row>! ! <row>! <a>2</a>! two! </row>! ! <row>! <a>-1</a>! ! </row>! ! </test1>! ! <test2>! ! <row>! <z>55</z>! <y>abc</y>! <x>def </x>! <w>98.60</w>! <v>2</v>! 999! <t>0</t>! <s>21:07:00</s>! <r>2009-06-08T21:07:30</r>! <q>2009-06-08</q>! ! <o>ABC</o>! <n>true</n>! <m>WFla</m>! </row>! ! </test2>! ! </testxmlschema>! ! (1 row)! SELECT schema_to_xmlschema('testxmlschema', false, true, '');! schema_to_xmlschema ! -------------------------------------------------------------------------------------------------------------------! <xsd:schema! xmlns:xsd="http://www.w3.org/2001/XMLSchema">! ! <xsd:simpleType name="INTEGER">! <xsd:restriction base="xsd:int">! <xsd:maxInclusive value="2147483647"/>! <xsd:minInclusive value="-2147483648"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="UDT.regression.pg_catalog.text">! <xsd:restriction base="xsd:string">! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="VARCHAR">! <xsd:restriction base="xsd:string">! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="CHAR">! <xsd:restriction base="xsd:string">! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="NUMERIC">! </xsd:simpleType>! ! <xsd:simpleType name="SMALLINT">! <xsd:restriction base="xsd:short">! <xsd:maxInclusive value="32767"/>! <xsd:minInclusive value="-32768"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="BIGINT">! <xsd:restriction base="xsd:long">! <xsd:maxInclusive value="9223372036854775807"/>! <xsd:minInclusive value="-9223372036854775808"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="REAL">! <xsd:restriction base="xsd:float"></xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="TIME">! <xsd:restriction base="xsd:time">! <xsd:pattern value="\p{Nd}{2}:\p{Nd}{2}:\p{Nd}{2}(.\p{Nd}+)?"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="TIMESTAMP">! <xsd:restriction base="xsd:dateTime">! <xsd:pattern value="\p{Nd}{4}-\p{Nd}{2}-\p{Nd}{2}T\p{Nd}{2}:\p{Nd}{2}:\p{Nd}{2}(.\p{Nd}+)?"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="DATE">! <xsd:restriction base="xsd:date">! <xsd:pattern value="\p{Nd}{4}-\p{Nd}{2}-\p{Nd}{2}"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:complexType mixed="true">! <xsd:sequence>! <xsd:any name="element" minOccurs="0" maxOccurs="unbounded" processContents="skip"/>! </xsd:sequence>! </xsd:complexType>! ! <xsd:simpleType name="Domain.regression.public.testxmldomain">! <xsd:restriction base="VARCHAR"/>! </xsd:simpleType>! ! <xsd:simpleType name="BOOLEAN">! <xsd:restriction base="xsd:boolean"></xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="UDT.regression.pg_catalog.bytea">! <xsd:restriction base="xsd:base64Binary">! </xsd:restriction>! </xsd:simpleType>! ! <xsd:complexType name="SchemaType.regression.testxmlschema">! <xsd:sequence>! <xsd:element name="test1" type="RowType.regression.testxmlschema.test1" minOccurs="0" maxOccurs="unbounded"/>! <xsd:element name="test2" type="RowType.regression.testxmlschema.test2" minOccurs="0" maxOccurs="unbounded"/>! </xsd:sequence>! </xsd:complexType>! ! <xsd:element name="testxmlschema" type="SchemaType.regression.testxmlschema"/>! ! </xsd:schema>! (1 row)! SELECT schema_to_xmlschema('testxmlschema', true, false, '');! schema_to_xmlschema ! ---------------------------------------------------------------------------------------------------! <xsd:schema! xmlns:xsd="http://www.w3.org/2001/XMLSchema">! ! <xsd:simpleType name="INTEGER">! <xsd:restriction base="xsd:int">! <xsd:maxInclusive value="2147483647"/>! <xsd:minInclusive value="-2147483648"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="UDT.regression.pg_catalog.text">! <xsd:restriction base="xsd:string">! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="VARCHAR">! <xsd:restriction base="xsd:string">! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="CHAR">! <xsd:restriction base="xsd:string">! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="NUMERIC">! </xsd:simpleType>! ! <xsd:simpleType name="SMALLINT">! <xsd:restriction base="xsd:short">! <xsd:maxInclusive value="32767"/>! <xsd:minInclusive value="-32768"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="BIGINT">! <xsd:restriction base="xsd:long">! <xsd:maxInclusive value="9223372036854775807"/>! <xsd:minInclusive value="-9223372036854775808"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="REAL">! <xsd:restriction base="xsd:float"></xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="TIME">! <xsd:restriction base="xsd:time">! <xsd:pattern value="\p{Nd}{2}:\p{Nd}{2}:\p{Nd}{2}(.\p{Nd}+)?"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="TIMESTAMP">! <xsd:restriction base="xsd:dateTime">! <xsd:pattern value="\p{Nd}{4}-\p{Nd}{2}-\p{Nd}{2}T\p{Nd}{2}:\p{Nd}{2}:\p{Nd}{2}(.\p{Nd}+)?"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="DATE">! <xsd:restriction base="xsd:date">! <xsd:pattern value="\p{Nd}{4}-\p{Nd}{2}-\p{Nd}{2}"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:complexType mixed="true">! <xsd:sequence>! <xsd:any name="element" minOccurs="0" maxOccurs="unbounded" processContents="skip"/>! </xsd:sequence>! </xsd:complexType>! ! <xsd:simpleType name="Domain.regression.public.testxmldomain">! <xsd:restriction base="VARCHAR"/>! </xsd:simpleType>! ! <xsd:simpleType name="BOOLEAN">! <xsd:restriction base="xsd:boolean"></xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="UDT.regression.pg_catalog.bytea">! <xsd:restriction base="xsd:base64Binary">! </xsd:restriction>! </xsd:simpleType>! ! <xsd:complexType name="SchemaType.regression.testxmlschema">! <xsd:all>! <xsd:element name="test1" type="TableType.regression.testxmlschema.test1"/>! <xsd:element name="test2" type="TableType.regression.testxmlschema.test2"/>! </xsd:all>! </xsd:complexType>! ! <xsd:element name="testxmlschema" type="SchemaType.regression.testxmlschema"/>! ! </xsd:schema>! (1 row)! SELECT schema_to_xml_and_xmlschema('testxmlschema', true, true, 'foo');! schema_to_xml_and_xmlschema ! -------------------------------------------------------------------------------------------------------------------! <testxmlschema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="foo" xsi:schemaLocation="foo #">! ! <xsd:schema! xmlns:xsd="http://www.w3.org/2001/XMLSchema"! targetNamespace="foo"! elementFormDefault="qualified">! ! <xsd:simpleType name="INTEGER">! <xsd:restriction base="xsd:int">! <xsd:maxInclusive value="2147483647"/>! <xsd:minInclusive value="-2147483648"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="UDT.regression.pg_catalog.text">! <xsd:restriction base="xsd:string">! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="VARCHAR">! <xsd:restriction base="xsd:string">! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="CHAR">! <xsd:restriction base="xsd:string">! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="NUMERIC">! </xsd:simpleType>! ! <xsd:simpleType name="SMALLINT">! <xsd:restriction base="xsd:short">! <xsd:maxInclusive value="32767"/>! <xsd:minInclusive value="-32768"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="BIGINT">! <xsd:restriction base="xsd:long">! <xsd:maxInclusive value="9223372036854775807"/>! <xsd:minInclusive value="-9223372036854775808"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="REAL">! <xsd:restriction base="xsd:float"></xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="TIME">! <xsd:restriction base="xsd:time">! <xsd:pattern value="\p{Nd}{2}:\p{Nd}{2}:\p{Nd}{2}(.\p{Nd}+)?"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="TIMESTAMP">! <xsd:restriction base="xsd:dateTime">! <xsd:pattern value="\p{Nd}{4}-\p{Nd}{2}-\p{Nd}{2}T\p{Nd}{2}:\p{Nd}{2}:\p{Nd}{2}(.\p{Nd}+)?"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="DATE">! <xsd:restriction base="xsd:date">! <xsd:pattern value="\p{Nd}{4}-\p{Nd}{2}-\p{Nd}{2}"/>! </xsd:restriction>! </xsd:simpleType>! ! <xsd:complexType mixed="true">! <xsd:sequence>! <xsd:any name="element" minOccurs="0" maxOccurs="unbounded" processContents="skip"/>! </xsd:sequence>! </xsd:complexType>! ! <xsd:simpleType name="Domain.regression.public.testxmldomain">! <xsd:restriction base="VARCHAR"/>! </xsd:simpleType>! ! <xsd:simpleType name="BOOLEAN">! <xsd:restriction base="xsd:boolean"></xsd:restriction>! </xsd:simpleType>! ! <xsd:simpleType name="UDT.regression.pg_catalog.bytea">! <xsd:restriction base="xsd:base64Binary">! </xsd:restriction>! </xsd:simpleType>! ! <xsd:complexType name="SchemaType.regression.testxmlschema">! <xsd:sequence>! <xsd:element name="test1" type="RowType.regression.testxmlschema.test1" minOccurs="0" maxOccurs="unbounded"/>! <xsd:element name="test2" type="RowType.regression.testxmlschema.test2" minOccurs="0" maxOccurs="unbounded"/>! </xsd:sequence>! </xsd:complexType>! ! <xsd:element name="testxmlschema" type="SchemaType.regression.testxmlschema"/>! ! </xsd:schema>! ! <test1>! ! <a>1</a>! one! </test1>! ! <test1>! ! <a>2</a>! two! </test1>! ! <test1>! ! <a>-1</a>! ! </test1>! ! ! <test2>! ! <z>55</z>! <y>abc</y>! <x>def </x>! <w>98.60</w>! <v>2</v>! 999! <t>0</t>! <s>21:07:00</s>! <r>2009-06-08T21:07:30</r>! <q>2009-06-08</q>! ! <o>ABC</o>! <n>true</n>! <m>WFla</m>! </test2>! ! ! </testxmlschema>! ! (1 row)! ======================================================================*** /tmp/regress/expected/json.out 2014-03-18 03:36:46.000000000--- /tmp/regress/results/json.out 2014-10-28 17:14:10.779780129****************** 309,316 **** SELECT array_to_json(array_agg(q),true) from (select x as b, x * 2 as c from generate_series(1,3) x) q; array_to_json -----------------! [{"b":1,"c":2},+! {"b":2,"c":4},+ {"b":3,"c":6}] (1 row) --- 309,316 ---- SELECT array_to_json(array_agg(q),true) from (select x as b, x * 2 as c from generate_series(1,3) x) q; array_to_json -----------------! [{"b":1,"c":2},! {"b":2,"c":4}, {"b":3,"c":6}] (1 row) ****************** 368,384 **** generate_series(4,5) y) q; row_to_json -----------------------------------------------------! {"b":"a1", +! "c":4, + "z":[{"f1":1,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]}! {"b":"a1", +! "c":5, + "z":[{"f1":1,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]}! {"b":"a2", +! "c":4, + "z":[{"f1":2,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]}! {"b":"a2", +! "c":5, + "z":[{"f1":2,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]} (4 rows) --- 368,384 ---- generate_series(4,5) y) q; row_to_json -----------------------------------------------------! {"b":"a1",! "c":4, "z":[{"f1":1,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]}! {"b":"a1",! "c":5, "z":[{"f1":1,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]}! {"b":"a2",! "c":4, "z":[{"f1":2,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]}! {"b":"a2",! "c":5, "z":[{"f1":2,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]} (4 rows) ****************** 389,399 **** FROM rows q; row_to_json --------------! {"x":1, + "y":"txt1"}! {"x":2, + "y":"txt2"}! {"x":3, + "y":"txt3"} (3 rows) --- 389,399 ---- FROM rows q; row_to_json --------------! {"x":1, "y":"txt1"}! {"x":2, "y":"txt2"}! {"x":3, "y":"txt3"} (3 rows) ======================================================================*** /tmp/regress/expected/copy2.out 2014-03-18 03:36:46.000000000--- /tmp/regress/results/copy2.out 2014-10-28 17:14:13.424779645****************** 213,233 **** "It is ""perfect""."," " "", \copy y TO stdout (FORMAT CSV)! "Jackson, Sam",\h! "It is ""perfect"".", ! "", \copy y TO stdout (FORMAT CSV, QUOTE '''', DELIMITER '|')! Jackson, Sam|\h! It is "perfect".| ! ''| \copy y TO stdout (FORMAT CSV, FORCE_QUOTE (col2), ESCAPE E'\\')! "Jackson, Sam","\\h"! "It is \"perfect\"."," "! "", \copy y TO stdout (FORMAT CSV, FORCE_QUOTE *)! "Jackson, Sam","\h"! "It is ""perfect""."," "! "", --test that we read consecutive LFs properly CREATE TEMP TABLE testnl (a int, b text, c int); COPY testnl FROM stdin CSV;--- 213,225 ---- "It is ""perfect""."," " "", \copy y TO stdout (FORMAT CSV)! \copy: parse error at "(FORMAT" \copy y TO stdout (FORMAT CSV, QUOTE '''', DELIMITER '|')! \copy: parse error at "(FORMAT" \copy y TO stdout (FORMAT CSV, FORCE_QUOTE (col2), ESCAPE E'\\')! \copy: parse error at "(FORMAT" \copy y TO stdout (FORMAT CSV, FORCE_QUOTE *)! \copy: parse error at "(FORMAT" --test that we read consecutive LFs properly CREATE TEMP TABLE testnl (a int, b text, c int); COPY testnl FROM stdin CSV;======================================================================*** /tmp/regress/expected/prepare.out 2014-03-18 03:36:46.000000000--- /tmp/regress/results/prepare.out 2014-10-28 17:14:13.881779561****************** 154,173 **** ORDER BY name; name | statement | parameter_types ------+---------------------------------------------------------------------+--------------------------------------------------------! q2 | PREPARE q2(text) AS +| {text}! | SELECT datname, datistemplate, datallowconn +| ! | FROM pg_database WHERE datname = $1; | ! q3 | PREPARE q3(text, int, float, boolean, oid, smallint) AS +| {text,integer,"double precision",boolean,oid,smallint}! | SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR +| ! | ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int)+| ! | ORDER BY unique1; | ! q5 | PREPARE q5(int, text) AS +| {integer,text}! | SELECT * FROM tenk1 WHERE unique1 = $1 OR stringu1 = $2 +| ! | ORDER BY unique1; | ! q6 | PREPARE q6 AS +| {integer,name}! | SELECT * FROM tenk1 WHERE unique1 = $1 AND stringu1 = $2; | ! q7 | PREPARE q7(unknown) AS +| {path}! | SELECT * FROM road WHERE thepath = $1; | (5 rows) -- test DEALLOCATE ALL;--- 154,173 ---- ORDER BY name; name | statement | parameter_types ------+---------------------------------------------------------------------+--------------------------------------------------------! q2 | PREPARE q2(text) AS | {text}! : SELECT datname, datistemplate, datallowconn ! : FROM pg_database WHERE datname = $1; ! q3 | PREPARE q3(text, int, float, boolean, oid, smallint) AS | {text,integer,"double precision",boolean,oid,smallint}! : SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR ! : ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int) ! : ORDER BY unique1; ! q5 | PREPARE q5(int, text) AS | {integer,text}! : SELECT * FROM tenk1 WHERE unique1 = $1 OR stringu1 = $2 ! : ORDER BY unique1; ! q6 | PREPARE q6 AS | {integer,name}! : SELECT * FROM tenk1 WHERE unique1 = $1 AND stringu1 = $2; ! q7 | PREPARE q7(unknown) AS | {path}! : SELECT * FROM road WHERE thepath = $1; (5 rows) -- test DEALLOCATE ALL;======================================================================*** /tmp/regress/expected/alter_table.out 2014-03-18 03:36:46.000000000--- /tmp/regress/results/alter_table.out 2014-10-28 17:14:15.253779309****************** 220,226 **** c | integer | Check constraints: "con1foo" CHECK (a > 0)- Number of child tables: 1 (Use \d+ to list them.) \d constraint_rename_test2 Table "public.constraint_rename_test2"--- 220,225 ----****************** 247,253 **** Check constraints: "con1foo" CHECK (a > 0) "con2bar" CHECK (b > 0) NO INHERIT- Number of child tables: 1 (Use \d+ to list them.) \d constraint_rename_test2 Table "public.constraint_rename_test2"--- 246,251 ----****************** 276,282 **** Check constraints: "con1foo" CHECK (a > 0) "con2bar" CHECK (b > 0) NO INHERIT- Number of child tables: 1 (Use \d+ to list them.) \d constraint_rename_test2 Table "public.constraint_rename_test2"--- 274,279 ----****************** 1830,1836 **** a | numeric | Check constraints: "test_inh_check_a_check" CHECK (a::double precision > 10.2::double precision)- Number of child tables: 1 (Use \d+ to list them.) \d test_inh_check_child Table "public.test_inh_check_child"--- 1827,1832 ----****************** 2083,2126 **** CREATE TYPE test_type AS (a int); \d test_type Composite type "public.test_type"! Column | Type | Modifiers ! --------+---------+-----------! a | integer | ALTER TYPE nosuchtype ADD ATTRIBUTE b text; -- fails ERROR: relation "nosuchtype" does not exist ALTER TYPE test_type ADD ATTRIBUTE b text; \d test_type Composite type "public.test_type"! Column | Type | Modifiers ! --------+---------+-----------! a | integer | ! b | text | ALTER TYPE test_type ADD ATTRIBUTE b text; -- fails ERROR: column "b" of relation "test_type" already exists ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE varchar; \d test_type! Composite type "public.test_type"! Column | Type | Modifiers ! --------+-------------------+-----------! a | integer | ! b | character varying | ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE integer; \d test_type Composite type "public.test_type"! Column | Type | Modifiers ! --------+---------+-----------! a | integer | ! b | integer | ALTER TYPE test_type DROP ATTRIBUTE b; \d test_type Composite type "public.test_type"! Column | Type | Modifiers ! --------+---------+-----------! a | integer | ALTER TYPE test_type DROP ATTRIBUTE c; -- fails ERROR: column "c" of relation "test_type" does not exist--- 2079,2122 ---- CREATE TYPE test_type AS (a int); \d test_type Composite type "public.test_type"! Column | Type ! --------+---------! a | integer ALTER TYPE nosuchtype ADD ATTRIBUTE b text; -- fails ERROR: relation "nosuchtype" does not exist ALTER TYPE test_type ADD ATTRIBUTE b text; \d test_type Composite type "public.test_type"! Column | Type ! --------+---------! a | integer! b | text ALTER TYPE test_type ADD ATTRIBUTE b text; -- fails ERROR: column "b" of relation "test_type" already exists ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE varchar; \d test_type! Composite type "public.test_type"! Column | Type ! --------+-------------------! a | integer! b | character varying ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE integer; \d test_type Composite type "public.test_type"! Column | Type ! --------+---------! a | integer! b | integer ALTER TYPE test_type DROP ATTRIBUTE b; \d test_type Composite type "public.test_type"! Column | Type ! --------+---------! a | integer ALTER TYPE test_type DROP ATTRIBUTE c; -- fails ERROR: column "c" of relation "test_type" does not exist****************** 2129,2146 **** ALTER TYPE test_type DROP ATTRIBUTE a, ADD ATTRIBUTE d boolean; \d test_type Composite type "public.test_type"! Column | Type | Modifiers ! --------+---------+-----------! d | boolean | ALTER TYPE test_type RENAME ATTRIBUTE a TO aa; ERROR: column "a" does not exist ALTER TYPE test_type RENAME ATTRIBUTE d TO dd; \d test_type Composite type "public.test_type"! Column | Type | Modifiers ! --------+---------+-----------! dd | boolean | DROP TYPE test_type; CREATE TYPE test_type1 AS (a int, b text);--- 2125,2142 ---- ALTER TYPE test_type DROP ATTRIBUTE a, ADD ATTRIBUTE d boolean; \d test_type Composite type "public.test_type"! Column | Type ! --------+---------! d | boolean ALTER TYPE test_type RENAME ATTRIBUTE a TO aa; ERROR: column "a" does not exist ALTER TYPE test_type RENAME ATTRIBUTE d TO dd; \d test_type Composite type "public.test_type"! Column | Type ! --------+---------! dd | boolean DROP TYPE test_type; CREATE TYPE test_type1 AS (a int, b text);****************** 2152,2161 **** CREATE TABLE test_tbl2_subclass () INHERITS (test_tbl2); \d test_type2 Composite type "public.test_type2"! Column | Type | Modifiers ! --------+---------+-----------! a | integer | ! b | text | \d test_tbl2 Table "public.test_tbl2"--- 2148,2157 ---- CREATE TABLE test_tbl2_subclass () INHERITS (test_tbl2); \d test_type2 Composite type "public.test_type2"! Column | Type ! --------+---------! a | integer! b | text \d test_tbl2 Table "public.test_tbl2"****************** 2163,2170 **** --------+---------+----------- a | integer | b | text | - Number of child tables: 1 (Use \d+ to list them.)- Typed table of type: test_type2 ALTER TYPE test_type2 ADD ATTRIBUTE c text; -- fails ERROR: cannot alter type "test_type2" because it is the type of a typed table--- 2159,2164 ----****************** 2172,2182 **** ALTER TYPE test_type2 ADD ATTRIBUTE c text CASCADE; \d test_type2 Composite type "public.test_type2"! Column | Type | Modifiers ! --------+---------+-----------! a | integer | ! b | text | ! c | text | \d test_tbl2 Table "public.test_tbl2"--- 2166,2176 ---- ALTER TYPE test_type2 ADD ATTRIBUTE c text CASCADE; \d test_type2 Composite type "public.test_type2"! Column | Type ! --------+---------! a | integer! b | text! c | text \d test_tbl2 Table "public.test_tbl2"****************** 2185,2204 **** a | integer | b | text | c | text | - Number of child tables: 1 (Use \d+ to list them.)- Typed table of type: test_type2 ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar; -- fails ERROR: cannot alter type "test_type2" because it is the type of a typed table HINT: Use ALTER ... CASCADE to alter the typed tables too. ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar CASCADE; \d test_type2! Composite type "public.test_type2"! Column | Type | Modifiers ! --------+-------------------+-----------! a | integer | ! b | character varying | ! c | text | \d test_tbl2 Table "public.test_tbl2"--- 2179,2196 ---- a | integer | b | text | c | text | ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar; -- fails ERROR: cannot alter type "test_type2" because it is the type of a typed table HINT: Use ALTER ... CASCADE to alter the typed tables too. ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar CASCADE; \d test_type2! Composite type "public.test_type2"! Column | Type ! --------+-------------------! a | integer! b | character varying! c | text \d test_tbl2 Table "public.test_tbl2"****************** 2207,2214 **** a | integer | b | character varying | c | text | - Number of child tables: 1 (Use \d+ to list them.)- Typed table of type: test_type2 ALTER TYPE test_type2 DROP ATTRIBUTE b; -- fails ERROR: cannot alter type "test_type2" because it is the type of a typed table--- 2199,2204 ----****************** 2216,2225 **** ALTER TYPE test_type2 DROP ATTRIBUTE b CASCADE; \d test_type2 Composite type "public.test_type2"! Column | Type | Modifiers ! --------+---------+-----------! a | integer | ! c | text | \d test_tbl2 Table "public.test_tbl2"--- 2206,2215 ---- ALTER TYPE test_type2 DROP ATTRIBUTE b CASCADE; \d test_type2 Composite type "public.test_type2"! Column | Type ! --------+---------! a | integer! c | text \d test_tbl2 Table "public.test_tbl2"****************** 2227,2234 **** --------+---------+----------- a | integer | c | text | - Number of child tables: 1 (Use \d+ to list them.)- Typed table of type: test_type2 ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa; -- fails ERROR: cannot alter type "test_type2" because it is the type of a typed table--- 2217,2222 ----****************** 2236,2245 **** ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa CASCADE; \d test_type2 Composite type "public.test_type2"! Column | Type | Modifiers ! --------+---------+-----------! aa | integer | ! c | text | \d test_tbl2 Table "public.test_tbl2"--- 2224,2233 ---- ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa CASCADE; \d test_type2 Composite type "public.test_type2"! Column | Type ! --------+---------! aa | integer! c | text \d test_tbl2 Table "public.test_tbl2"****************** 2247,2254 **** --------+---------+----------- aa | integer | c | text | - Number of child tables: 1 (Use \d+ to list them.)- Typed table of type: test_type2 \d test_tbl2_subclass Table "public.test_tbl2_subclass"--- 2235,2240 ----======================================================================*** /tmp/regress/expected/with.out 2014-03-18 03:36:46.000000000--- /tmp/regress/results/with.out 2014-10-28 17:14:15.777779212****************** 279,295 **** SELECT pg_get_viewdef('vsubdepartment'::regclass, true); 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) --- 279,295 ---- SELECT pg_get_viewdef('vsubdepartment'::regclass, true); 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) ======================================================================

Thanks
Romu

В списке pgsql-general по дате отправления:

Предыдущее

От: "nurul [via PostgreSQL]"
Дата: 28 октября 2014 г., 22:17:23
Сообщение: Re: Log-shipping replication in one machine

Следующее

От: Adrian Klaver
Дата: 28 октября 2014 г., 22:24:00
Сообщение: Re: "can not able to find scan Function For making NoDB"

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Need guidance on regression.diffs

Предыдущее

Следующее