以下代码仅作为参考之用

select md5, crc32, record->'UserModerAnalysis'->'base_info'->'file_malware' as file_malware

from reports

CREATE OR REPLACE FUNCTION py_get_file_malware(record TEXT)

    RETURNS TEXT

AS $$

    # pl/python functioin body

    import json

    plpy.notice('type of record is', type(record))

    # plpy.notice('import json')

    # plpy.notice('begin to loads()')

    #if 'json' in SD:

    #    json = SD['json']

    #else:

    #    import json

    #    SD['json'] = json

    obj = json.loads(record)

    plpy.notice('UserModerAnalysis = %s'%(str(obj['UserModerAnalysis'])))

    try:

    file_malware = obj['UserModerAnalysis']['base_info']['file_malware']

    except Exception, e:

    #plpy.error(record)

    plpy.notice('ERROR!')

    file_malware = ''

    return file_malware

$$ LANGUAGE plpythonu

select md5, crc32, py_get_file_malware(record::TEXT)

from reports

limit 2

-- create table summary

CREATE TABLE summary_file_malware

(

  description character varying(10) NOT NULL,

  count integer,

  CONSTRAINT summary_file_malware_pkey PRIMARY KEY (description)

)

DROP FUNCTION calculate_file_malware()

CREATE OR REPLACE FUNCTION calculate_file_malware()

    RETURNS trigger AS $$

    plpy.notice('calculate_file_malware invoked')

    import json

    event = TD['event']

    

    if event == 'INSERT':

        plpy.notice('insert triggered')

    elif event == 'UPDATE':

        plpy.notice('update triggered')

        # parse parameter

    old_obj = json.loads(TD['old']['record'])

    new_obj = json.loads(TD['new']['record'])

    plpy.notice('old = %s, new = %s'%(old_obj['UserModerAnalysis']['base_info']['file_malware'],

        new_obj['UserModerAnalysis']['base_info']['file_malware']))

        

        # sub old

        try:

        plpy.notice('begin')

            plan = plpy.prepare('SELECT * FROM summary_file_malware WHERE description = $1', ['text'])

        old_value = old_obj['UserModerAnalysis']['base_info']['file_malware']

        plpy.notice("old_value = " + old_value)

        rv = plpy.execute(plan, [old_value], 1)

        old_count = int(rv[0]['count'])

        plpy.notice('old_count = %s'%(old_count))

        plan = plpy.prepare('UPDATE summary_file_malware SET count = $1 WHERE description = $2', ['int', 'text'])

        plpy.execute(plan, [old_count - 1, old_value])

    except Exception, e:

        plpy.notice('exception occured, exception msg = '+str(e))

    # add new

        try:

            plan = plpy.prepare('SELECT * FROM summary_file_malware WHERE description = $1', ['text'])

        old_value = new_obj['UserModerAnalysis']['base_info']['file_malware']

        rv = plpy.execute(plan, [old_value], 1)

        old_count = int(rv[0]['count'])

        plpy.notice('old_count = %s'%(old_count))

        plan = plpy.prepare('UPDATE summary_file_malware SET count = $1 WHERE description = $2', ['int', 'text'])

        plpy.execute(plan, [old_count + 1, old_value])

    except Exception, e:

        plpy.notice('exception occured, exception msg = '+str(e))

    

    elif event == 'DELETE':

        plpy.notice('delete triggered')

    elif event == 'TRUNCATE':

        plpy.notice('trancate triggered')

    else:

        plpy.notice('unknow event, event = ', event)

$$ LANGUAGE plpythonu

DROP TRIGGER IF EXISTS calculate on reports;

CREATE TRIGGER  calculate AFTER UPDATE OF record

    ON reports

    FOR EACH ROW

    EXECUTE PROCEDURE calculate_file_malware ();

SELECT * FROM summary_file_malware WHERE description ='OK'

INSERT INTO summary_file_malware VALUES('OK', 0)

UPDATE reports SET record = '{"Name": "000BD3A69E56CD5E8D998FEDA8EF3CA6.CCD2FFE1", "UserModerAnalysis": {"base_info": {"file_malware": "YES"}, "file_monitor": [], "virusname": null, "danger_behavior": [], "relation": {"processtree": [{"processid": "608", "process": "000BD3A69E56CD5E8D998FEDA8EF3CA6.CCD2FFE1", "module": "", "parentid": 0, "relationtype": "Root", "id": 1}]}, "other_behavior": [], "network_monitor": [], "process_monitor": [], "reg_monitor": []}, "KernelModelAnalysis": {"MaliciousActives": {"000BD3A69E56CD5E8D998FEDA8EF3CA6.CCD2FFE1": {"MemoryOperations": {}, "FileOperations": {"CREATE_FILE.DROP_PE_TO_SYSTEM_DIR": [{"COMMENT": "Create_File_In_SystemDirectory", "DETAILS": {"file_path": "c:\\windows\\.exe"}, "LEVEL": "LEVEL_3"}]}, "NetworkOperations": {}, "ProcessOperations": {}, "RegistryOperations": {}, "OtherOperations": {}}}, "ProcessFamily": {"000BD3A69E56CD5E8D998FEDA8EF3CA6.CCD2FFE1": {"Parent_Process": "", "Command_Line": "", "Type_Created": "Root"}}, "ProcessActives": {"000BD3A69E56CD5E8D998FEDA8EF3CA6.CCD2FFE1": {"MemoryOperations": {}, "FileOperations": {"DELETE_FILE": [{"COMMENT": "Delete_File_Found", "DETAILS": {"file_path": "C:\\DOCUME~1\\autoer\\LOCALS~1\\Temp\\~DFCCF6.tmp"}, "LEVEL": "LEVEL_2"}], "CREATE_FILE": [{"COMMENT": "Create_File_Found", "DETAILS": {"file_path": "C:\\DOCUME~1\\autoer\\LOCALS~1\\Temp\\~DFCCF6.tmp"}, "LEVEL": "LEVEL_2"}]}, "NetworkOperations": {}, "ProcessOperations": {}, "RegistryOperations": {"SET_KEY_VALUE": [{"COMMENT": "Set_Key_Value_Found", "DETAILS": {"value": "Drive", "type": "REG_SZ", "name": "BaseClass", "key": "HKEY_USERS\\S-1-5-21-1708537768-287218729-1177238915-1003\\Software\\Microsoft\\Windows\\CurrentVersion\\Explorer\\MountPoints2\\{7fb46850-baea-11e1-9890-806d6172696f}"}, "LEVEL": "LEVEL_2"}]}, "OtherOperations": {}}}, "TimeOfReportCreated": "2013-06-03 11:25:25:724 +0800", "Summary": ["CREATE_FILE", "CREATE_FILE.DROP_PE_TO_SYSTEM_DIR", "DELETE_FILE", "SET_KEY_VALUE"], "FileName": "000BD3A69E56CD5E8D998FEDA8EF3CA6.CCD2FFE1"}, "Result": "Success", "Time": "2013-06-03 11:25:25:724 +0800", "DESCRIPTION": "\u64cd\u4f5c\u6210\u529f\u5b8c\u6210\u3002"}' WHERE md5 = '000BD3A69E56CD5E8D998FEDA8EF3CA6' and crc32 = 'CCD2FFE1'

select * from summary_file_malware