[PostgreSQL] Dblink insert,update

-- Drop table

 

-- DROP TABLE naqsifh.tn_menu_atchmnfl_m;

 

CREATE TABLE tn_menu_atchmnfl_m (

menu_id varchar(20) NOT NULL,

atchmnfl_posbl_mg numeric(16, 4) NULL,

del_yn bpchar(1) NOT NULL,

register_id varchar(20) NOT NULL,

reg_dt timestamp NOT NULL,

updusr_id varchar(20) NOT NULL,

mdfcn_dt timestamp NOT NULL,

CONSTRAINT pk_n_menu_atchmnfl_m PRIMARY KEY (menu_id)

)

 

-- Table Triggers

 

create trigger tg_n_menu_atchmnfl_m_ar after

insert

    or

update

    on

    tn_menu_atchmnfl_m for each row execute procedure tg_pr_db_link_test();

 

 

CREATE OR REPLACE FUNCTION tg_pr_db_link_test()

RETURNS TRIGGER

AS $$

DECLARE

insert_statement TEXT;

    update_statement TEXT;

        res TEXT;

BEGIN

BEGIN

perform dblink_connect('postgres', 'hostaddr=192.168.1.26 port=5432 dbname=naqs user=naqsifh password=ifhnaqs2021');

 

EXCEPTION

                   WHEN duplicate_object THEN --code error 42710

            RAISE NOTICE 'this connections exists';

    END;

  

if(TG_OP = 'INSERT') then

insert_statement = 'insert into tn_menu_atchmnfl_m(

                                      MENU_ID,

      ATCHMNFL_POSBL_MG,

      DEL_YN,

      REGISTER_ID,

      REG_DT,

  updusr_id,

                                      mdfcn_dt

             )

            values ('''||NEW.MENU_ID||''',

                                    '''||NEW.ATCHMNFL_POSBL_MG||''',

                                    '''||NEW.DEL_YN||''',

                                    '''||NEW.REGISTER_ID||''',

                                    '''||NEW.REG_DT||''',

                                    '''||NEW.updusr_id||''',

                                    '''||NEW.mdfcn_dt||'''

                                    );';

                 res := dblink_exec('postgres', insert_statement, true);

        RAISE INFO '%', res;

        perform dblink_disconnect('postgres');

        RETURN NEW;

 

       elsif(TG_OP = 'UPDATE') then                           

                update_statement = 'update tn_menu_atchmnfl_m

                                      SET DEL_YN = '''||NEW.DEL_YN||''',

          ATCHMNFL_POSBL_MG = '''||NEW.ATCHMNFL_POSBL_MG||'''

                                     WHERE menu_id = '''||OLD.MENU_ID||'''

                                    ;';

                                  

              res := dblink_exec('postgres', update_statement, true);

              RAISE INFO '%', res;

          perform dblink_disconnect('postgres');

          return NEW;

 

        end if;

      RETURN NULL;                        

END; $$

LANGUAGE 'plpgsql';

 

 

select * FROM TN_MENU_ATCHMNFL_M;

 

 

INSERT INTO TN_MENU_ATCHMNFL_M

    (

      MENU_ID,

      ATCHMNFL_POSBL_MG,

      DEL_YN,

      REGISTER_ID,

      REG_DT,

      updusr_id ,

      mdfcn_dt

    )

    VALUES

    (

      'DBLINK11',

      '1',

      'N',

      'SYSTEM',

      NOW(),

      'SYSTEM',

      NOW()

    );

   

   UPDATE TN_MENU_ATCHMNFL_M

     SET DEL_YN = 'Y'

   WHERE menu_id = 'DBLINK8'