PostgreSQL常用函数

1，

with t as (select poi.mid poi_id, link.link_id link_id from   poi, nav_link_graphy link where poi.kind not like ‘%BF00%’ and   poi.kind not like ‘%BB86%’ and   poi.kind not like ‘%2F0105%’ and   poi.kind not like ‘%8401%’ and   poi.kind not like ‘%8085%’
and
   st_dwithin(poi.geometry::geography, link.geom, 3.0))
select ‘poi’ tn, t.poi_id fid, string_agg(t.link_id::varchar, ‘|’) r__agg__fid, ‘nav_link’ r__agg__tn   from t group by t.poi_id;

注：string_agg聚合函数，可以放在select之后，且语句中有groupby分组，对于多行多个要素之间存在关系,需要一起报出时

又如：

select n1.name_id,string_agg(n2.name_id,’|’) r_agg_fid, ‘nav_name’ as r_agg_tn from nav_namen1,nav_name n2 where n1.name_py=n2.name_py and

n1.name_ch<>n2.name_chand n1.ctid<n2.ctid group by n1.name_id

 

2，

SELECT ‘NAV_LINK’ as tn,nav_link.link_id as fid,’sp_class取值错误’ err_msg from nav_link WHERE sp_class is not null and sp_class <>” and substring(sp_class,1,1) not similar to ‘[012345678]’
UNION ALL
SELECT ‘NAV_LINK’ as tn,nav_link.link_id as fid,’f_speed取值错误’ err_msg from nav_link WHERE f_speed is not null and f_speed !=” and f_speed not similar to ‘(\s|[0-9]*),(\s|0|1|2|3|4|5),(\s|0|1)’
UNION ALL
SELECT ‘NAV_LINK’ as tn,nav_link.link_id as fid,’t_speed取值错误’ err_msg from nav_link WHERE t_speed is not null and t_speed !=” and t_speed not similar to ‘(\s|[0-9]*),(\s|0|1|2|3|4|5),(\s|0|1)’

3，

WITH t1 as(SELECT s_nid as nid from nav_link UNION ALL SELECT e_nid as nid FROM nav_link),
t2 as (SELECT nid from t1 GROUP BY nid HAVING “count”(1)=1)
SELECT ‘NAV_LINK’ as tn,nav_link.link_id as fid from nav_link WHERE nav_link.s_nid in (SELECT nid from t2) AND
nav_link.e_nid in (SELECT nid from t2)

 

4，

SELECT ‘NAV_BRANCH’ as tn,branch_id as fid from NAV_BRANCH WHERE not EXISTS(
SELECT 1 from nav_link WHERE nav_link.link_id = nav_branch.in_linkid)

 

5，

SELECT link_id from nav_link WHERE string_to_array(nav_link.form,’;’)@>array[’50’]

注：@>array[’50’]表示包含

 

6，

SELECT ‘NAV_LINK’ as tn,link_id as fid FROM NAV_LINK
WHERE string_to_array(form,’;’)&&array[‘0′,’1′,’2′,’10’,’11’,’12’,’13’,’14’,’15’,’16’,’17’,’18’,’20’,’21’,’22’,’23’,’24’,’30’,’31’,’32’,’33’,’34’,’35’,’36’,’37’,’38’,’39’,’50’,’51’,’52’,’54’,’60’,’80’]=false

注：&&表示有交集.

例如：@>array[’33’,’50’]  —这个是同时包含两个才返回true
string_to_array(form,’;’)&&array[’33’,’50’]   –&&是包含其中一个就满足
注意他们的反面：@>array[’33’,’50’]=false，这表示不包含其中一个就返回false，而&&array[’33’,’50’]=false，这表示同时不包含两个

7，

SELECT ‘NAV_REALIMAGE’ as tn,REAL_ID as fid,’image_type取值错误’ err_msg from NAV_REALIMAGE where image_type::VARCHAR not SIMILAR TO ‘[01]’

8，

