postgresql plpythonu例子

以下代码仅作为参考之用

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