--
-- XC_COPY
--
-- COPY tests for a Postgres-XC cluster

create or replace function pgxc_nodetype() returns varchar as 
$$
declare
	pgxc_class_entries int;        
begin
	select into pgxc_class_entries count(*) from pgxc_class ;             
	if pgxc_class_entries > 0 then
		return ' Node type: C';
	else
		return ' Node type: D';
	end if;
end $$ language plpgsql;


create function deffunc() returns bytea as
$$
begin
	return E'\\023\\000\\\\''!|''';
end $$ language plpgsql;


create or replace function deffunc_bytea() returns bytea as 
$$
begin
	return E'\0123';
end $$ language plpgsql;

create or replace function deffunc_str() returns varchar as 
$$
begin
	return E'\\.\\000"\\''!|''\\N' || pgxc_nodetype();
end $$ language plpgsql;

create or replace function deffunc_str_i() returns varchar immutable as 
$$
begin
	return E'\\.\\000"\\''!|''' || pgxc_nodetype();
end $$ language plpgsql;

create or replace function deffunc_nullstring() returns varchar as 
$$
begin
	return E'\\N';
end $$ language plpgsql;

create table xccopydef(idseq int, id1 int,
                       def1 bytea default deffunc(),
					   def2 bytea default E'\\000'::bytea,
					   def3 varchar default deffunc_str(),
					   def4 varchar default deffunc_str_i(),
					   def5 varchar default deffunc_nullstring(),
					   def6 bytea default deffunc_bytea(),
					   id2 varchar,
					   def7 float default length(deffunc_str()) + 0.433);


copy xccopydef (id1, id2) from stdin (delimiter '|');
3 | \\\\x135
\N| abcd
\.

copy xccopydef (id1, id2) from stdin (format 'csv');
4 , \\\\x135
5 , abcd
\.


insert into xccopydef (id1, id2) values (NULL, NULL);
insert into xccopydef (id1) values (4567);

select * from xccopydef order by idseq, id1, id2;

copy xccopydef (id1, id2) to '@abs_builddir@/results/copydefout.data' (format 'binary');

truncate xccopydef;

copy xccopydef (id1, id2) from '@abs_builddir@/results/copydefout.data' (format 'binary');

select * from xccopydef order by idseq, id1, id2;

drop table xccopydef;
drop function pgxc_nodetype();
drop function deffunc();
drop function deffunc_bytea();
drop function deffunc_str();
drop function deffunc_str_i();
drop function deffunc_nullstring();

-- Tests related to COPY for a Postgres-XC cluster
-- Create a table not using the first node of cluster
CREATE TABLE xc_copy_1(a int, b int);
INSERT INTO xc_copy_1 VALUES (1,23),(34,5),(9,11);
-- Extract its data
COPY xc_copy_1 TO STDOUT;
DROP TABLE xc_copy_1;

-- Quoted table name
-- check for correct remote query generation
CREATE TABLE "Xc_copy_2" (a int, b int);
COPY "Xc_copy_2" FROM STDIN DELIMITER ',';
1,2
\.
COPY "Xc_copy_2" TO STDOUT;
DROP TABLE "Xc_copy_2";

-- Quoted column name
-- check for correct remote query generation
CREATE TABLE xc_copy_3(a int, "user" int);
COPY xc_copy_3 (a, "user") FROM STDIN (DELIMITER ',');
1,2
\.
COPY xc_copy_3 TO STDOUT;
DROP TABLE xc_copy_3;

-- Table with no locator data
CREATE TABLE xc_copy_4 (c1 int);
COPY (SELECT pclocatortype,pcattnum,pchashalgorithm,pchashbuckets FROM pgxc_class WHERE pgxc_class.pcrelid = 'xc_copy_4'::regclass) TO stdout;
DROP TABLE xc_copy_4;


-- ///////////////////////////
-- Test COPY TO and FROM from various types of tables
-- ///////////////////////////

-- create some tables
CREATE TABLE cpy_tbl_r_n12(a int, b int);
CREATE TABLE cpy_tbl_r_n2(a int, b int);

CREATE TABLE cpy_tbl_rr_n12(a int, b int);
CREATE TABLE cpy_tbl_rr_n2(a int, b int);

CREATE TABLE cpy_tbl_h_n12(a int, b int);
CREATE TABLE cpy_tbl_h_n2(a int, b int);

-- try copy from replicated table

copy cpy_tbl_r_n12(a,b) from stdin;
\N	11
\.

copy cpy_tbl_r_n2(a,b) from stdin;
\N	11
\.

-- try copy from round robin table
copy cpy_tbl_rr_n12(a,b) from stdin;
\N	11
\.

copy cpy_tbl_rr_n2(a,b) from stdin;
\N	11
\.

-- try copy from hash distributed table
copy cpy_tbl_h_n12(a,b) from stdin;
\N	11
\.

copy cpy_tbl_h_n2(a,b) from stdin;
\N	11
\.

-- confirm copy from

select * from cpy_tbl_r_n12;
select * from cpy_tbl_r_n2;

select * from cpy_tbl_rr_n12;
select * from cpy_tbl_rr_n2;

select * from cpy_tbl_h_n12;
select * from cpy_tbl_h_n2;


-- change some data


update cpy_tbl_r_n12 set b = 999 where a is null;
update cpy_tbl_r_n2 set b = 999 where a is null;

update cpy_tbl_rr_n12 set b = 999 where a is null;
update cpy_tbl_rr_n2 set b = 999 where a is null;

update cpy_tbl_h_n12 set b = 999 where a is null;
update cpy_tbl_h_n2 set b = 999 where a is null;

-- try copy to a replicated table

copy cpy_tbl_r_n12 (a,b) to stdout;
copy cpy_tbl_r_n2 (a,b) to stdout;

-- try copy to a round robin table
copy cpy_tbl_rr_n12 (a,b) to stdout;
copy cpy_tbl_rr_n2 (a,b) to stdout;

-- try copy to a hash distributed table
copy cpy_tbl_h_n12 (a,b) to stdout;
copy cpy_tbl_h_n2 (a,b) to stdout;


-- clean up

drop table if exists cpy_tbl_r_n12;
drop table if exists cpy_tbl_r_n2;

drop table if exists cpy_tbl_rr_n12;
drop table if exists cpy_tbl_rr_n2;

drop table if exists cpy_tbl_h_n12;
drop table if exists cpy_tbl_h_n2;