李大仁博客

import sys, os
import numpy as np
 
def get_object_by_gid(id):
	for dict in data["json"]:
		if dict["gid"] == id:
	return dict.copy()
 
def self_exact_node(key):
	dict = get_object_by_gid(key)
	for k,v in dict.items():
		if k == "sub_item" :
			item_arr = []
			for id in v["gids"]:
				item_arr.append(self_exact_node(id))
			v["item_arr"] = item_arr.copy()
	return dict
 
def demo():
	data_exact = data.copy()
	for d in data_exact["json"]:
		d = self_exact_node(d["gid"])
 
def main():
	demo()
 
if __name__ == '__main__’:
	sys.exit(main())

# Global ID方式
"gid": "大分类2:中分类2:子分类2",
# 复合ID
"id":{"l1_cat":"大分类1","l2_cat":"中分类2","l3_cat":"子分类2"}
# 链表式
"chain":{"next_gid":"","pre_gid":"","head_gid":"","tail_gid":""}
# 二叉树式
"btree":{"next_sibling":"","child":”"}

DROP FUNCTION fun_datecursor(from_date text,to_date text,cond text);
CREATE OR REPLACE FUNCTION fun_datecursor(from_date text,to_date text,cond text) RETURNS text
AS $ 
DECLARE
    cur_date refcursor;
    buffer text := '';
    var_day DATE;
BEGIN
OPEN cur_date FOR EXECUTE 'SELECT generate_series('''|| from_date ||'''::date,'''|| to_date ||'''::date,'''|| cond ||''')'; 
    loop  --开始循环
        fetch cur_date INTO var_day;  --将游标指定的值赋值给变量
        IF found THEN 
             ---------------------------------------
             --任意的逻辑，或调用其他function
             ---------------------------------------
            buffer:= buffer || to_char(var_day,'yyyyMMdd'); 
           ELSE 
            exit; 
            END IF; 
    END loop;  --结束循环
close cur_date;  --关闭游标
RETURN buffer;
END
$ LANGUAGE plpgsql;

SELECT fun_datecursor('2008-03-01','2008-03-31','1 day’);

-- 创建readonly_user用户，密码为readonly_password
CREATE USER readonly_user WITH encrypted password 'readonly_password';
-- 设置readonly_user用户为只读事务
ALTER USER readonly_user SET default_transaction_read_only=ON;
-- 授予usage权限给到readonly_user用户
GRANT usage ON schema "public" TO readonly_user;
-- 将默认"public"schema下新建表的读取权限授予给readonly_user
ALTER DEFAULT privileges IN schema "public" GRANT SELECT ON TABLES TO readonly_user;
-- 授予select权限给到readonly_user用户
GRANT SELECT ON ALL TABLES IN schema "public" TO readonly_user;
GRANT SELECT ON ALL sequences IN schema "public" TO readonly_user;
-- 允许readonly_user用户连接到指定数据库
GRANT CONNECT ON DATABASE splrp_dev TO readonly_user;

-- 授予usage权限给到readonly_user用户
GRANT usage ON schema "public" TO readonly_user;
-- 授予select权限给到readonly_user用户
GRANT SELECT ON ALL TABLES IN schema "public" TO readonly_user;
GRANT SELECT ON ALL sequences IN schema "public" TO readonly_user;

-- 将默认"public"schema下新建表的读取权限授予给readonly_user
ALTER DEFAULT privileges IN schema "public" GRANT SELECT ON TABLES TO readonly_user;

--查出单个表的大小
SELECT pg_size_pretty(pg_relation_size('TABLENAME'));

-- 查出表大小按大小含Index
SELECT
"table_name",
pg_size_pretty(table_size) AS table_size,
pg_size_pretty(indexes_size) AS indexes_size,
pg_size_pretty(total_size) AS total_size
FROM (
SELECT
TABLE_NAME,
SUBSTRING("table_name",1,10) AS short_name,
pg_table_size(TABLE_NAME) AS table_size,
pg_indexes_size(TABLE_NAME) AS indexes_size,
pg_total_relation_size(TABLE_NAME) AS total_size
FROM (
SELECT ('"' || table_schema || '"."' || TABLE_NAME || '"') AS TABLE_NAME
FROM information_schema.tables
) AS all_tables
WHERE all_tables.table_name LIKE '%TABLENAME%'
ORDER BY total_size DESC
) AS pretty_sizes

SELECT * FROM pg_stat_activity WHERE datname='schema名称';

SELECT pg_cancel_backend(pid);

SELECT  pg_terminate_backend(pid)；