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;