pgsql 批量创建字段和触发器

pgsql 批量创建字段和触发器

-- FUNCTION: public.fn_add_trigger()

-- DROP FUNCTION public.fn_add_trigger();

CREATE OR REPLACE FUNCTION public.fn_add_trigger(
	)
    RETURNS character varying
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
    
AS $BODY$
DECLARE
r record;
col integer;
tig integer;
BEGIN

FOR r IN SELECT tablename FROM pg_tables WHERE SCHEMANAME = 'public'
LOOP
    SELECT COUNT(*) INTO col FROM information_schema.columns WHERE table_name = ''|| r.tablename ||'' AND column_name = 'create_at';
	IF col = 0 THEN 
		RAISE NOTICE '创建字段create_at %', col;
		EXECUTE 'ALTER TABLE '|| r.tablename ||' ADD create_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP';
		EXECUTE 'COMMENT ON COLUMN public.'|| r.tablename ||'.create_at IS ''创建时间''';
    END IF;
	
	SELECT COUNT(*) INTO col FROM information_schema.columns WHERE table_name = ''|| r.tablename ||'' AND column_name = 'update_at';
	IF col = 0 THEN 
		RAISE NOTICE '创建字段update_at %', col;
		EXECUTE 'ALTER TABLE '|| r.tablename ||' ADD update_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP';
		EXECUTE 'COMMENT ON COLUMN public.'|| r.tablename ||'.update_at IS ''更新时间''';
		
		RAISE NOTICE '创建触发器';
		EXECUTE 'CREATE TRIGGER '|| r.tablename ||'_update_timestamp BEFORE UPDATE ON public.'|| r.tablename ||' FOR EACH ROW EXECUTE PROCEDURE public.update_timestamp()';
	ELSE
	    SELECT COUNT(*) INTO tig FROM pg_trigger WHERE tgname=''|| r.tablename ||'_update_timestamp';
		IF tig = 0 THEN
			RAISE NOTICE '创建触发器';
			EXECUTE 'CREATE TRIGGER '|| r.tablename ||'_update_timestamp BEFORE UPDATE ON public.'|| r.tablename ||' FOR EACH ROW EXECUTE PROCEDURE public.update_timestamp()';
    	END IF;
	END IF;
	RAISE NOTICE 'tables_name %', r.tablename; 
END LOOP;

RETURN 'SUCCESS';

END;
$BODY$;

ALTER FUNCTION public.fn_add_trigger()
    OWNER TO postgres;