redraiment_个人页

提供两种思路：

• 将Excel导出成CSV文件，然后通过COPY导入
• 通过Python等语言定义UDF，实现Excel文件的解析，自动创建表并导入数据

参考：https://stackoverflow.com/questions/20039856/import-excel-data-into-postgresql-9-3

根据题目的描述，假设表dm_org，有四个字段：

• org_id int：节点唯一编号。
• org_up_id int：父节点编号，根据decode(t.org_up_id , t.org_id , 0 , t.org_up_id)，推测根节点的org_up_id等于org_id。
• org_level int：层级？
• org_name varchar(64)：名称

假设表中包含以下六行记录：

利用题主的SQL查询结果如下：

因为没有指定start with语句，从功能上来说，是递归查询dm_org表所有记录的子节点。与之对应的PostgreSQL的查询语句是：

with recursive leafs as (
  select
   distinct
    parents.org_id as leaf_id
  from
    dm_org as parents
  left join
    dm_org as children
  on
    parents.org_id = children.org_up_id
  where
    children.org_id is null
), trees as (
  select
    org_id,
    org_name,
    org_level,
    org_id as org_up_id,
    org_name as org_up_name,
    org_level as org_up_level
  from
    dm_org

  union all

  select
    dm_org.org_id,
    dm_org.org_name,
    dm_org.org_level,
    trees.org_up_id,
    trees.org_up_name,
    trees.org_up_level
  from
    dm_org
  inner join
    trees
  on
    dm_org.org_up_id = trees.org_id
    and dm_org.org_id != trees.org_id
)
select
  trees.*,
  leafs.leaf_id is not null as leaf
from
  trees
left join
  leafs
on
  trees.org_id = leafs.leaf_id
order by
  org_up_id,
  org_id;

定义了两个公共表表达式，其中：

• leafs：获得所有叶子节点。因为PostgreSQL没有提供connect_by_isleaf这样的关键字。
• trees：递归查询所有记录的子节点。

看问题描述，你是要直接开通PG数据库的直连用户？

• 问题1：不需要创建schema，用create user xxx创建用户即可，相应的权限用grant来赋予；
• 问题2：如果空间不是问题，可以考虑修改postgresql.conf的日志选项log_statement = 'all'，这样就会把所有执行的语句都输出到日志文件中，配置如下：

log_destination = 'csvlog'
log_directory = 'pg_logs'
log_filename = 'postgresql-%Y-%m-%d.log'
log_rotation_age = 1d
log_statement = 'all'

按照上面的配置，日志会输出到$PGDATA/pg_logs/postgresql-YYYY-MM-DD.log文件中，一天一个文件。

例如，我用redraiment用户在postgres数据库中执行了三条SQL，日志中得到结果如下：

2018-07-09 22:13:57.746 CST,"redraiment","postgres",11009,"[local]",5b436da3.2b01,1,"",2018-07-09 22:13:55 CST,3/9,0,日志,00000,"语句: create table users (id bigserial primary key, name text);",,,,,,,,,"psql"
2018-07-09 22:13:58.969 CST,"redraiment","postgres",11009,"[local]",5b436da3.2b01,2,"",2018-07-09 22:13:55 CST,3/10,0,日志,00000,"语句: insert into users (name) values ('Joe'), ('Alice'), ('Wiki');",,,,,,,,,"psql"
2018-07-09 22:14:00.546 CST,"redraiment","postgres",11009,"[local]",5b436da3.2b01,3,"",2018-07-09 22:13:55 CST,3/11,0,日志,00000,"语句: select * from users;",,,,,,,,,"psql"

因为问题描述中没有提到数据模型，我假设alarm表与alarm_date表的结构如下：

create table alarm(
  id serial primary key,
  description text,
  hits int
);
comment on column alarm.hits is '告警的次数';

create table alarm_date (
  id serial primary key,
  aid int references alarm,
  create_time timestamp
);

假设alarm与alarm_date是“one to many”的关系，则

查询2018-11-11之后发生的告警，并按告警次数倒序的查询SQL如下：

select
  id,
  description,
  hits
from
  alarm
inner join (
  select
   distinct
    aid
  from
    alarm_date
  where
    create_time > '2018-11-11'
) as records
on
  alarm.id = records.aid
order by
  hits desc

猜测md5_wp_cur这个字段的类型是varchar(32)，所以代码中array[md5_wp_cur]::varchar[]转换成非32位varchar的数组时，会有类型不匹配的问题。可以改成：

...
array[md5_wp_cur]::varchar(32)[],
...
(path || g.md5_wp_cur)::varchar(32)[],