CREATE EXTENSION multicorn;
CREATE server multicorn_srv foreign data wrapper multicorn options (
    wrapper 'multicorn.testfdw.TestForeignDataWrapper'
);
CREATE user mapping for postgres server multicorn_srv options (usermapping 'test');
CREATE foreign table testmulticorn (
    test1 character varying(20),
    test2 character varying
) server multicorn_srv options (
    option1 'option1'
);
-- Test "normal" usage
select * from testmulticorn;
NOTICE:  [('option1', 'option1'), ('usermapping', 'test')]
NOTICE:  [('test1', 'character varying(20)'), ('test2', 'character varying')]
NOTICE:  []
NOTICE:  ['test1', 'test2']
   test1    |   test2    
------------+------------
 test1 1 0  | test2 2 0
 test1 3 1  | test2 1 1
 test1 2 2  | test2 3 2
 test1 1 3  | test2 2 3
 test1 3 4  | test2 1 4
 test1 2 5  | test2 3 5
 test1 1 6  | test2 2 6
 test1 3 7  | test2 1 7
 test1 2 8  | test2 3 8
 test1 1 9  | test2 2 9
 test1 3 10 | test2 1 10
 test1 2 11 | test2 3 11
 test1 1 12 | test2 2 12
 test1 3 13 | test2 1 13
 test1 2 14 | test2 3 14
 test1 1 15 | test2 2 15
 test1 3 16 | test2 1 16
 test1 2 17 | test2 3 17
 test1 1 18 | test2 2 18
 test1 3 19 | test2 1 19
(20 rows)

ALTER foreign table testmulticorn drop column test1;
select * from testmulticorn;
NOTICE:  [('option1', 'option1'), ('usermapping', 'test')]
NOTICE:  [('test2', 'character varying')]
NOTICE:  []
NOTICE:  ['test2']
   test2    
------------
 test2 1 0
 test2 2 1
 test2 3 2
 test2 1 3
 test2 2 4
 test2 3 5
 test2 1 6
 test2 2 7
 test2 3 8
 test2 1 9
 test2 2 10
 test2 3 11
 test2 1 12
 test2 2 13
 test2 3 14
 test2 1 15
 test2 2 16
 test2 3 17
 test2 1 18
 test2 2 19
(20 rows)

ALTER foreign table testmulticorn add column test1 varchar;
select * from testmulticorn;
NOTICE:  [('option1', 'option1'), ('usermapping', 'test')]
NOTICE:  [('test1', 'character varying'), ('test2', 'character varying')]
NOTICE:  []
NOTICE:  ['test1', 'test2']
   test2    |   test1    
------------+------------
 test2 1 0  | test1 2 0
 test2 3 1  | test1 1 1
 test2 2 2  | test1 3 2
 test2 1 3  | test1 2 3
 test2 3 4  | test1 1 4
 test2 2 5  | test1 3 5
 test2 1 6  | test1 2 6
 test2 3 7  | test1 1 7
 test2 2 8  | test1 3 8
 test2 1 9  | test1 2 9
 test2 3 10 | test1 1 10
 test2 2 11 | test1 3 11
 test2 1 12 | test1 2 12
 test2 3 13 | test1 1 13
 test2 2 14 | test1 3 14
 test2 1 15 | test1 2 15
 test2 3 16 | test1 1 16
 test2 2 17 | test1 3 17
 test2 1 18 | test1 2 18
 test2 3 19 | test1 1 19
(20 rows)

ALTER foreign table testmulticorn add column test3 varchar;
select * from testmulticorn;
NOTICE:  [('option1', 'option1'), ('usermapping', 'test')]
NOTICE:  [('test1', 'character varying'), ('test2', 'character varying'), ('test3', 'character varying')]
NOTICE:  []
NOTICE:  ['test1', 'test2', 'test3']
   test2    |   test1    |   test3    
------------+------------+------------
 test2 1 0  | test1 2 0  | test3 3 0
 test2 1 1  | test1 2 1  | test3 3 1
 test2 1 2  | test1 2 2  | test3 3 2
 test2 1 3  | test1 2 3  | test3 3 3
 test2 1 4  | test1 2 4  | test3 3 4
 test2 1 5  | test1 2 5  | test3 3 5
 test2 1 6  | test1 2 6  | test3 3 6
 test2 1 7  | test1 2 7  | test3 3 7
 test2 1 8  | test1 2 8  | test3 3 8
 test2 1 9  | test1 2 9  | test3 3 9
 test2 1 10 | test1 2 10 | test3 3 10
 test2 1 11 | test1 2 11 | test3 3 11
 test2 1 12 | test1 2 12 | test3 3 12
 test2 1 13 | test1 2 13 | test3 3 13
 test2 1 14 | test1 2 14 | test3 3 14
 test2 1 15 | test1 2 15 | test3 3 15
 test2 1 16 | test1 2 16 | test3 3 16
 test2 1 17 | test1 2 17 | test3 3 17
 test2 1 18 | test1 2 18 | test3 3 18
 test2 1 19 | test1 2 19 | test3 3 19
(20 rows)

ALTER foreign table testmulticorn options (SET option1 'option1_update');
select * from testmulticorn;
NOTICE:  [('option1', 'option1_update'), ('usermapping', 'test')]
NOTICE:  [('test1', 'character varying'), ('test2', 'character varying'), ('test3', 'character varying')]
NOTICE:  []
NOTICE:  ['test1', 'test2', 'test3']
   test2    |   test1    |   test3    
------------+------------+------------
 test2 1 0  | test1 2 0  | test3 3 0
 test2 1 1  | test1 2 1  | test3 3 1
 test2 1 2  | test1 2 2  | test3 3 2
 test2 1 3  | test1 2 3  | test3 3 3
 test2 1 4  | test1 2 4  | test3 3 4
 test2 1 5  | test1 2 5  | test3 3 5
 test2 1 6  | test1 2 6  | test3 3 6
 test2 1 7  | test1 2 7  | test3 3 7
 test2 1 8  | test1 2 8  | test3 3 8
 test2 1 9  | test1 2 9  | test3 3 9
 test2 1 10 | test1 2 10 | test3 3 10
 test2 1 11 | test1 2 11 | test3 3 11
 test2 1 12 | test1 2 12 | test3 3 12
 test2 1 13 | test1 2 13 | test3 3 13
 test2 1 14 | test1 2 14 | test3 3 14
 test2 1 15 | test1 2 15 | test3 3 15
 test2 1 16 | test1 2 16 | test3 3 16
 test2 1 17 | test1 2 17 | test3 3 17
 test2 1 18 | test1 2 18 | test3 3 18
 test2 1 19 | test1 2 19 | test3 3 19
(20 rows)

ALTER foreign table testmulticorn options (ADD option2 'option2');
select * from testmulticorn;
NOTICE:  [('option1', 'option1_update'), ('option2', 'option2'), ('usermapping', 'test')]
NOTICE:  [('test1', 'character varying'), ('test2', 'character varying'), ('test3', 'character varying')]
NOTICE:  []
NOTICE:  ['test1', 'test2', 'test3']
   test2    |   test1    |   test3    
------------+------------+------------
 test2 1 0  | test1 2 0  | test3 3 0
 test2 1 1  | test1 2 1  | test3 3 1
 test2 1 2  | test1 2 2  | test3 3 2
 test2 1 3  | test1 2 3  | test3 3 3
 test2 1 4  | test1 2 4  | test3 3 4
 test2 1 5  | test1 2 5  | test3 3 5
 test2 1 6  | test1 2 6  | test3 3 6
 test2 1 7  | test1 2 7  | test3 3 7
 test2 1 8  | test1 2 8  | test3 3 8
 test2 1 9  | test1 2 9  | test3 3 9
 test2 1 10 | test1 2 10 | test3 3 10
 test2 1 11 | test1 2 11 | test3 3 11
 test2 1 12 | test1 2 12 | test3 3 12
 test2 1 13 | test1 2 13 | test3 3 13
 test2 1 14 | test1 2 14 | test3 3 14
 test2 1 15 | test1 2 15 | test3 3 15
 test2 1 16 | test1 2 16 | test3 3 16
 test2 1 17 | test1 2 17 | test3 3 17
 test2 1 18 | test1 2 18 | test3 3 18
 test2 1 19 | test1 2 19 | test3 3 19
(20 rows)

ALTER foreign table testmulticorn options (DROP option2);
select * from testmulticorn;
NOTICE:  [('option1', 'option1_update'), ('usermapping', 'test')]
NOTICE:  [('test1', 'character varying'), ('test2', 'character varying'), ('test3', 'character varying')]
NOTICE:  []
NOTICE:  ['test1', 'test2', 'test3']
   test2    |   test1    |   test3    
------------+------------+------------
 test2 1 0  | test1 2 0  | test3 3 0
 test2 1 1  | test1 2 1  | test3 3 1
 test2 1 2  | test1 2 2  | test3 3 2
 test2 1 3  | test1 2 3  | test3 3 3
 test2 1 4  | test1 2 4  | test3 3 4
 test2 1 5  | test1 2 5  | test3 3 5
 test2 1 6  | test1 2 6  | test3 3 6
 test2 1 7  | test1 2 7  | test3 3 7
 test2 1 8  | test1 2 8  | test3 3 8
 test2 1 9  | test1 2 9  | test3 3 9
 test2 1 10 | test1 2 10 | test3 3 10
 test2 1 11 | test1 2 11 | test3 3 11
 test2 1 12 | test1 2 12 | test3 3 12
 test2 1 13 | test1 2 13 | test3 3 13
 test2 1 14 | test1 2 14 | test3 3 14
 test2 1 15 | test1 2 15 | test3 3 15
 test2 1 16 | test1 2 16 | test3 3 16
 test2 1 17 | test1 2 17 | test3 3 17
 test2 1 18 | test1 2 18 | test3 3 18
 test2 1 19 | test1 2 19 | test3 3 19
(20 rows)

-- Test dropping column when returning sequences (issue #15)
ALTER foreign table testmulticorn options (ADD test_type 'sequence');
select * from testmulticorn;
NOTICE:  [('option1', 'option1_update'), ('test_type', 'sequence'), ('usermapping', 'test')]
NOTICE:  [('test1', 'character varying'), ('test2', 'character varying'), ('test3', 'character varying')]
NOTICE:  []
NOTICE:  ['test1', 'test2', 'test3']
   test2    |   test1    |   test3    
------------+------------+------------
 test2 1 0  | test1 2 0  | test3 3 0
 test2 1 1  | test1 2 1  | test3 3 1
 test2 1 2  | test1 2 2  | test3 3 2
 test2 1 3  | test1 2 3  | test3 3 3
 test2 1 4  | test1 2 4  | test3 3 4
 test2 1 5  | test1 2 5  | test3 3 5
 test2 1 6  | test1 2 6  | test3 3 6
 test2 1 7  | test1 2 7  | test3 3 7
 test2 1 8  | test1 2 8  | test3 3 8
 test2 1 9  | test1 2 9  | test3 3 9
 test2 1 10 | test1 2 10 | test3 3 10
 test2 1 11 | test1 2 11 | test3 3 11
 test2 1 12 | test1 2 12 | test3 3 12
 test2 1 13 | test1 2 13 | test3 3 13
 test2 1 14 | test1 2 14 | test3 3 14
 test2 1 15 | test1 2 15 | test3 3 15
 test2 1 16 | test1 2 16 | test3 3 16
 test2 1 17 | test1 2 17 | test3 3 17
 test2 1 18 | test1 2 18 | test3 3 18
 test2 1 19 | test1 2 19 | test3 3 19
(20 rows)

ALTER foreign table testmulticorn drop test3;
select * from testmulticorn;
NOTICE:  [('option1', 'option1_update'), ('test_type', 'sequence'), ('usermapping', 'test')]
NOTICE:  [('test1', 'character varying'), ('test2', 'character varying')]
NOTICE:  []
NOTICE:  ['test1', 'test2']
   test2    |   test1    
------------+------------
 test2 1 0  | test1 2 0
 test2 3 1  | test1 1 1
 test2 2 2  | test1 3 2
 test2 1 3  | test1 2 3
 test2 3 4  | test1 1 4
 test2 2 5  | test1 3 5
 test2 1 6  | test1 2 6
 test2 3 7  | test1 1 7
 test2 2 8  | test1 3 8
 test2 1 9  | test1 2 9
 test2 3 10 | test1 1 10
 test2 2 11 | test1 3 11
 test2 1 12 | test1 2 12
 test2 3 13 | test1 1 13
 test2 2 14 | test1 3 14
 test2 1 15 | test1 2 15
 test2 3 16 | test1 1 16
 test2 2 17 | test1 3 17
 test2 1 18 | test1 2 18
 test2 3 19 | test1 1 19
(20 rows)

ALTER foreign table testmulticorn alter test1 type varchar(30);
select * from testmulticorn limit 1;
NOTICE:  [('option1', 'option1_update'), ('test_type', 'sequence'), ('usermapping', 'test')]
NOTICE:  [('test1', 'character varying(30)'), ('test2', 'character varying')]
NOTICE:  []
NOTICE:  ['test1', 'test2']
   test2   |   test1   
-----------+-----------
 test2 1 0 | test1 2 0
(1 row)

ALTER foreign table testmulticorn alter test1 type text;
select * from testmulticorn limit 1;
NOTICE:  [('option1', 'option1_update'), ('test_type', 'sequence'), ('usermapping', 'test')]
NOTICE:  [('test1', 'text'), ('test2', 'character varying')]
NOTICE:  []
NOTICE:  ['test1', 'test2']
   test2   |   test1   
-----------+-----------
 test2 1 0 | test1 2 0
(1 row)

ALTER foreign table testmulticorn rename test1 to testnew;
select * from testmulticorn limit 1;
NOTICE:  []
NOTICE:  ['test2', 'testnew']
   test2   |  testnew  
-----------+-----------
 test2 1 0 | test1 2 0
(1 row)

DROP USER MAPPING for postgres SERVER multicorn_srv;
DROP EXTENSION multicorn cascade;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to server multicorn_srv
drop cascades to foreign table testmulticorn