再一次证明, python 和 postgresql是完美的结合

最新推荐文章于 2024-07-27 18:56:22 发布

weixin_34195546

最新推荐文章于 2024-07-27 18:56:22 发布

阅读量141

点赞数

CC 4.0 BY-SA版权

文章标签： python 数据库 json

原文链接：https://my.oschina.net/anthonychen/blog/162943

本文详细介绍了如何使用SQL查询语言从LOVD（List of Values Definition）表中获取LOV（List of Values）路径的过程。通过自定义函数`getlovpath`，实现从特定LOVD ID出发，遍历获取所有相关LOV ID及其层级结构，最终以JSON格式返回结果。此过程涉及到SQL查询、函数编写和数据结构理解。

2019独角兽企业重金招聘Python工程师标准>>>

还是一样的风格, 独孤求败

-- Function: cms.getlovpath(integer, integer)

-- DROP FUNCTION cms.getlovpath(integer, integer);

CREATE OR REPLACE FUNCTION cms.getlovpath(i_lovd_id integer, i_lov_id integer)
  RETURNS json AS
$BODY$import ujson as json 
import plpy
_sql = 'select k from cms.lov where id=$1';
plan = plpy.prepare(_sql,["integer"])
rv = plpy.execute(plan,[i_lov_id],1)
_k = rv[0]['k']
_sql= 'select vc.lov_id,vc.id,vc.pid,vct.level from (select * from cms.v_lov where lovd_id=$2'\
+""") vc,(select * from connectby('(select * from cms.v_lov where lovd_id="""\
+str(i_lovd_id)+""") t','pid','id','id',$1"""\
+""",0,';') as t(id int, pid int, level int, branch text, pos int)) vct where vct.id=vc.id order by vct.level DESC offset 1"""
rs = {}
#return json.dumps(_sql);
plan = plpy.prepare(_sql,["varchar","integer"])
for row in plpy.cursor(plan,[str(_k),i_lovd_id]):
#	rs[row['lov_id']]={'id':row['id'],'pid':row['pid']}
	rs[row['pid']]=row['lov_id']
return json.dumps(rs);$BODY$
  LANGUAGE plpython3u VOLATILE
  COST 100;
ALTER FUNCTION cms.getlovpath(integer, integer)
  OWNER TO postgres;

转载于:https://my.oschina.net/anthonychen/blog/162943