DROP  TABLE  cmap;

DROP  TABLE  "data";

create table cmap (

  cmap_id numeric NOT NULL,

  cmap_term_id numeric NOT NULL,

  hosp_term_cd character(4) NOT NULL,

  hosp_term_div_stts numeric(1) NOT NULL DEFAULT 0,

  CONSTRAINT xpkcmap_term PRIMARY KEY (cmap_id, cmap_term_id)

) ;

CREATE TABLE "data" (

  cmap_id numeric NOT NULL,

  cmap_term_id numeric NOT NULL,

  md_no SERIAL,

  md_list_cd character(2) NOT NULL default 'a',

  hosp_term_cd character(4),

  hosp_term_div_stts numeric(1),

  CONSTRAINT xpkcmap_data PRIMARY KEY (cmap_id, cmap_term_id, md_no, md_list_cd)

);

drop FUNCTION  cmap_to_data_func();

CREATE  FUNCTION  cmap_to_data_func()  RETURNS  OPAQUE

AS

$function$

BEGIN

        EXECUTE ' INSERT INTO data (cmap_term_id, cmap_id, hosp_term_cd,hosp_term_div_stts) values ( '|| NEW.cmap_term_id || ','|| NEW.cmap_id || ',' || quote_literal(NEW.hosp_term_cd) || ',' || NEW.hosp_term_div_stts || ' ) ';

  RETURN  NEW;

END;

$function$

LANGUAGE plpgsql;

CREATE  TRIGGER  cmap_to_data before INSERT  OR  UPDATE  ON  cmap

FOR  EACH  ROW  EXECUTE  PROCEDURE  cmap_to_data_func();

INSERT  INTO  cmap(  cmap_term_id, cmap_id, hosp_term_cd,hosp_term_div_stts) values (  8.2, 4.3, 'abc',1 );

INSERT  INTO  cmap(  cmap_term_id, cmap_id, hosp_term_cd,hosp_term_div_stts) values (   7.6, 9.2, 'abcd',3 );

SELECT  *  FROM  cmap;

SELECT  *  FROM  data;