Thread: Need guidance on regression.diffs
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
-----------------------------------------
! <b>painted</b> Ocean. +
! <b>Water</b>, <b>water</b>, every where+
! And all the boards did shrink; +
<b>Water</b>, <b>water</b>, every
(1 row)
--- 610,618 ----
', to_tsquery('english', 'paint&water'));
ts_headline
-----------------------------------------
! <b>painted</b> Ocean.
! <b>Water</b>, <b>water</b>, every where
! And all the boards did shrink;
<b>Water</b>, <b>water</b>, every
(1 row)
***************
*** 629,637 ****
', to_tsquery('english', 'breath&motion&water'));
ts_headline
----------------------------------
! <b>breath</b> nor <b>motion</b>,+
! As idle as a painted Ship +
! Upon a painted Ocean. +
<b>Water</b>, <b>water</b>
(1 row)
--- 629,637 ----
', to_tsquery('english', 'breath&motion&water'));
ts_headline
----------------------------------
! <b>breath</b> nor <b>motion</b>,
! As idle as a painted Ship
! Upon a painted Ocean.
<b>Water</b>, <b>water</b>
(1 row)
***************
*** 648,656 ****
', to_tsquery('english', 'ocean'));
ts_headline
----------------------------------
! <b>Ocean</b>. +
! Water, water, every where +
! And all the boards did shrink;+
Water, water, every where
(1 row)
--- 648,656 ----
', to_tsquery('english', 'ocean'));
ts_headline
----------------------------------
! <b>Ocean</b>.
! 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> +
! <!-- some comment --> +
! <body> +
! <b>Sea</b> view wow <u><b>foo</b> bar</u> <i>qq</i> +
! <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>
! <!-- some comment -->
! <body>
! <b>Sea</b> view wow <u><b>foo</b> bar</u> <i>qq</i>
! <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 <b>Ocean</b>. +
! 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 <b>Ocean</b>.
! 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 <b>stuck</b>, 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. <b>Coleridge</b>
(1 row)
--- 721,733 ----
', to_tsquery('english', 'Coleridge & stuck'), 'MaxFragments=2');
ts_headline
----------------------------------------------
! after day, day after day,
! We <b>stuck</b>, 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. <b>Coleridge</b>
(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 <b>stuck</b>, 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. <b>Coleridge</b>
(1 row)
--- 765,777 ----
', to_tsquery('english', 'Coleridge & stuck'), 'MaxFragments=2,FragmentDelimiter=***');
ts_headline
--------------------------------------------
! after day, day after day,
! We <b>stuck</b>, 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. <b>Coleridge</b>
(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>
! <b>one</b>
! </row>
!
! <row>
! <a>2</a>
! <b>two</b>
! </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>
! <b>one</b>
! </row>
!
! <row>
! <a>2</a>
! <b>two</b>
! </row>
!
! <row>
! <a>-1</a>
! <b xsi:nil="true"/>
! </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>
! <b>one</b>
! </test1>
!
! <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
!
! <a>2</a>
! <b>two</b>
! </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>
! <b>one</b>
! </test1>
!
! <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
!
! <a>2</a>
! <b>two</b>
! </test1>
!
! <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
!
! <a>-1</a>
! <b xsi:nil="true"/>
! </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>
! <u>999</u>
! <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>
! <b>one</b>
! </row>
!
! <row>
! <a>2</a>
! <b>two</b>
! </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>
! <b>one</b>
! </row>
!
! <row>
! <a>2</a>
! <b>two</b>
! </row>
!
! <row>
! <a>-1</a>
! <b xsi:nil="true"/>
! </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>
! <b>one</b>
! </test1>
!
! <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
!
! <a>2</a>
! <b>two</b>
! </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>
! <b>one</b>
! </test1>
!
! <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="foo">
!
! <a>2</a>
! <b>two</b>
! </test1>
!
! <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="foo">
!
! <a>-1</a>
! <b xsi:nil="true"/>
! </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>
! <b>one</b>
! </row>
!
! <row>
! <a>2</a>
! <b>two</b>
! </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>
! <b>one</b>
! </row>
!
! <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
!
! <a>2</a>
! <b>two</b>
! </row>
!
! <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
!
! <a>-1</a>
! <b xsi:nil="true"/>
! </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>
! <b>one</b>
! </row>
!
! <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
!
! <a>2</a>
! <b>two</b>
! </row>
!
!
! (1 row)
!
MOVE FIRST IN xc;
SELECT cursor_to_xml('xc'::refcursor, 5, true, false, '');
! cursor_to_xml
! ---------------
! <row>
! <a>1</a>
! <b>one</b>
! </row>
!
! <row>
! <a>2</a>
! <b>two</b>
! </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>
! <b>one</b>
! </test1>
!
! <test1>
!
! <a>2</a>
! <b>two</b>
! </test1>
!
! <test1>
!
! <a>-1</a>
! </test1>
!
!
! <test2>
!
! <z>55</z>
! <y>abc</y>
! <x>def </x>
! <w>98.60</w>
! <v>2</v>
! <u>999</u>
! <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>
! <b>one</b>
! </row>
!
! <row>
! <a>2</a>
! <b>two</b>
! </row>
!
! <row>
! <a>-1</a>
! <b xsi:nil="true"/>
! </row>
!
! </test1>
!
! <test2>
!
! <row>
! <z>55</z>
! <y>abc</y>
! <x>def </x>
! <w>98.60</w>
! <v>2</v>
! <u>999</u>
! <t>0</t>
! <s>21:07:00</s>
! <r>2009-06-08T21:07:30</r>
! <q>2009-06-08</q>
! <p xsi:nil="true"/>
! <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>
! <b>one</b>
! </test1>
!
! <test1>
!
! <a>2</a>
! <b>two</b>
! </test1>
!
! <test1>
!
! <a>-1</a>
! <b xsi:nil="true"/>
! </test1>
!
!
! <test2>
!
! <z>55</z>
! <y>abc</y>
! <x>def </x>
! <w>98.60</w>
! <v>2</v>
! <u>999</u>
! <t>0</t>
! <s>21:07:00</s>
! <r>2009-06-08T21:07:30</r>
! <q>2009-06-08</q>
! <p xsi:nil="true"/>
! <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
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
-----------------------------------------
! <b>painted</b> Ocean. +
! <b>Water</b>, <b>water</b>, every where+
! And all the boards did shrink; +
<b>Water</b>, <b>water</b>, every
(1 row)
--- 610,618 ----
', to_tsquery('english', 'paint&water'));
ts_headline
-----------------------------------------
! <b>painted</b> Ocean.
! <b>Water</b>, <b>water</b>, every where
! And all the boards did shrink;
<b>Water</b>, <b>water</b>, every
(1 row)
***************
*** 629,637 ****
', to_tsquery('english', 'breath&motion&water'));
ts_headline
----------------------------------
! <b>breath</b> nor <b>motion</b>,+
! As idle as a painted Ship +
! Upon a painted Ocean. +
<b>Water</b>, <b>water</b>
(1 row)
--- 629,637 ----
', to_tsquery('english', 'breath&motion&water'));
ts_headline
----------------------------------
! <b>breath</b> nor <b>motion</b>,
! As idle as a painted Ship
! Upon a painted Ocean.
<b>Water</b>, <b>water</b>
(1 row)
***************
*** 648,656 ****
', to_tsquery('english', 'ocean'));
ts_headline
----------------------------------
! <b>Ocean</b>. +
! Water, water, every where +
! And all the boards did shrink;+
Water, water, every where
(1 row)
--- 648,656 ----
', to_tsquery('english', 'ocean'));
ts_headline
----------------------------------
! <b>Ocean</b>.
! 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> +
! <!-- some comment --> +
! <body> +
! <b>Sea</b> view wow <u><b>foo</b> bar</u> <i>qq</i> +
! <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>
! <!-- some comment -->
! <body>
! <b>Sea</b> view wow <u><b>foo</b> bar</u> <i>qq</i>
! <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 <b>Ocean</b>. +
! 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 <b>Ocean</b>.
! 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 <b>stuck</b>, 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. <b>Coleridge</b>
(1 row)
--- 721,733 ----
', to_tsquery('english', 'Coleridge & stuck'), 'MaxFragments=2');
ts_headline
----------------------------------------------
! after day, day after day,
! We <b>stuck</b>, 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. <b>Coleridge</b>
(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 <b>stuck</b>, 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. <b>Coleridge</b>
(1 row)
--- 765,777 ----
', to_tsquery('english', 'Coleridge & stuck'), 'MaxFragments=2,FragmentDelimiter=***');
ts_headline
--------------------------------------------
! after day, day after day,
! We <b>stuck</b>, 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. <b>Coleridge</b>
(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>
! <b>one</b>
! </row>
!
! <row>
! <a>2</a>
! <b>two</b>
! </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>
! <b>one</b>
! </row>
!
! <row>
! <a>2</a>
! <b>two</b>
! </row>
!
! <row>
! <a>-1</a>
! <b xsi:nil="true"/>
! </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>
! <b>one</b>
! </test1>
!
! <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
!
! <a>2</a>
! <b>two</b>
! </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>
! <b>one</b>
! </test1>
!
! <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
!
! <a>2</a>
! <b>two</b>
! </test1>
!
! <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
!
! <a>-1</a>
! <b xsi:nil="true"/>
! </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>
! <u>999</u>
! <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>
! <b>one</b>
! </row>
!
! <row>
! <a>2</a>
! <b>two</b>
! </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>
! <b>one</b>
! </row>
!
! <row>
! <a>2</a>
! <b>two</b>
! </row>
!
! <row>
! <a>-1</a>
! <b xsi:nil="true"/>
! </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>
! <b>one</b>
! </test1>
!
! <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
!
! <a>2</a>
! <b>two</b>
! </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>
! <b>one</b>
! </test1>
!
! <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="foo">
!
! <a>2</a>
! <b>two</b>
! </test1>
!
! <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="foo">
!
! <a>-1</a>
! <b xsi:nil="true"/>
! </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>
! <b>one</b>
! </row>
!
! <row>
! <a>2</a>
! <b>two</b>
! </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>
! <b>one</b>
! </row>
!
! <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
!
! <a>2</a>
! <b>two</b>
! </row>
!
! <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
!
! <a>-1</a>
! <b xsi:nil="true"/>
! </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>
! <b>one</b>
! </row>
!
! <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
!
! <a>2</a>
! <b>two</b>
! </row>
!
!
! (1 row)
!
MOVE FIRST IN xc;
SELECT cursor_to_xml('xc'::refcursor, 5, true, false, '');
! cursor_to_xml
! ---------------
! <row>
! <a>1</a>
! <b>one</b>
! </row>
!
! <row>
! <a>2</a>
! <b>two</b>
! </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>
! <b>one</b>
! </test1>
!
! <test1>
!
! <a>2</a>
! <b>two</b>
! </test1>
!
! <test1>
!
! <a>-1</a>
! </test1>
!
!
! <test2>
!
! <z>55</z>
! <y>abc</y>
! <x>def </x>
! <w>98.60</w>
! <v>2</v>
! <u>999</u>
! <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>
! <b>one</b>
! </row>
!
! <row>
! <a>2</a>
! <b>two</b>
! </row>
!
! <row>
! <a>-1</a>
! <b xsi:nil="true"/>
! </row>
!
! </test1>
!
! <test2>
!
! <row>
! <z>55</z>
! <y>abc</y>
! <x>def </x>
! <w>98.60</w>
! <v>2</v>
! <u>999</u>
! <t>0</t>
! <s>21:07:00</s>
! <r>2009-06-08T21:07:30</r>
! <q>2009-06-08</q>
! <p xsi:nil="true"/>
! <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>
! <b>one</b>
! </test1>
!
! <test1>
!
! <a>2</a>
! <b>two</b>
! </test1>
!
! <test1>
!
! <a>-1</a>
! <b xsi:nil="true"/>
! </test1>
!
!
! <test2>
!
! <z>55</z>
! <y>abc</y>
! <x>def </x>
! <w>98.60</w>
! <v>2</v>
! <u>999</u>
! <t>0</t>
! <s>21:07:00</s>
! <r>2009-06-08T21:07:30</r>
! <q>2009-06-08</q>
! <p xsi:nil="true"/>
! <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
On 10/28/2014 02:21 AM, Romu Hu wrote: > Hi, > > I'm new to postgresql. I ran the regression tests > <http://www.postgresql.org/docs/9.2/static/regress-run.html> 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. > Did you run this against the temporary installation as in section 30.1.1. Running the Tests Against a Temporary Installation or 30.1.2. Running the Tests Against an Existing Installation? In particular if running against an existing installation, is it possible you ran against a different version of Postgres then 9.2.8? This could happen if you have the env variables PGHOST and PGPORT set to another Postgres instance. > > > Thanks > Romu -- Adrian Klaver adrian.klaver@aklaver.com
On 2014/10/29 3:58, Adrian Klaver wrote: > On 10/28/2014 02:21 AM, Romu Hu wrote: >> Hi, >> >> I'm new to postgresql. I ran the regression tests >> <http://www.postgresql.org/docs/9.2/static/regress-run.html> 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. >> > > Did you run this against the temporary installation as in section > > 30.1.1. Running the Tests Against a Temporary Installation > > or > > 30.1.2. Running the Tests Against an Existing Installation? > > In particular if running against an existing installation, is it > possible you ran against a different version of Postgres then 9.2.8? > > This could happen if you have the env variables PGHOST and PGPORT set > to another Postgres instance. I ran the test against an existing installation (redhat enterprise linux software collection postgresql92). The postgres server and the tests are from the same source package. Thanks Romu
On Wed, Oct 29, 2014 at 10:44 AM, Romu Hu <huruomu@gmail.com> wrote: > I ran the test against an existing installation (redhat enterprise linux > software collection postgresql92). The postgres server and the tests are > from the same source package. Well, your diffs are telling us the contrary. The additional columns of \d+ have been added by this commit which is a new feature of Postgres 9.4, introduced by this commit: commit: 4168c00a5d9c0c0c17cdfc902587b6d22ea1720f author: Bruce Momjian <bruce@momjian.us> date: Tue, 15 Apr 2014 13:28:54 -0400 psql: conditionally display oids and replication identity In psql \d+, display oids only when they exist, and display replication identity only when it is non-default. Also document the defaults for replication identity for system and non-system tables. Update regression output. So you may be indeed running the tests on a 9.2 server, but what is sure is that you are comparing the results with the regression output of a 9.4 server. Regards, -- Michael
Michael Paquier <michael.paquier@gmail.com> writes:
> On Wed, Oct 29, 2014 at 10:44 AM, Romu Hu <huruomu@gmail.com> wrote:
>> I ran the test against an existing installation (redhat enterprise linux
>> software collection postgresql92). The postgres server and the tests are
>> from the same source package.
> Well, your diffs are telling us the contrary.
Yeah, this is definitely some kind of version skew problem.
> So you may be indeed running the tests on a 9.2 server, but what is
> sure is that you are comparing the results with the regression output
> of a 9.4 server.
No, that's not quite right I think. I can't find anything in a quick
look that is clearly different server behavior. There are a bunch of
differences in output of \d commands, and a bunch of different formatting
of query results containing newlines, but both of those things are on
psql's head not the server's. The psql being used is clearly older than
9.0, which is where the display of newlines changed. I'd bet on it being
the 8.4.something version shipped by Red Hat with their regular RHEL6
postgresql package.
I managed to leave Red Hat before they shipped any of those "software
collections" packages, but when I was there I was pretty unimpressed
with that packaging technology. You have to use the packages just so
or things fall apart, because for example their programs aren't in the
system default PATH. I think something like that happened here:
somehow or other the test process is invoking /usr/bin/psql and not
the psql included in the software-collections PG package.
Take a close re-read of the documentation for the software-collections
PG package and see if you missed a setup setup (scenable or whatever it
was called), or maybe they have a special recipe for running the
regression tests. If you can't find anything, file a bug with Red Hat
(not us), to the effect that their regression test packaging is either
broken or underdocumented.
regards, tom lane
On 2014/10/29 11:23, Tom Lane wrote: > Michael Paquier <michael.paquier@gmail.com> writes: >> On Wed, Oct 29, 2014 at 10:44 AM, Romu Hu <huruomu@gmail.com> wrote: >>> I ran the test against an existing installation (redhat enterprise linux >>> software collection postgresql92). The postgres server and the tests are >>> from the same source package. >> Well, your diffs are telling us the contrary. > Yeah, this is definitely some kind of version skew problem. > >> So you may be indeed running the tests on a 9.2 server, but what is >> sure is that you are comparing the results with the regression output >> of a 9.4 server. > No, that's not quite right I think. I can't find anything in a quick > look that is clearly different server behavior. There are a bunch of > differences in output of \d commands, and a bunch of different formatting > of query results containing newlines, but both of those things are on > psql's head not the server's. The psql being used is clearly older than > 9.0, which is where the display of newlines changed. I'd bet on it being > the 8.4.something version shipped by Red Hat with their regular RHEL6 > postgresql package. You are right, the test was using /usr/bin/psql from RHEL6 postgresql-8.4.20 package, hence the diffs. I removed postgresql-8.4.20 and reran the regression tests from the postgresql92 software collection, it failed the run because it couldn't find /usr/bin/psql, I linked /opt/rh/postgresql92/root/usr/bin/psql to /usr/bin/psql and all regression passed. Thanks Romu > I managed to leave Red Hat before they shipped any of those "software > collections" packages, but when I was there I was pretty unimpressed > with that packaging technology. You have to use the packages just so > or things fall apart, because for example their programs aren't in the > system default PATH. I think something like that happened here: > somehow or other the test process is invoking /usr/bin/psql and not > the psql included in the software-collections PG package. > > Take a close re-read of the documentation for the software-collections > PG package and see if you missed a setup setup (scenable or whatever it > was called), or maybe they have a special recipe for running the > regression tests. If you can't find anything, file a bug with Red Hat > (not us), to the effect that their regression test packaging is either > broken or underdocumented. > > regards, tom lane