Postgres SQL - 笔记

设置临时变量

set session “unit.name” to “张三”;

select current_setting(‘unit.name’)

更多：https://www.jb51.net/article/204214.htm

根据父级id，查询子级

with recursive cte as(select a.atid,cast(a.name as varchar(200)) from app_aqxj_equipment a where a.atid='5cbd1b00-ca0f-461c-9f13-2215bdaf1d9c'union all select k.atid,cast(c.name||'>'||k.name as varchar(200)) as name  from app_aqxj_equipment k inner join cte c on c.atid = k.parent_id--可以根据条件筛选where  k.type_id <> 'xunjianxiang')select atid,name from cte ;create or replace function func_get_loginUnit(in_userid varchar(200),out o_area text)
returns text as $$
DECLARE
 v_rec_record RECORD;
BEGIN
 o_area = '';
 FOR v_rec_record IN (WITH RECURSIVE r AS (
 SELECT userid,usertitle,userparentid,userwbscode FROM ta_users WHERE userid = in_userid
 union ALL
 SELECT ta_users.userid,ta_users.usertitle,ta_users.userparentid,ta_users.userwbscode FROM ta_users, r 
	WHERE ta_users.userid = r.userparentid and ta_users.userparentid <> '-1'
 )
SELECT userid,concat_ws(' > ',usertitle,'') as name,userparentid,userwbscode FROM r order by userwbscode asc) LOOP
 o_area := o_area || v_rec_record.name;
 
 END LOOP;
 return;
END;
$$
language plpgsql;

根据父级查询子级

with recursive cte as
(
select a.atid,cast(a.name as varchar(200)) from app_aqxj_equipment a 
where a.atid='036bc777-c718-4a81-bbc7-b8fe6f8ce913'
union all 
select k.atid,cast(c.name||'>'||k.name as varchar(200)) as name  from app_aqxj_equipment k 
inner join cte c on c.atid = k.parent_id
where  k.type_id <> 'xunjianxiang'
)select atid,name from cte

WITH RECURSIVE subject_tree AS (
    -- 基础查询，选择父级记录
    SELECT plan_subject_id, parent_id, template_content
    FROM train_plan_subject
    WHERE plan_subject_id = 'e7bfe6ce837e4ee58e5642ab232df906'
  UNION ALL
    -- 递归查询，查找当前结果的子级
    SELECT t.plan_subject_id, t.parent_id, t.template_content
    FROM train_plan_subject t
    INNER JOIN subject_tree st ON t.parent_id = st.plan_subject_id
)
SELECT * FROM subject_tree;

根据子级查询父级

WITH RECURSIVE dict AS (
     SELECT *
     FROM app_aqxj_equipment
     WHERE atid= '036bc777-c718-4a81-bbc7-b8fe6f8ce913'
     union ALL
     SELECT app_aqxj_equipment.*
     FROM app_aqxj_equipment,
          dict
     WHERE app_aqxj_equipment.atid = dict.parent_id
)
SELECT atid AS id, name as name, parent_id as parentId
FROM dict
ORDER BY name

从父级到子级

WITH RECURSIVE dict AS (
     SELECT *
     FROM app_aqxj_equipment
     WHERE atid= '036bc777-c718-4a81-bbc7-b8fe6f8ce913'
     union ALL
     SELECT app_aqxj_equipment.*
     FROM app_aqxj_equipment,
          dict
     WHERE app_aqxj_equipment.parent_id = dict.atid
)
SELECT atid AS id, name as name, parent_id as parentId
FROM dict
ORDER BY name

方法函数

SUBSTRING('abcd',2); -- result:bcd 表示从下标从2开始截取到末尾
SUBSTRING('abcd',1,2); -- result:ab 表示从下标从1开始,截取2个字符
to_char(date_trunc('day',now()),'yyyy-mm-dd') --时间格式转换为字符日期类型
to_date('2020-09-09','yyyy-mm-dd')) --把字符转换为日期格式 没有时分秒
to_timestamp('2020-09-09 22:22:00','yyyy-MM-dd hh24:mi:ss') --有时分秒
to_number(12,'99') --把字符转为数值型
split_part('2020-09-08','-',2) --分割字符，获取的是月，也就是09
concat_ws(':','1','2') --查询字段拼接 结果：1:2
string_agg(name,','); --多行合成一列，例如：张三，李四，王五，赵六
 
regexp_split_to_array('1,2,3',',') --返回数组格式
regexp_split_to_table('1,2,3',',') --拆分返回行模式
 
select case when 2>1 then '大于' else '小于' end --if判断
 
|| --字符拼接符
 
--其他类型格式转换
'1'::varchar
'1'::interger
'2020-01-01'::date
 
--当所有数据id全部一致时，没有唯一标识，使用下面这个函数可以查看隐藏的id
select ctid,* from wx_info limit 5
delete from wx_info where ctid in (select min(ctid) from wx_info where id = 1);
 
 
 
--查询行号
select 
row_number() over(order by recid) row_number
from jhrwbzs

创建游标

实用代码

实现夸库查询

--创建扩展
create extension
dblink;--dblink函数实现夸库查询
 
select * from dblink(
	'host=127.0.0.1 dbname=数据库名字 user=postgres password=postgres',
	'select atid,name from app_aqxj_place_info') as t
	(atid varchar(200),name varchar(200));

数学函数

字符串函数和操作符

类型转换相关函数

三角函数列表

函数详细：https://www.runoob.com/postgresql/postgresql-functions.html

这是 父级查询所有子级的！！！

with recursive cte as
(
select a.atid,cast(a.name as varchar(200)) from app_aqxj_equipment a
where a.atid=’239972297dd742e28043bfdfa9eef264′
union all
select k.atid,cast(c.name||’>’||k.name as varchar(200)) as name from app_aqxj_equipment k
inner join cte c on c.atid = k.parent_id
where k.type_id <> ‘xunjianxiang’
)select atid,name from cte