IF TG_OP <> 'INSERT' OR TG_TABLE_NAME <>'base_table_machine1' OR TG_WHEN <>
'BEFORE' THEN

RETURN NULL;

END IF;

--Generate Table Name

str_sub_sample_time = date_part('year',NEW.sample_time)::varchar || '_' ||

CASE WHEN date_part('month',NEW.sample_time) <10 THEN '0' ELSE '' END

||date_part('month',NEW.sample_time)::varchar;

str_sub_tablename = 'machine1_' || str_sub_sample_time;

--Check if table not created

select * from pg_tables where schemaname = 'public' and tablename=str_sub_
tablename

into str_sql_cmd;

IF NOT FOUND THEN

--Create table Cmd

str_sql_cmd = '

CREATE TABLE '||str_sub_tablename||'

(

CONSTRAINT pk_'|| str_sub_tablename||' PRIMARY KEY (id ),

CONSTRAINT chk_'|| str_sub_tablename||'

CHECK(date_part(''year''::text, sample_time) = '||

date_part('year',NEW.sample_time)::varchar||

'::double precision AND

date_part(''month''::text, sample_time) = '||

date_part('month',NEW.sample_time)::varchar||'

)

)

INHERITS (base_table_machine1)

WITH ( OIDS=FALSE );

ALTER TABLE '||str_sub_tablename||' OWNER TO postgres;

CREATE INDEX idx_'|| str_sub_tablename||'_sample_time

ON '|| str_sub_tablename||'

USING btree (sample_time );

';

EXECUTE str_sql_cmd;

END IF;

--insert Data

str_sql_cmd = 'INSERT INTO '||str_sub_tablename||'

( id,dvalue,sample_time,machine_code,max_res,curr_res) VALUES (

nextval(''serial_id_seq''),$1,$2,$3,$4,$5);

';

EXECUTE str_sql_cmd USING

NEW.dvalue,

NEW.sample_time,

NEW.machine_code,

NEW.max_res,

NEW.curr_res;

--return null because main table does not really contain data

return NULL;

END;

$BODY$

LANGUAGE plpgsql VOLATILE

COST 100;

ALTER FUNCTION on_insert_base_table_machine1()

OWNER TO postgres;