Need guidance on regression.diffs - Mailing list pgsql-general
| From | Romu Hu |
|---|---|
| Subject | Need guidance on regression.diffs |
| Date | |
| Msg-id | 544F602D.5070207@gmail.com Whole thread Raw |
| Responses |
Re: Need guidance on regression.diffs
|
| List | pgsql-general |
Hi,
I'm new to postgresql. I ran the regression tests on my postgresql 9.2.8, and 17 of 131 tests failed. Below is the regression.diffs (sorry for pasting such a long file), I have no clue whether these failures can be safely ignored. Any guidance would be appreciated.
*** /tmp/regress/expected/copyselect.out 2014-03-18 03:36:46.000000000
--- /tmp/regress/results/copyselect.out 2014-10-28 17:14:03.581781440
***************
*** 111,116 ****
--- 111,118 ----
\copy v_test1 to stdout
ERROR: cannot copy from view "v_test1"
HINT: Try the COPY (SELECT ...) TO variant.
+ \copy: ERROR: cannot copy from view "v_test1"
+ HINT: Try the COPY (SELECT ...) TO variant.
--
-- Test \copy (select ...)
--
***************
*** 125,153 ****
-- psql handling of COPY in multi-command strings
copy (select 1) to stdout\; select 1/0; -- row, then error
1
- ERROR: division by zero
select 1/0\; copy (select 1) to stdout; -- error only
ERROR: division by zero
copy (select 1) to stdout\; copy (select 2) to stdout\; select 0\; select 3; -- 1 2 3
1
- 2
- ?column?
- ----------
- 3
- (1 row)
-
create table test3 (c int);
select 0\; copy test3 from stdin\; copy test3 from stdin\; select 1; -- 1
! ?column?
! ----------
! 1
! (1 row)
!
select * from test3;
! c
! ---
! 1
! 2
! (2 rows)
!
drop table test3;
--- 127,143 ----
-- psql handling of COPY in multi-command strings
copy (select 1) to stdout\; select 1/0; -- row, then error
1
select 1/0\; copy (select 1) to stdout; -- error only
ERROR: division by zero
copy (select 1) to stdout\; copy (select 2) to stdout\; select 0\; select 3; -- 1 2 3
1
create table test3 (c int);
select 0\; copy test3 from stdin\; copy test3 from stdin\; select 1; -- 1
! 2
! \.
! invalid command \.
select * from test3;
! ERROR: syntax error at or near "2"
! LINE 1: 2
! ^
drop table test3;
======================================================================
*** /tmp/regress/expected/triggers.out 2014-03-18 03:36:46.000000000
--- /tmp/regress/results/triggers.out 2014-10-28 17:14:05.237781139
***************
*** 1114,1129 ****
--------+---------+-----------
a | integer |
b | integer |
! Triggers:
! after_del_stmt_trig AFTER DELETE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_del_stmt')
! after_ins_stmt_trig AFTER INSERT ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_ins_stmt')
! after_upd_stmt_trig AFTER UPDATE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_upd_stmt')
! before_del_stmt_trig BEFORE DELETE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_del_stmt')
! before_ins_stmt_trig BEFORE INSERT ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt')
! before_upd_stmt_trig BEFORE UPDATE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_upd_stmt')
! instead_of_delete_trig INSTEAD OF DELETE ON main_view FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del')
! instead_of_insert_trig INSTEAD OF INSERT ON main_view FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_ins')
! instead_of_update_trig INSTEAD OF UPDATE ON main_view FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd')
-- Test dropping view triggers
DROP TRIGGER instead_of_insert_trig ON main_view;
--- 1114,1122 ----
--------+---------+-----------
a | integer |
b | integer |
! View definition:
! SELECT main_table.a, main_table.b
! FROM main_table;
-- Test dropping view triggers
DROP TRIGGER instead_of_insert_trig ON main_view;
***************
*** 1137,1150 ****
View definition:
SELECT main_table.a, main_table.b
FROM main_table;
- Triggers:
- after_del_stmt_trig AFTER DELETE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_del_stmt')
- after_ins_stmt_trig AFTER INSERT ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_ins_stmt')
- after_upd_stmt_trig AFTER UPDATE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_upd_stmt')
- before_del_stmt_trig BEFORE DELETE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_del_stmt')
- before_ins_stmt_trig BEFORE INSERT ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt')
- before_upd_stmt_trig BEFORE UPDATE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_upd_stmt')
- instead_of_update_trig INSTEAD OF UPDATE ON main_view FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd')
DROP VIEW main_view;
--
--- 1130,1135 ----
======================================================================
*** /tmp/regress/expected/inherit.out 2014-03-18 03:36:46.000000000
--- /tmp/regress/results/inherit.out 2014-10-28 17:14:05.560781080
***************
*** 635,641 ****
Check constraints:
"p1chk" CHECK (ff1 > 0) NO INHERIT
"p2chk" CHECK (ff1 > 10)
- Number of child tables: 1 (Use \d+ to list them.)
\d c1
Table "public.c1"
--- 635,640 ----
***************
*** 905,917 ****
ERROR: cannot rename inherited column "aa"
ALTER TABLE inhts RENAME d TO dd;
\d+ inhts
! Table "public.inhts"
! Column | Type | Modifiers | Storage | Stats target | Description
! --------+---------+-----------+---------+--------------+-------------
! aa | integer | | plain | |
! b | integer | | plain | |
! c | integer | | plain | |
! dd | integer | | plain | |
Inherits: inht1,
inhs1
Has OIDs: no
--- 904,916 ----
ERROR: cannot rename inherited column "aa"
ALTER TABLE inhts RENAME d TO dd;
\d+ inhts
! Table "public.inhts"
! Column | Type | Modifiers | Storage | Description
! --------+---------+-----------+---------+-------------
! aa | integer | | plain |
! b | integer | | plain |
! c | integer | | plain |
! dd | integer | | plain |
Inherits: inht1,
inhs1
Has OIDs: no
***************
*** 925,938 ****
NOTICE: merging multiple inherited definitions of column "b"
ALTER TABLE inht1 RENAME aa TO aaa;
\d+ inht4
! Table "public.inht4"
! Column | Type | Modifiers | Storage | Stats target | Description
! --------+---------+-----------+---------+--------------+-------------
! aaa | integer | | plain | |
! b | integer | | plain | |
! x | integer | | plain | |
! y | integer | | plain | |
! z | integer | | plain | |
Inherits: inht2,
inht3
Has OIDs: no
--- 924,937 ----
NOTICE: merging multiple inherited definitions of column "b"
ALTER TABLE inht1 RENAME aa TO aaa;
\d+ inht4
! Table "public.inht4"
! Column | Type | Modifiers | Storage | Description
! --------+---------+-----------+---------+-------------
! aaa | integer | | plain |
! b | integer | | plain |
! x | integer | | plain |
! y | integer | | plain |
! z | integer | | plain |
Inherits: inht2,
inht3
Has OIDs: no
***************
*** 943,956 ****
ALTER TABLE inht1 RENAME b TO bb; -- to be failed
ERROR: cannot rename inherited column "b"
\d+ inhts
! Table "public.inhts"
! Column | Type | Modifiers | Storage | Stats target | Description
! --------+---------+-----------+---------+--------------+-------------
! aaaa | integer | | plain | |
! b | integer | | plain | |
! x | integer | | plain | |
! c | integer | | plain | |
! d | integer | | plain | |
Inherits: inht2,
inhs1
Has OIDs: no
--- 942,955 ----
ALTER TABLE inht1 RENAME b TO bb; -- to be failed
ERROR: cannot rename inherited column "b"
\d+ inhts
! Table "public.inhts"
! Column | Type | Modifiers | Storage | Description
! --------+---------+-----------+---------+-------------
! aaaa | integer | | plain |
! b | integer | | plain |
! x | integer | | plain |
! c | integer | | plain |
! d | integer | | plain |
Inherits: inht2,
inhs1
Has OIDs: no
***************
*** 992,1026 ****
NOTICE: CREATE TABLE / UNIQUE will create implicit index "test_constraints_val1_val2_key" for table "test_constraints"
CREATE TABLE test_constraints_inh () INHERITS (test_constraints);
\d+ test_constraints
! Table "public.test_constraints"
! Column | Type | Modifiers | Storage | Stats target | Description
! --------+-------------------+-----------+----------+--------------+-------------
! id | integer | | plain | |
! val1 | character varying | | extended | |
! val2 | integer | | plain | |
Indexes:
! "test_constraints_val1_val2_key" UNIQUE CONSTRAINT, btree (val1, val2)
! Child tables: test_constraints_inh
Has OIDs: no
ALTER TABLE ONLY test_constraints DROP CONSTRAINT test_constraints_val1_val2_key;
\d+ test_constraints
! Table "public.test_constraints"
! Column | Type | Modifiers | Storage | Stats target | Description
! --------+-------------------+-----------+----------+--------------+-------------
! id | integer | | plain | |
! val1 | character varying | | extended | |
! val2 | integer | | plain | |
! Child tables: test_constraints_inh
Has OIDs: no
\d+ test_constraints_inh
! Table "public.test_constraints_inh"
! Column | Type | Modifiers | Storage | Stats target | Description
! --------+-------------------+-----------+----------+--------------+-------------
! id | integer | | plain | |
! val1 | character varying | | extended | |
! val2 | integer | | plain | |
Inherits: test_constraints
Has OIDs: no
--- 991,1023 ----
NOTICE: CREATE TABLE / UNIQUE will create implicit index "test_constraints_val1_val2_key" for table "test_constraints"
CREATE TABLE test_constraints_inh () INHERITS (test_constraints);
\d+ test_constraints
! Table "public.test_constraints"
! Column | Type | Modifiers | Storage | Description
! --------+-------------------+-----------+----------+-------------
! id | integer | | plain |
! val1 | character varying | | extended |
! val2 | integer | | plain |
Indexes:
! "test_constraints_val1_val2_key" UNIQUE, btree (val1, val2)
Has OIDs: no
ALTER TABLE ONLY test_constraints DROP CONSTRAINT test_constraints_val1_val2_key;
\d+ test_constraints
! Table "public.test_constraints"
! Column | Type | Modifiers | Storage | Description
! --------+-------------------+-----------+----------+-------------
! id | integer | | plain |
! val1 | character varying | | extended |
! val2 | integer | | plain |
Has OIDs: no
\d+ test_constraints_inh
! Table "public.test_constraints_inh"
! Column | Type | Modifiers | Storage | Description
! --------+-------------------+-----------+----------+-------------
! id | integer | | plain |
! val1 | character varying | | extended |
! val2 | integer | | plain |
Inherits: test_constraints
Has OIDs: no
***************
*** 1033,1061 ****
NOTICE: CREATE TABLE / EXCLUDE will create implicit index "test_ex_constraints_c_excl" for table "test_ex_constraints"
CREATE TABLE test_ex_constraints_inh () INHERITS (test_ex_constraints);
\d+ test_ex_constraints
! Table "public.test_ex_constraints"
! Column | Type | Modifiers | Storage | Stats target | Description
! --------+--------+-----------+---------+--------------+-------------
! c | circle | | plain | |
Indexes:
! "test_ex_constraints_c_excl" EXCLUDE USING gist (c WITH &&)
! Child tables: test_ex_constraints_inh
Has OIDs: no
ALTER TABLE test_ex_constraints DROP CONSTRAINT test_ex_constraints_c_excl;
\d+ test_ex_constraints
! Table "public.test_ex_constraints"
! Column | Type | Modifiers | Storage | Stats target | Description
! --------+--------+-----------+---------+--------------+-------------
! c | circle | | plain | |
! Child tables: test_ex_constraints_inh
Has OIDs: no
\d+ test_ex_constraints_inh
! Table "public.test_ex_constraints_inh"
! Column | Type | Modifiers | Storage | Stats target | Description
! --------+--------+-----------+---------+--------------+-------------
! c | circle | | plain | |
Inherits: test_ex_constraints
Has OIDs: no
--- 1030,1056 ----
NOTICE: CREATE TABLE / EXCLUDE will create implicit index "test_ex_constraints_c_excl" for table "test_ex_constraints"
CREATE TABLE test_ex_constraints_inh () INHERITS (test_ex_constraints);
\d+ test_ex_constraints
! Table "public.test_ex_constraints"
! Column | Type | Modifiers | Storage | Description
! --------+--------+-----------+---------+-------------
! c | circle | | plain |
Indexes:
! "test_ex_constraints_c_excl" gist (c)
Has OIDs: no
ALTER TABLE test_ex_constraints DROP CONSTRAINT test_ex_constraints_c_excl;
\d+ test_ex_constraints
! Table "public.test_ex_constraints"
! Column | Type | Modifiers | Storage | Description
! --------+--------+-----------+---------+-------------
! c | circle | | plain |
Has OIDs: no
\d+ test_ex_constraints_inh
! Table "public.test_ex_constraints_inh"
! Column | Type | Modifiers | Storage | Description
! --------+--------+-----------+---------+-------------
! c | circle | | plain |
Inherits: test_ex_constraints
Has OIDs: no
***************
*** 1067,1076 ****
CREATE TABLE test_foreign_constraints(id1 int REFERENCES test_primary_constraints(id));
CREATE TABLE test_foreign_constraints_inh () INHERITS (test_foreign_constraints);
\d+ test_primary_constraints
! Table "public.test_primary_constraints"
! Column | Type | Modifiers | Storage | Stats target | Description
! --------+---------+-----------+---------+--------------+-------------
! id | integer | not null | plain | |
Indexes:
"test_primary_constraints_pkey" PRIMARY KEY, btree (id)
Referenced by:
--- 1062,1071 ----
CREATE TABLE test_foreign_constraints(id1 int REFERENCES test_primary_constraints(id));
CREATE TABLE test_foreign_constraints_inh () INHERITS (test_foreign_constraints);
\d+ test_primary_constraints
! Table "public.test_primary_constraints"
! Column | Type | Modifiers | Storage | Description
! --------+---------+-----------+---------+-------------
! id | integer | not null | plain |
Indexes:
"test_primary_constraints_pkey" PRIMARY KEY, btree (id)
Referenced by:
***************
*** 1078,1106 ****
Has OIDs: no
\d+ test_foreign_constraints
! Table "public.test_foreign_constraints"
! Column | Type | Modifiers | Storage | Stats target | Description
! --------+---------+-----------+---------+--------------+-------------
! id1 | integer | | plain | |
Foreign-key constraints:
"test_foreign_constraints_id1_fkey" FOREIGN KEY (id1) REFERENCES test_primary_constraints(id)
- Child tables: test_foreign_constraints_inh
Has OIDs: no
ALTER TABLE test_foreign_constraints DROP CONSTRAINT test_foreign_constraints_id1_fkey;
\d+ test_foreign_constraints
! Table "public.test_foreign_constraints"
! Column | Type | Modifiers | Storage | Stats target | Description
! --------+---------+-----------+---------+--------------+-------------
! id1 | integer | | plain | |
! Child tables: test_foreign_constraints_inh
Has OIDs: no
\d+ test_foreign_constraints_inh
! Table "public.test_foreign_constraints_inh"
! Column | Type | Modifiers | Storage | Stats target | Description
! --------+---------+-----------+---------+--------------+-------------
! id1 | integer | | plain | |
Inherits: test_foreign_constraints
Has OIDs: no
--- 1073,1099 ----
Has OIDs: no
\d+ test_foreign_constraints
! Table "public.test_foreign_constraints"
! Column | Type | Modifiers | Storage | Description
! --------+---------+-----------+---------+-------------
! id1 | integer | | plain |
Foreign-key constraints:
"test_foreign_constraints_id1_fkey" FOREIGN KEY (id1) REFERENCES test_primary_constraints(id)
Has OIDs: no
ALTER TABLE test_foreign_constraints DROP CONSTRAINT test_foreign_constraints_id1_fkey;
\d+ test_foreign_constraints
! Table "public.test_foreign_constraints"
! Column | Type | Modifiers | Storage | Description
! --------+---------+-----------+---------+-------------
! id1 | integer | | plain |
Has OIDs: no
\d+ test_foreign_constraints_inh
! Table "public.test_foreign_constraints_inh"
! Column | Type | Modifiers | Storage | Description
! --------+---------+-----------+---------+-------------
! id1 | integer | | plain |
Inherits: test_foreign_constraints
Has OIDs: no
======================================================================
*** /tmp/regress/expected/create_table_like.out 2014-03-18 03:36:46.000000000
--- /tmp/regress/results/create_table_like.out 2014-10-28 17:14:05.735781049
***************
*** 115,136 ****
ALTER TABLE ctlt4 ALTER COLUMN c SET STORAGE EXTERNAL;
CREATE TABLE ctlt12_storage (LIKE ctlt1 INCLUDING STORAGE, LIKE ctlt2 INCLUDING STORAGE);
\d+ ctlt12_storage
! Table "public.ctlt12_storage"
! Column | Type | Modifiers | Storage | Stats target | Description
! --------+------+-----------+----------+--------------+-------------
! a | text | not null | main | |
! b | text | | extended | |
! c | text | | external | |
Has OIDs: no
CREATE TABLE ctlt12_comments (LIKE ctlt1 INCLUDING COMMENTS, LIKE ctlt2 INCLUDING COMMENTS);
\d+ ctlt12_comments
! Table "public.ctlt12_comments"
! Column | Type | Modifiers | Storage | Stats target | Description
! --------+------+-----------+----------+--------------+-------------
! a | text | not null | extended | | A
! b | text | | extended | | B
! c | text | | extended | | C
Has OIDs: no
CREATE TABLE ctlt1_inh (LIKE ctlt1 INCLUDING CONSTRAINTS INCLUDING COMMENTS) INHERITS (ctlt1);
--- 115,136 ----
ALTER TABLE ctlt4 ALTER COLUMN c SET STORAGE EXTERNAL;
CREATE TABLE ctlt12_storage (LIKE ctlt1 INCLUDING STORAGE, LIKE ctlt2 INCLUDING STORAGE);
\d+ ctlt12_storage
! Table "public.ctlt12_storage"
! Column | Type | Modifiers | Storage | Description
! --------+------+-----------+----------+-------------
! a | text | not null | main |
! b | text | | extended |
! c | text | | external |
Has OIDs: no
CREATE TABLE ctlt12_comments (LIKE ctlt1 INCLUDING COMMENTS, LIKE ctlt2 INCLUDING COMMENTS);
\d+ ctlt12_comments
! Table "public.ctlt12_comments"
! Column | Type | Modifiers | Storage | Description
! --------+------+-----------+----------+-------------
! a | text | not null | extended | A
! b | text | | extended | B
! c | text | | extended | C
Has OIDs: no
CREATE TABLE ctlt1_inh (LIKE ctlt1 INCLUDING CONSTRAINTS INCLUDING COMMENTS) INHERITS (ctlt1);
***************
*** 138,148 ****
NOTICE: merging column "b" with inherited definition
NOTICE: merging constraint "ctlt1_a_check" with inherited definition
\d+ ctlt1_inh
! Table "public.ctlt1_inh"
! Column | Type | Modifiers | Storage | Stats target | Description
! --------+------+-----------+----------+--------------+-------------
! a | text | not null | main | | A
! b | text | | extended | | B
Check constraints:
"ctlt1_a_check" CHECK (length(a) > 2)
Inherits: ctlt1
--- 138,148 ----
NOTICE: merging column "b" with inherited definition
NOTICE: merging constraint "ctlt1_a_check" with inherited definition
\d+ ctlt1_inh
! Table "public.ctlt1_inh"
! Column | Type | Modifiers | Storage | Description
! --------+------+-----------+----------+-------------
! a | text | not null | main | A
! b | text | | extended | B
Check constraints:
"ctlt1_a_check" CHECK (length(a) > 2)
Inherits: ctlt1
***************
*** 157,168 ****
CREATE TABLE ctlt13_inh () INHERITS (ctlt1, ctlt3);
NOTICE: merging multiple inherited definitions of column "a"
\d+ ctlt13_inh
! Table "public.ctlt13_inh"
! Column | Type | Modifiers | Storage | Stats target | Description
! --------+------+-----------+----------+--------------+-------------
! a | text | not null | main | |
! b | text | | extended | |
! c | text | | external | |
Check constraints:
"ctlt1_a_check" CHECK (length(a) > 2)
"ctlt3_a_check" CHECK (length(a) < 5)
--- 157,168 ----
CREATE TABLE ctlt13_inh () INHERITS (ctlt1, ctlt3);
NOTICE: merging multiple inherited definitions of column "a"
\d+ ctlt13_inh
! Table "public.ctlt13_inh"
! Column | Type | Modifiers | Storage | Description
! --------+------+-----------+----------+-------------
! a | text | not null | main |
! b | text | | extended |
! c | text | | external |
Check constraints:
"ctlt1_a_check" CHECK (length(a) > 2)
"ctlt3_a_check" CHECK (length(a) < 5)
***************
*** 173,184 ****
CREATE TABLE ctlt13_like (LIKE ctlt3 INCLUDING CONSTRAINTS INCLUDING COMMENTS INCLUDING STORAGE) INHERITS (ctlt1);
NOTICE: merging column "a" with inherited definition
\d+ ctlt13_like
! Table "public.ctlt13_like"
! Column | Type | Modifiers | Storage | Stats target | Description
! --------+------+-----------+----------+--------------+-------------
! a | text | not null | main | | A3
! b | text | | extended | |
! c | text | | external | | C
Check constraints:
"ctlt1_a_check" CHECK (length(a) > 2)
"ctlt3_a_check" CHECK (length(a) < 5)
--- 173,184 ----
CREATE TABLE ctlt13_like (LIKE ctlt3 INCLUDING CONSTRAINTS INCLUDING COMMENTS INCLUDING STORAGE) INHERITS (ctlt1);
NOTICE: merging column "a" with inherited definition
\d+ ctlt13_like
! Table "public.ctlt13_like"
! Column | Type | Modifiers | Storage | Description
! --------+------+-----------+----------+-------------
! a | text | not null | main | A3
! b | text | | extended |
! c | text | | external | C
Check constraints:
"ctlt1_a_check" CHECK (length(a) > 2)
"ctlt3_a_check" CHECK (length(a) < 5)
***************
*** 194,204 ****
CREATE TABLE ctlt_all (LIKE ctlt1 INCLUDING ALL);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "ctlt_all_pkey" for table "ctlt_all"
\d+ ctlt_all
! Table "public.ctlt_all"
! Column | Type | Modifiers | Storage | Stats target | Description
! --------+------+-----------+----------+--------------+-------------
! a | text | not null | main | | A
! b | text | | extended | | B
Indexes:
"ctlt_all_pkey" PRIMARY KEY, btree (a)
"ctlt_all_b_idx" btree (b)
--- 194,204 ----
CREATE TABLE ctlt_all (LIKE ctlt1 INCLUDING ALL);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "ctlt_all_pkey" for table "ctlt_all"
\d+ ctlt_all
! Table "public.ctlt_all"
! Column | Type | Modifiers | Storage | Description
! --------+------+-----------+----------+-------------
! a | text | not null | main | A
! b | text | | extended | B
Indexes:
"ctlt_all_pkey" PRIMARY KEY, btree (a)
"ctlt_all_b_idx" btree (b)
======================================================================
*** /tmp/regress/expected/typed_table.out 2014-03-18 03:36:46.000000000
--- /tmp/regress/results/typed_table.out 2014-10-28 17:14:05.791781039
***************
*** 15,21 ****
--------+---------+-----------
id | integer |
name | text |
- Typed table of type: person_type
CREATE FUNCTION get_all_persons() RETURNS SETOF person_type
LANGUAGE SQL
--- 15,20 ----
***************
*** 55,62 ****
name | text |
Indexes:
"persons2_pkey" PRIMARY KEY, btree (id)
! "persons2_name_key" UNIQUE CONSTRAINT, btree (name)
! Typed table of type: person_type
CREATE TABLE persons3 OF person_type (
PRIMARY KEY (id),
--- 54,60 ----
name | text |
Indexes:
"persons2_pkey" PRIMARY KEY, btree (id)
! "persons2_name_key" UNIQUE, btree (name)
CREATE TABLE persons3 OF person_type (
PRIMARY KEY (id),
***************
*** 71,77 ****
name | text | default ''::text
Indexes:
"persons3_pkey" PRIMARY KEY, btree (id)
- Typed table of type: person_type
CREATE TABLE persons4 OF person_type (
name WITH OPTIONS NOT NULL,
--- 69,74 ----
======================================================================
*** /tmp/regress/expected/privileges.out 2014-03-18 03:36:46.000000000
--- /tmp/regress/results/privileges.out 2014-10-28 17:14:08.141780611
***************
*** 1381,1392 ****
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+---------------+-------+-----------------------------------+--------------------------
! public | dep_priv_test | table | regressuser1=arwdDxt/regressuser1+|
! | | | regressuser2=r*/regressuser1 +|
! | | | regressuser3=r*/regressuser1 +|
! | | | regressuser4=r*/regressuser2 +|
! | | | regressuser4=r*/regressuser3 +|
! | | | regressuser5=r/regressuser4 |
(1 row)
set session role regressuser2;
--- 1381,1392 ----
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+---------------+-------+-----------------------------------+--------------------------
! public | dep_priv_test | table | regressuser1=arwdDxt/regressuser1 |
! : regressuser2=r*/regressuser1
! : regressuser3=r*/regressuser1
! : regressuser4=r*/regressuser2
! : regressuser4=r*/regressuser3
! : regressuser5=r/regressuser4
(1 row)
set session role regressuser2;
***************
*** 1395,1405 ****
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+---------------+-------+-----------------------------------+--------------------------
! public | dep_priv_test | table | regressuser1=arwdDxt/regressuser1+|
! | | | regressuser2=r*/regressuser1 +|
! | | | regressuser3=r*/regressuser1 +|
! | | | regressuser4=r*/regressuser3 +|
! | | | regressuser5=r/regressuser4 |
(1 row)
set session role regressuser3;
--- 1395,1405 ----
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+---------------+-------+-----------------------------------+--------------------------
! public | dep_priv_test | table | regressuser1=arwdDxt/regressuser1 |
! : regressuser2=r*/regressuser1
! : regressuser3=r*/regressuser1
! : regressuser4=r*/regressuser3
! : regressuser5=r/regressuser4
(1 row)
set session role regressuser3;
***************
*** 1408,1416 ****
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+---------------+-------+-----------------------------------+--------------------------
! public | dep_priv_test | table | regressuser1=arwdDxt/regressuser1+|
! | | | regressuser2=r*/regressuser1 +|
! | | | regressuser3=r*/regressuser1 |
(1 row)
set session role regressuser1;
--- 1408,1416 ----
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+---------------+-------+-----------------------------------+--------------------------
! public | dep_priv_test | table | regressuser1=arwdDxt/regressuser1 |
! : regressuser2=r*/regressuser1
! : regressuser3=r*/regressuser1
(1 row)
set session role regressuser1;
======================================================================
*** /tmp/regress/expected/collate.out 2014-03-18 03:36:46.000000000
--- /tmp/regress/results/collate.out 2014-10-28 17:14:08.295780582
***************
*** 16,26 ****
b text COLLATE "C" NOT NULL
);
\d collate_test1
! Table "collate_tests.collate_test1"
! Column | Type | Modifiers
! --------+---------+--------------------
a | integer |
! b | text | collate C not null
CREATE TABLE collate_test_fail (
a int COLLATE "C",
--- 16,26 ----
b text COLLATE "C" NOT NULL
);
\d collate_test1
! Table "collate_tests.collate_test1"
! Column | Type | Modifiers
! --------+---------+-----------
a | integer |
! b | text | not null
CREATE TABLE collate_test_fail (
a int COLLATE "C",
***************
*** 34,43 ****
);
\d collate_test_like
Table "collate_tests.collate_test_like"
! Column | Type | Modifiers
! --------+---------+--------------------
a | integer |
! b | text | collate C not null
CREATE TABLE collate_test2 (
a int,
--- 34,43 ----
);
\d collate_test_like
Table "collate_tests.collate_test_like"
! Column | Type | Modifiers
! --------+---------+-----------
a | integer |
! b | text | not null
CREATE TABLE collate_test2 (
a int,
======================================================================
*** /tmp/regress/expected/rules.out 2014-03-18 03:36:46.000000000
--- /tmp/regress/results/rules.out 2014-10-28 17:14:08.831780485
***************
*** 1600,1625 ****
select pg_get_viewdef('shoe'::regclass,true) as pretty;
pretty
-------------------------------------------------------------
! SELECT sh.shoename, sh.sh_avail, sh.slcolor, sh.slminlen, +
! sh.slminlen * un.un_fact AS slminlen_cm, sh.slmaxlen, +
! sh.slmaxlen * un.un_fact AS slmaxlen_cm, sh.slunit +
! FROM shoe_data sh, unit un +
WHERE sh.slunit = un.un_name;
(1 row)
select pg_get_viewdef('shoe'::regclass,0) as prettier;
prettier
-----------------------------------------------
! SELECT sh.shoename, +
! sh.sh_avail, +
! sh.slcolor, +
! sh.slminlen, +
! sh.slminlen * un.un_fact AS slminlen_cm, +
! sh.slmaxlen, +
! sh.slmaxlen * un.un_fact AS slmaxlen_cm, +
! sh.slunit +
! FROM shoe_data sh, +
! unit un +
WHERE sh.slunit = un.un_name;
(1 row)
--- 1600,1625 ----
select pg_get_viewdef('shoe'::regclass,true) as pretty;
pretty
-------------------------------------------------------------
! SELECT sh.shoename, sh.sh_avail, sh.slcolor, sh.slminlen,
! sh.slminlen * un.un_fact AS slminlen_cm, sh.slmaxlen,
! sh.slmaxlen * un.un_fact AS slmaxlen_cm, sh.slunit
! FROM shoe_data sh, unit un
WHERE sh.slunit = un.un_name;
(1 row)
select pg_get_viewdef('shoe'::regclass,0) as prettier;
prettier
-----------------------------------------------
! SELECT sh.shoename,
! sh.sh_avail,
! sh.slcolor,
! sh.slminlen,
! sh.slminlen * un.un_fact AS slminlen_cm,
! sh.slmaxlen,
! sh.slmaxlen * un.un_fact AS slmaxlen_cm,
! sh.slunit
! FROM shoe_data sh,
! unit un
WHERE sh.slunit = un.un_name;
(1 row)
======================================================================
*** /tmp/regress/expected/dependency.out 2014-03-18 03:36:46.000000000
--- /tmp/regress/results/dependency.out 2014-10-28 17:14:09.707780325
***************
*** 71,79 ****
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+----------+-------+--------------------------------------------------+--------------------------
! public | deptest1 | table | regression_user0=arwdDxt/regression_user0 +|
! | | | regression_user1=a*r*w*d*D*x*t*/regression_user0+|
! | | | regression_user2=arwdDxt/regression_user1 |
(1 row)
DROP OWNED BY regression_user1;
--- 71,79 ----
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+----------+-------+--------------------------------------------------+--------------------------
! public | deptest1 | table | regression_user0=arwdDxt/regression_user0 |
! : regression_user1=a*r*w*d*D*x*t*/regression_user0
! : regression_user2=arwdDxt/regression_user1
(1 row)
DROP OWNED BY regression_user1;
======================================================================
*** /tmp/regress/expected/tsearch.out 2014-03-18 03:36:46.000000000
--- /tmp/regress/results/tsearch.out 2014-10-28 17:14:10.282780220
***************
*** 357,364 ****
3 | ewri2
12 |
13 | <a href="qwe<qwe>">
! 12 | +
! |
19 | /usr/local/fff
12 |
19 | /awdf/dwqe/4325
--- 357,364 ----
3 | ewri2
12 |
13 | <a href="qwe<qwe>">
! 12 |
! :
19 | /usr/local/fff
12 |
19 | /awdf/dwqe/4325
***************
*** 390,397 ****
20 | -4.2
12 | .
22 | 234
! 12 | +
! |
12 | <
1 | i
12 |
--- 390,397 ----
20 | -4.2
12 | .
22 | 234
! 12 |
! :
12 | <
1 | i
12 |
***************
*** 610,618 ****
', to_tsquery('english', 'paint&water'));
ts_headline
-----------------------------------------
! <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
pgsql-general by date: