--
-- Testing ALTER TABLE on cstore_fdw tables.
--
CREATE FOREIGN TABLE test_alter_table (a int, b int, c int) SERVER cstore_server;
WITH sample_data AS (VALUES
    (1, 2, 3),
    (4, 5, 6),
    (7, 8, 9)
)
INSERT INTO test_alter_table SELECT * FROM sample_data;
-- drop a column
ALTER FOREIGN TABLE test_alter_table DROP COLUMN a;
-- test analyze
ANALYZE test_alter_table;
-- verify select queries run as expected
SELECT * FROM test_alter_table;
 b | c 
---+---
 2 | 3
 5 | 6
 8 | 9
(3 rows)

SELECT a FROM test_alter_table;
ERROR:  column "a" does not exist
LINE 1: SELECT a FROM test_alter_table;
               ^
SELECT b FROM test_alter_table;
 b 
---
 2
 5
 8
(3 rows)

-- verify insert runs as expected
INSERT INTO test_alter_table (SELECT 3, 5, 8);
ERROR:  INSERT has more expressions than target columns
LINE 1: INSERT INTO test_alter_table (SELECT 3, 5, 8);
                                                   ^
INSERT INTO test_alter_table (SELECT 5, 8);
-- add a column with no defaults
ALTER FOREIGN TABLE test_alter_table ADD COLUMN d int;
SELECT * FROM test_alter_table;
 b | c | d 
---+---+---
 2 | 3 |  
 5 | 6 |  
 8 | 9 |  
 5 | 8 |  
(4 rows)

INSERT INTO test_alter_table (SELECT 3, 5, 8);
SELECT * FROM test_alter_table;
 b | c | d 
---+---+---
 2 | 3 |  
 5 | 6 |  
 8 | 9 |  
 5 | 8 |  
 3 | 5 | 8
(5 rows)

-- add a fixed-length column with default value
ALTER FOREIGN TABLE test_alter_table ADD COLUMN e int default 3;
SELECT * from test_alter_table;
 b | c | d | e 
---+---+---+---
 2 | 3 |   | 3
 5 | 6 |   | 3
 8 | 9 |   | 3
 5 | 8 |   | 3
 3 | 5 | 8 | 3
(5 rows)

INSERT INTO test_alter_table (SELECT 1, 2, 4, 8);
SELECT * from test_alter_table;
 b | c | d | e 
---+---+---+---
 2 | 3 |   | 3
 5 | 6 |   | 3
 8 | 9 |   | 3
 5 | 8 |   | 3
 3 | 5 | 8 | 3
 1 | 2 | 4 | 8
(6 rows)

-- add a variable-length column with default value
ALTER FOREIGN TABLE test_alter_table ADD COLUMN f text DEFAULT 'TEXT ME';
SELECT * from test_alter_table;
 b | c | d | e |    f    
---+---+---+---+---------
 2 | 3 |   | 3 | TEXT ME
 5 | 6 |   | 3 | TEXT ME
 8 | 9 |   | 3 | TEXT ME
 5 | 8 |   | 3 | TEXT ME
 3 | 5 | 8 | 3 | TEXT ME
 1 | 2 | 4 | 8 | TEXT ME
(6 rows)

INSERT INTO test_alter_table (SELECT 1, 2, 4, 8, 'ABCDEF');
SELECT * from test_alter_table;
 b | c | d | e |    f    
---+---+---+---+---------
 2 | 3 |   | 3 | TEXT ME
 5 | 6 |   | 3 | TEXT ME
 8 | 9 |   | 3 | TEXT ME
 5 | 8 |   | 3 | TEXT ME
 3 | 5 | 8 | 3 | TEXT ME
 1 | 2 | 4 | 8 | TEXT ME
 1 | 2 | 4 | 8 | ABCDEF
(7 rows)

-- drop couple of columns
ALTER FOREIGN TABLE test_alter_table DROP COLUMN c;
ALTER FOREIGN TABLE test_alter_table DROP COLUMN e;
ANALYZE test_alter_table;
SELECT * from test_alter_table;
 b | d |    f    
---+---+---------
 2 |   | TEXT ME
 5 |   | TEXT ME
 8 |   | TEXT ME
 5 |   | TEXT ME
 3 | 8 | TEXT ME
 1 | 4 | TEXT ME
 1 | 4 | ABCDEF
(7 rows)

SELECT count(*) from test_alter_table;
 count 
-------
     7
(1 row)

SELECT count(t.*) from test_alter_table t;
 count 
-------
     7
(1 row)

-- unsupported default values
ALTER FOREIGN TABLE test_alter_table ADD COLUMN g boolean DEFAULT isfinite(current_date);
ALTER FOREIGN TABLE test_alter_table ADD COLUMN h DATE DEFAULT current_date;
SELECT * FROM test_alter_table;
ERROR:  unsupported default value for column "g"
HINT:  Expression is either mutable or does not evaluate to constant value
ALTER FOREIGN TABLE test_alter_table ALTER COLUMN g DROP DEFAULT;
SELECT * FROM test_alter_table;
ERROR:  unsupported default value for column "h"
HINT:  Expression is either mutable or does not evaluate to constant value
ALTER FOREIGN TABLE test_alter_table ALTER COLUMN h DROP DEFAULT;
ANALYZE test_alter_table;
SELECT * FROM test_alter_table;
 b | d |    f    | g | h 
---+---+---------+---+---
 2 |   | TEXT ME |   | 
 5 |   | TEXT ME |   | 
 8 |   | TEXT ME |   | 
 5 |   | TEXT ME |   | 
 3 | 8 | TEXT ME |   | 
 1 | 4 | TEXT ME |   | 
 1 | 4 | ABCDEF  |   | 
(7 rows)

-- unsupported type change
ALTER FOREIGN TABLE test_alter_table ADD COLUMN i int;
ALTER FOREIGN TABLE test_alter_table ADD COLUMN j float;
ALTER FOREIGN TABLE test_alter_table ADD COLUMN k text;
-- this is valid type change
ALTER FOREIGN TABLE test_alter_table ALTER COLUMN i TYPE float;
-- this is not valid
ALTER FOREIGN TABLE test_alter_table ALTER COLUMN j TYPE int;
ERROR:  Column j cannot be cast automatically to type pg_catalog.int4
-- text / varchar conversion is valid both ways
ALTER FOREIGN TABLE test_alter_table ALTER COLUMN k TYPE varchar(20);
ALTER FOREIGN TABLE test_alter_table ALTER COLUMN k TYPE text;
DROP FOREIGN TABLE test_alter_table;