postgresql 数据库级 日志触发器



CREATE OR REPLACE FUNCTION log_trigger()
  RETURNS trigger AS
$BODY$
DECLARE
 table_name varchar;
 option_type varchar;
 column_names varchar;
 column_name_arr text[];

 
 column_names_sql varchar;
 column_names_views RECORD;

 new_views RECORD;
 new_val text;

 log_sql varchar;

BEGIN

 table_name := TG_RELNAME;
 option_type := TG_OP;

 --EXECUTE 'select array_to_string(array( select
 --column_name::text
 --FROM information_schema.columns
 --WHERE table_schema = ''public'' and table_name='''||TG_RELNAME||'''),'','');';
 
-- column_names_sql := 'select array_to_string(array( select
-- column_name::text
-- FROM information_schema.columns
-- WHERE table_schema = ''public'' and table_name='''||TG_RELNAME||'''),'', '') as column_names ;';

-- FOR column_names_views IN EXECUTE column_names_sql LOOP
--  column_names := column_names_views.column_names;
-- END LOOP;
 
 column_names_sql := 'select array( select
 column_name::text
 FROM information_schema.columns
 WHERE table_schema = ''public'' and table_name='''||TG_RELNAME||''') as column_names;';

 FOR column_names_views IN EXECUTE column_names_sql LOOP
  column_name_arr := column_names_views.column_names;
 END LOOP;

 --insert into debug(result) values(array_to_string(column_name_arr,', ($1).'));

 log_sql := 'insert into '||table_name||'_log (option_type, log_date, '||array_to_string(column_name_arr,', ')||')
values('||quote_literal(option_type)||',current_timestamp, ($1).'||array_to_string(column_name_arr,', ($1).')||');';

 insert into debug(result) values(log_sql);

 IF TG_OP='DELETE' OR TG_OP='UPDATE' THEN

  EXECUTE log_sql USING OLD;

 END IF;

 IF TG_OP='INSERT' OR TG_OP='UPDATE' THEN

--  FOR column_names IN select unnest(column_name_arr)
--  LOOP

--   --insert into debug(result) values(column_names);

--   FOR new_val IN EXECUTE 'SELECT ($1).'||column_names||'::text val' USING NEW
--   LOOP

--    insert into debug(result) values(new_val);
--   END LOOP;
   
--  END LOOP;

  EXECUTE log_sql USING NEW;

 END IF;

 
return NEW;
EXCEPTION
WHEN UNIQUE_VIOLATION THEN

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION log_trigger()
  OWNER TO postgres;