SELECT ‘NAV_REALIMAGE’ as tn,REAL_ID as fid from NAV_REALIMAGE where arr_code is not null and arr_code!=” and real_code is not null and real_code !=”
and (“substring”(arr_code from 2 for “char_length”(arr_code)-1) != “substring”(real_code from 2 for “char_length”(real_code))-1)

注：substring(field,start,length) ，field为字段，start为开始点（从1开始），length为长度

 

9，

selectst_astext(st_pointN(geom,5)) as a_location from nav_link

注：请转为wkt字符串,比如某个link的第5个形状点处存在折角较小,比较尖,一般在此处会创建一个点几何

 

 10，

“该连接点驶出的道路至少有条LinkID在NAV_SLOPE表存在，否则报错。”

with a as(SELECT ns.node_id nid, “string_agg”(sln.link_id::VARCHAR,’|’) lidfrom nav_slope ns,shd_link_node sln WHERE ns.node_id = sln.node_id and ns.link_id <>sln.link_id GROUP BY ns.node_id )
,b as(SELECT a.nid nid,regexp_split_to_table(a.lid,’\|’)rid from a )
SELECT ‘nav_slope’ tn, a.nid fid, a.lid as m__1__link_id from b,a,nav_slope nsp WHERE a.nid=b.nid and b.rid=nsp.link_id GROUP BY a.nid,a.lid HAVING “count”(1)=0

“string_agg”先聚合，再使用regexp_split_to_table将聚合在一起使用‘|’分隔的一条记录，拆分成多条记录存于子表中，也可以使用regexp_split_to_array函数拆分成数组，array_length求得数组长度

 

11，

select * from a where a.nid>100 or a.lid<10000   ；       select * from a,b where a.nid>b.nid  union  select * from a,b where a.lid< b.lid

or单表内使用时效率还好，多表连接时使用union替换

12，

select f1,f2,… ,分析函数（例如row_num(),count(1)）over(partition by f3 oder by f4)  from table;

与group by 区别：1，select f1,f2,… ,后面字段不限制   2，count计数结果在一组内的每一条记录均有一个值，而group by会合成一条记录

例句：with a as (
select DETAIL_ID,group_id,link_id,count(1)over(partition by p.DETAIL_ID,p.group_id order by SEQ_NUM) m_seq,
    row_number()over(partition by p.DETAIL_ID,p.group_id order by SEQ_NUM) seq from  NAV_RESTRICTION_PASS p ),
link as (
select t1.DETAIL_ID, t1.link_id l1,t2.link_id l2 from a t1, a t2 where t1. DETAIL_ID=t2.DETAIL_ID and t1.group_id=t2.group_id and t1.seq+1=t2.seq
union all
select a.detail_id, a.link_id l1, d.out_linkid l2 from NAV_RESTRICTION_DETAIL d, a where d.detail_id=a.detail_id and a.seq=a.m_seq
union all
select a.detail_id, r.in_linkid l1, a.link_id  l2 from NAV_RESTRICTION r,NAV_RESTRICTION_DETAIL d, a where r.RESTRIC_ID=d.RESTRIC_ID and d.detail_id=a.detail_id and a.seq=1)
select l.detail_id, l.l1,l.l2 from link l where not exists (select 1 from shd_link_go_link s where l.l1=s.in_link_id and l.l2=s.out_link_id)

13,

如果一条弧段上制作了坡度信息，则该点连接的其他弧段中至少有一条link上也应该存在坡度信息，否则报log

 with b as(SELECT sln2.node_id,sln2.link_id,ns.slope_id fid from nav_slope ns,shd_link_node sln,shd_link_node sln2
WHERE ns.link_id=sln.link_id and ns.node_id= sln.node_id and sln2.node_id=sln.node_id and sln2.link_id<>sln.link_id),
c as(SELECT b.fid,string_agg(DISTINCT b.link_id,’|’) link_id,string_agg(DISTINCT b.node_id,’|’) node_id FROM b GROUP BY b.fid)
(
SELECT ‘nav_slope’ tn,c.fid fid from c
EXCEPT
SELECT ‘nav_slope’ tn,c.fid fid FROM c WHERE EXISTs(SELECT 1 from nav_slope WHERE string_to_array(c.link_id,’|’)@>string_to_array(nav_slope.link_id,’ ‘))
)

