python编写存储过程_plpython写的一个存储过程函数

今天花了好久使用plpython写了一个存储过程函数，终于解决了问题，庆祝一下，哈哈。下面把代码贴上来，可供参考： -- Function: bazaar_denim_nav(text) DROP FUNCTION bazaar_denim_nav(text); CREATE OR REPLACE FUNCTION bazaar_denim_nav(text)   RETURNS text AS $BODY$     import cPickle as pickle         # Unpack args     filters = [f.strip() for f in args[0].split(',')]     filters = [f for f in filters if f]     filters.append('designerdenimnav')     # Work out some SQL to extract all products that match a set of filters.     if filters:         # Find complete list of categories we should be using.         inClause = ','.join(['$'+str(i+1) for i in range(len(filters))])         qNodes = plpy.prepare(             """select lft_node, rgt_node from hierarchy where object in             (select id from category where alphanum in (%s))""" % inClause,             ['text']*len(filters)             )         nodes = list(plpy.execute(qNodes, filters))                 # Build query         parts = []         for node in nodes:             parts.append(                 """select distinct i.id from item i                 join item_category ic on ic.item_id=i.id                 join category c on c.id=ic.category_id                 where ic.category_id in (select object from hierarchy                 where lft_node >= %s and rgt_node <= %s)""" % (node['lft_node'], node['rgt_node'])                 )         filterSql = ' intersect '.join(parts)         filterSql = ' '.join(filterSql.split())     # Prepare most of the plans     if filters:         if len(filterSql) > 0:             qItems = plpy.prepare("select i.id from item i inner join stock_level sl on i.id=sl.item_id where sl.in_stock>0 and i.id in (%s) order by i.id"%filterSql)         else:             # Nothing matches the criteria so return now             return pickle.dumps([])     else:         qItems = plpy.prepare("select i.id from item i inner join stock_level sl on i.id=sl.item_id where sl.in_stock>0 order by i.id")     # get item lists     items = list(plpy.execute(qItems))     itemids = [item['id'] for item in items]     strItemIds = ', '.join([str(id) for id in itemids])     # define get sub categories function which filter by the in_stock     def getChildren(alphanum, strItemIds):         if strItemIds:             qChildren = plpy.prepare("""select distinct c.name, c.alphanum from category c inner join item_category ic on c.id=ic.category_id                  where c.id in (select object from hierarchy where                 parent=(select h.id from hierarchy h inner join category ca on ca.id=h.object where ca.alphanum='%s'))                 and ic.item_id in (%s)"""%(alphanum, strItemIds))             children = list(plpy.execute(qChildren))         else:             children = []         return children                      # Classification list     classification = {}     classification['brand'] = []     classification['type'] = []         classification['brand'] = getChildren('brand', strItemIds)     top_types = getChildren('designerdenimnav', strItemIds)     for temp in top_types:         t_alphanum = temp['alphanum'][1:]         t_name = temp['name'][1:]         t_children = getChildren(t_alphanum, strItemIds)         classification['type'].append({'name':t_name, 'alphanum':t_alphanum, 'children': t_children})             return pickle.dumps(classification)     $BODY$   LANGUAGE 'plpythonu' VOLATILE   COST 100; ALTER FUNCTION bazaar_denim_nav(text) OWNER TO postgres;