-- Function: create_partition(date)

-- DROP FUNCTION create_partition(date);

CREATE OR REPLACE FUNCTION create_partition(dt date DEFAULT now())
  RETURNS integer AS
$BODY$declare tomorrow date;
begin
  tomorrow = dt + interval '1 days';

  execute 'CREATE TABLE "MY_TBL_'||dt||'"
(
  CONSTRAINT "MY_TBL_'||dt||'_pkey" PRIMARY KEY ("ID" ),
  CONSTRAINT "MY_TBL_'||dt||'_DATE_CHECK" CHECK ("DATE" >= DATE '''||dt||''' AND "DATE" < DATE '''||tomorrow||''' )
)
INHERITS ("MY_TBL")
WITH (
  OIDS=FALSE
);
ALTER TABLE "MY_TBL_'||dt||'"
  OWNER TO postgres;';
  return null;
exception when duplicate_table then
  return null;
end;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION create_partition(date)
  OWNER TO postgres;



-- Function: my_tbl_redirect()

-- DROP FUNCTION my_tbl_redirect();

CREATE OR REPLACE FUNCTION my_tbl_redirect()
  RETURNS trigger AS
$BODY$

begin

  if (NEW."DATE" >= DATE '2012-03-25' AND NEW."DATE" < DATE '2012-03-26') then
    insert into "MY_TBL_2012-03-25" values(NEW.*);
  else -- for today, normal case
    insert into "MY_TBL_2012-03-26" values(NEW.*);
  end if;
  return null;

exception when undefined_table or check_violation then

  -- create missing table
  perform create_partition(date(NEW."DATE"));

  -- alter itself
  perform update_my_tbl_redirect(date(NEW."DATE"));

  -- insert again
  execute 'insert into "MY_TBL_'||date(NEW."DATE")||'"
    values('||quote_literal(NEW."ID")||',
    '||quote_literal(date(NEW."DATE"))||',
    '||quote_literal(NEW."OTHER_FIELD")||')';
  return null;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION my_tbl_redirect()
  OWNER TO postgres;


-- Function: update_my_tbl_redirect(date)

-- DROP FUNCTION update_my_tbl_redirect(date);

CREATE OR REPLACE FUNCTION update_my_tbl_redirect(dt date DEFAULT now())
  RETURNS integer AS
$BODY1$declare yesterday date;

begin
  yesterday = dt - interval '1 days';

  execute 'CREATE OR REPLACE FUNCTION my_tbl_redirect()
  RETURNS trigger AS
$BODY$

begin

  if (NEW."DATE" >= DATE '''||yesterday||''' AND NEW."DATE" < DATE '''||dt||''') then
    insert into "MY_TBL_'||yesterday||'" values(NEW.*);
  else -- for today, normal case
    insert into "MY_TBL_'||dt||'" values(NEW.*);
  end if;
  return null;

exception when undefined_table or check_violation then

  -- create missing table
  perform create_partition(date(NEW."DATE"));

  -- alter itself
  perform update_my_tbl_redirect(date(NEW."DATE"));

  -- insert again
  execute ''insert into "MY_TBL_''||date(NEW."DATE")||''"
    values(''||quote_literal(NEW."ID")||'',
    ''||quote_literal(date(NEW."DATE"))||'',
    ''||quote_literal(NEW."OTHER_FIELD")||'')'';
  return null;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION my_tbl_redirect()
  OWNER TO postgres;';

  return null;
end;$BODY1$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION update_my_tbl_redirect(date)
  OWNER TO postgres;

