methods/sketch/src/pg_gp/sql/fm.sql_in

---------------------------------------------------------------------------
-- Rules:
-- ------
-- 1) Any DB objects should be created w/o schema prefix,
--    since this file is executed in a separate schema context.
-- 2) There should be no DROP statements in this script, since
--    all objects created in the default schema will be cleaned-up outside.
---------------------------------------------------------------------------

---------------------------------------------------------------------------
-- Setup:
---------------------------------------------------------------------------
CREATE FUNCTION fm_install_test() RETURNS VOID AS $$
declare

	result INT[];
	result2 INT;

begin
	-- DROP TABLE IF EXISTS "FM_DATA";
	CREATE TABLE "FM_DATA"(class INT, a1 INT);
	INSERT INTO "FM_DATA" SELECT 1,1 FROM generate_series(1,10000);
	INSERT INTO "FM_DATA" SELECT 1,2 FROM generate_series(1,15000);
	INSERT INTO "FM_DATA" SELECT 1,3 FROM generate_series(1,10000);
	INSERT INTO "FM_DATA" SELECT 2,5 FROM generate_series(1,1000);
	INSERT INTO "FM_DATA" SELECT 2,6 FROM generate_series(1,1000);

	-- DROP TABLE IF EXISTS fm_result_table;
	CREATE TABLE fm_result_table AS
	SELECT (fmsketch_dcount(a1)) as val FROM "FM_DATA" GROUP BY class ORDER BY class;

	SELECT array( SELECT val FROM fm_result_table) INTO result;
	IF ((result[1] + result[2]) != 5) THEN
		RAISE EXCEPTION 'Incorrect fmsketch_dcount results, got %',result;
	END IF;
	TRUNCATE fm_result_table;


	RAISE INFO 'FM-Sketches install checks passed';
	RETURN;

end
$$ language plpgsql;

---------------------------------------------------------------------------
-- Test:
---------------------------------------------------------------------------
SELECT fm_install_test();

-- Tests for "little" tables using sortasorts
select fmsketch_dcount(R.i)
  from generate_series(1,100) AS R(i),
       generate_series(1,3) AS T(i);

select fmsketch_dcount(CAST('2010-10-10' As date) + CAST((R.i || ' days') As interval))
  from generate_series(1,100) AS R(i),
       generate_series(1,3) AS T(i);

select fmsketch_dcount(R.i::float)
  from generate_series(1,100) AS R(i),
       generate_series(1,3) AS T(i);

select fmsketch_dcount(R.i::text)
  from generate_series(1,100) AS R(i),
       generate_series(1,3) AS T(i);


-- Tests for "big" tables
select fmsketch_dcount(T.i)
  from generate_series(1,3) AS R(i),
       generate_series(1,20000) AS T(i);

select fmsketch_dcount(CAST('2010-10-10' As date) + CAST((T.i || ' days') As interval))
  from generate_series(1,3) AS R(i),
       generate_series(1,20000) AS T(i);

select fmsketch_dcount(T.i::float)
  from generate_series(1,3) AS R(i),
       generate_series(1,20000) AS T(i);

select fmsketch_dcount(T.i::text)
  from generate_series(1,3) AS R(i),
       generate_series(1,20000) AS T(i);

-- Tests for all-NULL column
select fmsketch_dcount(NULL::integer) from generate_series(1,10000) as R(i);