create schema copy_support_transform;
set current_schema = copy_support_transform;

----As a reference, create a basic table and export its data to copy_support_transform.data.
----create table basic(mes1 text, mes2 float8, mes3 timestamp with time zone, mes4 INTEGER);
----copy basic to '@abs_srcdir@/data/copy_support_transform.data';

create table test(mes int, mes1 text, mes2 float8, mes3 timestamp with time zone, mes4 INTEGER);
create table test1(mes int, mes1 text, mes2 float8, mes3 timestamp with time zone, mes4 SMALLINT);

copy test from '@abs_srcdir@/data/copy_support_transform.data' transform (mes1 text AS mes1 || mes2, mes2 float8 AS mes2 + 1, mes3 timestamp with time zone AS date_trunc('year', mes3));
select * from test order by mes1;
truncate table test;

copy test from '@abs_srcdir@/data/copy_support_transform.data' transform (mes1 AS mes1 || mes2, mes2 AS mes2 + 1, mes3 AS date_trunc('year', mes3));	
select * from test order by mes1;
truncate table test;

copy test from '@abs_srcdir@/data/copy_support_transform.data' transform (mes1 text, mes2 float8, mes3 timestamp with time zone);
select * from test order by mes1;
truncate table test;

copy test from '@abs_srcdir@/data/copy_support_transform.data' transform (mes1, mes2, mes3);
select * from test order by mes1;
truncate table test;

copy test from '@abs_srcdir@/data/copy_support_transform.data' transform (mes1 char(3), mes2 int, mes3 date);	
copy test from '@abs_srcdir@/data/copy_support_transform.data' transform (mes1 text AS mes1 || mes2, mes2 float8 AS mes2 + 1E400, mes3 timestamp with time zone AS date_trunc('year', mes3));
copy test from '@abs_srcdir@/data/copy_support_transform.data' transform (mes5 text AS mes1 || mes2, mes2 float8 AS mes2 + 1, mes3 timestamp with time zone AS date_trunc('year', mes3));
copy test from '@abs_srcdir@/data/copy_support_transform.data' transform (mes1 text AS mes5 || mes2, mes2 float8 AS mes2 + 1, mes3 timestamp with time zone AS date_trunc('year', mes3));

copy test from '@abs_srcdir@/data/copy_support_transform.data' transform (mes3 timestamp with time zone AS date_trunc('year', mes3), mes1 text AS mes1 || mes2, mes2 float8 AS mes2 + 1);
select * from test order by mes1;
truncate table test;

copy test from '@abs_srcdir@/data/copy_support_transform.data' transform (mes1 text AS mes1 || mes2);
select * from test order by mes1;
truncate table test;

copy test1(mes, mes1, mes2, mes3, mes4) from '@abs_srcdir@/data/copy_support_transform.data' transform (mes4 INTEGER as mes4 % 32767);
select * from test1 order by mes1;
truncate table test1;

-- called in procedure.
create or replace procedure copy_in_procedure()
AS
    BEGIN
        copy test from '@abs_srcdir@/data/copy_support_transform.data' transform (mes1 text AS mes1 || mes2, mes2 float8 AS mes2 + 1, mes3 timestamp with time zone AS date_trunc('year', mes3));
    END;
/
select copy_in_procedure();
select * from test order by mes1;
drop function copy_in_procedure;

drop table test;
drop table test1;

create table copy_transform_explicit_cast (
 c_id int,
 c_bigint int,
 c_varchar varchar(30),
 c_data date
 )
PARTITION BY RANGE(C_BIGINT)
(
PARTITION P_20000_BEFORE VALUES LESS THAN (20000),
PARTITION P_30000 VALUES LESS THAN (30000),
PARTITION P_30000_AFTER VALUES LESS THAN (MAXVALUE)
);

-- expr type will explicit cast to column type
copy copy_transform_explicit_cast from '@abs_srcdir@/data/copy_transform_explicit_cast.data' TRANSFORM (c_bigint bigint AS c_bigint * 2) delimiter ',';
select * from copy_transform_explicit_cast order by c_bigint;
-- There is no way to explicit cast between date and int4
copy copy_transform_explicit_cast from '@abs_srcdir@/data/copy_transform_explicit_cast.data' TRANSFORM (c_bigint bigint AS c_bigint::text::date) delimiter ',';

drop table copy_transform_explicit_cast;
CREATE TABLE float_type_t2
(
FT_COL INTEGER,
FT_COL1 INTEGER,
FT_COL2 FLOAT4,
FT_COL3 FLOAT8,
FT_COL4 FLOAT(3),
FT_COL5 BINARY_DOUBLE,
FT_COL6 DECIMAL(10,4),
FT_COL7 INTEGER(6,3)
);
INSERT INTO float_type_t2 VALUES(1,10,10.365456,123456.1234,10.3214, 321.321, 123.123654, 123.123654);

SELECT * FROM float_type_t2;
copy float_type_t2 to '@abs_srcdir@/data/copy_support_transform_dts.data';

copy float_type_t2 from '@abs_srcdir@/data/copy_support_transform_dts.data' TRANSFORM (FT_COL1 varchar as FT_COL1);
SELECT * FROM float_type_t2;
drop table float_type_t2;

-- There will be a success on Centralized, or a failure on Distributed.
create table copy_transform_first_col(mes int, mes1 text, mes2 float8, mes3 timestamp with time zone, mes4 INTEGER);
copy copy_transform_first_col from '@abs_srcdir@/data/copy_support_transform.data' transform (mes int AS mes + 1);
drop table copy_transform_first_col;

drop schema copy_support_transform;