注意：至少的表达，先求出包含坡度信息的表，然后求差集即错误的

 

14,查找NAV_LINK_LIMIT中TYPE=4的道路两端连接的弧段，如果一个端点连接的弧段只能进入当前节点或只能退出当前节点，则该点连接道路的TYPE！=4则报错

with sg as ( select ln.link_id, node_id from shd_link_node ln, NAV_LINK_LIMIT ll where ln.link_id=ll.link_id and ll.type=4 ),
 tp as (
 select
 case when dir=2 and s_e=1 then 0
      when dir=2 and s_e=0 then 1
      when dir=3 and s_e=1 then 1
      when dir=3 and s_e=0 then 0
      when dir=1 then 2
  end mark, ln.link_id,ln.node_id, count(1)over(partition by ln.node_id) gc
  from shd_link_node ln  where exists (select 1 from sg where ln.node_id=sg.node_id and ln.link_id<>sg.link_id))
,fx as ( select link_id, node_id, mark, count(1)over(partition by node_id, mark) mc, gc  from tp )
select ‘nav_link’ tn,link_id fid from fx where mc=gc and mark in (0,1)

 

15,

获取两个NODE的坐标，如果两个点(图廓点除外)的距离小于3则报错。程序检查时不检查的情况有： 1、排除当立交点之间都是Node点的情况。 4、排除图幅接边的图廓点

with t as (select z1.link_id lid1, z1.start_end se1, z2.link_id lid2, z2.start_end se2 from NAV_ZLEVEL_LINK z1, NAV_ZLEVEL_LINK z2
 where z1.start_end>0 and z2.start_end>0  and z1.table_name=’NAV_LINK’ and z2.table_name=z1.table_name  and z1.zlevel_id=z2.zlevel_id),
n as (select case t.se1 when 1 then l1.s_nid else l1.e_nid end nd1,
 case t.se2 when 1 then l2.s_nid else l2.e_nid  end nd2  from nav_link l1, t, nav_link l2
where t.lid1=l1.link_id and t.lid2=l2.link_id),
sn as (select case when nd1>nd2 then nd1 else nd2 end id1, case when nd1>nd2 then nd2 else nd1 end id2 from n )
SELECT ‘nav_node’ tn,a.node_id as fid,’nav_node’ as r__1__tn,b.node_id  as r__1__fid   from SHD_NODE_GRAPHY a,SHD_NODE_GRAPHY b  
WHERE st_dwithin(a.geom,b.geom,3)   and a.node_id>b.node_id  and a.adjoin_nid<>b.node_id 
and not exists(select 1 from sn where a.node_id=sn.id1 and b.node_id=sn.id2);

 

16，

pg存储过程输出（调试）语句,RAISE NOTICE

CREATE FUNCTION somefunc() RETURNS integer AS $$
    DECLARE
       quantity integer := 30;
    BEGIN
       RAISE NOTICE ‘Quantity here is %’, quantity;      –在这里的数量是30
       quantity := 50;
       —
       — 创建一个子块
       — http://www.qinglvfenzu.com

       DECLARE
          quantity integer := 80;
       BEGIN
          RAISE NOTICE ‘Quantity here is %’, quantity;   –在这里的数量是80
       END;
       RAISE NOTICE ‘Quantity here is %’, quantity;      –在这里的数量是50   
       RETURN quantity;
    END;
    $$ LANGUAGE plpgsql;
    #执行该函数以进一步观察其执行的结果。
    postgres=# select somefunc();
    NOTICE:  Quantity here is 30
    NOTICE:  Quantity here is 80
    NOTICE:  Quantity here is 50
     somefunc
    ———-
           50
    (1 row)

转载自：https://blog.csdn.net/shixiaoguo90/article/details/37594663