CREATE TABLE example (
	r DATE,
	p DATE
);

CREATE FUNCTION example_part_name(DATE) 
RETURNS char(18)
IMMUTABLE STRICT AS $$

	SELECT  'example_' || to_char($1,'YYYY_MM_DD');

$$ LANGUAGE sql;

CREATE FUNCTION example_part_exists(DATE) 
RETURNS BOOLEAN
IMMUTABLE STRICT AS $$

	SELECT count(tablename) > 0
	FROM pg_catalog.pg_tables 
	WHERE tablename = (SELECT example_part_name($1))
	LIMIT 1;

$$ LANGUAGE sql;

CREATE FUNCTION example_create_part(for_day DATE) 
RETURNS BOOLEAN STRICT AS $$
DECLARE
	tname char(18);
BEGIN
	tname := example_part_name(for_day);

	EXECUTE '
	CREATE TABLE ' || tname || ' (
		CHECK (r >= ' || quote_literal(for_day) || ' AND r < ' || quote_literal((for_day + interval '1 day')::date) || ')
	) INHERITS (example); 
        ';
	EXECUTE ' INSERT INTO ' || tname || ' VALUES (' || quote_literal(for_day) || ',' || quote_literal((for_day + interval '1 days')::date) || ');
                  INSERT INTO ' || tname || ' VALUES (' || quote_literal(for_day) || ',' || quote_literal((for_day + interval '2 days')::date) || ');';

	RETURN true;

	EXCEPTION WHEN OTHERS 
	THEN 
	RAISE NOTICE 'failed to create table %', tname;
	RAISE NOTICE '%', SQLERRM;
	RETURN false;

END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION example_drop_part(partname CHAR(18)) 
RETURNS BOOLEAN STRICT AS $$
BEGIN
	
	EXECUTE 'DROP TABLE ' || partname || ';';
	RETURN true;

	EXCEPTION WHEN OTHERS 
	THEN 
	RAISE NOTICE 'failed to drop table %', partname;
	RAISE NOTICE '%', SQLERRM;
	RETURN false;

END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION example(TIMESTAMP WITH TIME ZONE) 
RETURNS SETOF TIMESTAMP WITH TIME ZONE
STRICT AS $$
DECLARE
	tname char(18);
	c RECORD;
BEGIN
	tname := example_part_name($1::date);

	IF NOT example_part_exists($1::date)
	THEN
		IF NOT example_create_part($1::date)
		THEN
			RETURN;
		END IF;
	END IF;

	FOR c IN 
	EXECUTE ' SELECT r, (p + ' || quote_literal(($1 - date($1))::interval) || '::interval)::timestamptz AS p
                  FROM ' || quote_ident(tname) || ' 
                  WHERE r = ' || quote_literal($1::date) || ';'
	LOOP 
		RETURN NEXT c.p;
	END LOOP;
	
	RETURN;

END;
$$ LANGUAGE plpgsql;

-- works not: relation is still open
CREATE FUNCTION example_clean1(keep_from DATE, keep_to DATE) 
RETURNS SETOF DATE STRICT AS $$
DECLARE
	tname char(18);
	c RECORD;
	success boolean;
BEGIN

	IF keep_to < keep_from 
	THEN
		RETURN;
	END IF;

	FOR c IN EXECUTE '
	SELECT DISTINCT r, 0 as i
	FROM example 
	WHERE r NOT BETWEEN ' || quote_literal(keep_from) || ' AND ' || quote_literal(keep_to) || ';'
	LOOP
		BEGIN 
			EXECUTE 'SELECT example_drop_part(example_part_name(' || quote_literal(c.r) || '));' INTO success;
			EXCEPTION WHEN OTHERS 
			THEN 
				RAISE NOTICE 'failed to drop table %', example_part_name(c.r);
				RAISE NOTICE '%', SQLERRM;
			RETURN;
		END;
		IF success
		THEN 
			RETURN NEXT c.r; 
		END IF;
	END LOOP;

	RETURN;
END;
$$ LANGUAGE plpgsql;

-- works not: relation is still open
CREATE FUNCTION example_clean2(DATE, DATE) 
RETURNS SETOF DATE STRICT AS $$

	SELECT CASE WHEN example_drop_part(example_part_name(c.r)) THEN c.r END
	FROM (SELECT DISTINCT r
	      FROM example 
	      WHERE r NOT BETWEEN $1 AND $2) c(r);

$$ LANGUAGE sql;

-- works
CREATE OR REPLACE FUNCTION example_clean3(keep_from DATE, keep_to DATE) 
RETURNS SETOF DATE STRICT AS $$
DECLARE
	c RECORD;
BEGIN

	IF keep_to < keep_from 
	THEN
		RETURN;
	END IF;

	BEGIN
		EXECUTE '
		CREATE TEMP TABLE tmp_example_clean
		AS SELECT DISTINCT r
		FROM example 
		WHERE r NOT BETWEEN ' || quote_literal(keep_from) || ' 
        	        AND ' || quote_literal(keep_to) || ';';

		EXCEPTION WHEN OTHERS 
		THEN 
		RAISE NOTICE 'failed to create temp table tmp_example_clean';
		RAISE NOTICE '%', SQLERRM;
		RETURN;
	END;

	FOR c IN EXECUTE '
	SELECT  t.r, 
		example_drop_part(example_part_name(t.r)) AS success
	FROM tmp_example_clean t(r)'
	LOOP

		IF c.success
		THEN 
			RETURN NEXT c.r; 
		ELSE 
			RETURN;
		END IF;
	END LOOP;

	BEGIN
		EXECUTE 'DROP TABLE tmp_example_clean;';

		EXCEPTION WHEN OTHERS 
		THEN 
		RAISE NOTICE 'failed to drop table tmp_example_clean';
		RAISE NOTICE '%', SQLERRM;
		RETURN;
	END;

	RETURN;
END;
$$ LANGUAGE plpgsql;

-- create 3 parts with data
select * from example((current_date - interval '1 days')::date);
select * from example(current_date);
select * from example((current_date + interval '1 days')::date);

-- try to drop 2 parts
select * from example_clean1(current_date, current_date);
select * from example_clean2(current_date, current_date);

-- drop all parts
select * from example_clean3((current_date + interval '2 days')::date, (current_date + interval '2 days')::date);

drop function example_clean1(date, date);
drop function example_clean2(date, date);
drop function example_clean3(date, date);
drop function example(timestamptz);
drop function example_drop_part(char(18));
drop function example_create_part(date);
drop function example_part_exists(date);
drop function example_part_name(date);
drop table example;