常用的pg的sql

日常积累的一些pg数据库的操作函数。自己留着，也给各位giser分享一下吧。
————————————————————————————————————
1.整库所有的字段获取
————————————————————————————————————
SELECT col_description(a.attrelid,a.attnum) as comment,format_type(a.atttypid,a.atttypmod) as type,a.attname as name, a.attnotnull as notnull ,c.relname  
FROM pg_class as c,pg_attribute as a where a.attrelid = c.oid and a.attnum>0 and relname like '%doc%'

————————————————————————————————————
2.修改mutilinestring to simaplelinstring
————————————————————————————————————
UPDATE tablename set geom=st_linemerge(geom);

——————————————————————

3.pg查询整库数据量大小
————————————————————————————————————

SELECT 
table_schema || '.' || table_name 
AS table_full_name, pg_size_pretty(pg_total_relation_size('"' ||table_schema || '"."' || table_name || '"')) AS size
FROM 
information_schema.tables
ORDER BY
    pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')
DESC

————————————————————————————————
4.sql查重去重
————————————————————————————
select ctid, * from "i_szpipeline_ln_341300_2016" 

select distinct uid,  count(*) , * from "i_szpipeline_ln_341300_2016"  group by uid having count(*) > 1;

select ctid, * from "i_szpipeline_ln_341300_2016"  where ctid in (select min(ctid) from "i_szpipeline_ln_341300_2016" group by uid);

delete from "i_szpipeline_ln_341300_2016" where ctid not in (select min(ctid) from "i_szpipeline_ln_341300_2016" group by objectid);

——————————————————————————————————
5.计算三d空间位置
————————————————————————————————————————
ST_3DMakeBox
ST_3DIntersects
postgis函数查询位置：
https://www.cnblogs.com/lilei2blog/p/7815253.html
http://www.postgis.net/docs/manual-2.5/PostGIS_Special_Functions_Index.html#NewFunctions_2_5

——————————————————————————————————————————

6.起点埋深范围存储过程
——————————————————————————————————————————
--drop function scope(int[],text)
CREATE FUNCTION gc_stasticsdeep(
arr int[],
cityname text)
RETURNS table( slen double precision)AS $$
DECLARE
  x int[];
  i int;
  sql text;
BEGIN

foreach x SLICE 1 IN ARRAY $1
  LOOP
    RAISE NOTICE 'row = %', x;
    RAISE NOTICE 'row = %', x[1];
    RAISE NOTICE 'row = %', x[2];
    sql:='select sum(len) from '||cityname||'.tablename where s_deep in ('||x[1]||','||x[2]||');';
     RAISE NOTICE 'sql= %', sql;
    return query execute sql;
  end loop ;
END;
$$ LANGUAGE plpgsql;

SELECT gc_stasticsdeep(ARRAY[[1,2],[2,3],[3,4]],'suzhou');

————————————————————————————————————————————
7.sql存储过程，给定一个城市相同管类，相同管径的列出来_________________________________________________________________________________________________
DROP FUNCTION public.gc_plsizequery(text);

CREATE OR REPLACE FUNCTION public.gc_plsizequery(
     cityname text,
     pipelinekind text,
     pipelinesize text
      )
 RETURNS TABLE(id character varying, belong character varying, roadname character varying,mat character varying, ds character varying, sdeep double precision, edeep double precision, length double precision, sx double precision, sy double precision, ex double precision, ey double precision) AS
$BODY$
declare 
sql varchar;  
BEGIN
sql :='select  uid,b_code,road,material,d_s,s_deep,e_deep,len, st_x(st_startpoint(geom)),st_y(st_startpoint(geom)),st_x(st_endpoint(geom)),st_y(st_endpoint(geom))  from '||cityname||'.i_szpipeline_ln_341300_2016 where pltype='''||pipelinekind||''' and d_s='''||pipelinesize||''';';
return query EXECUTE sql; 
    
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION public.gc_plsizequery(text,text,text)
  OWNER TO postgres;

————————————————————————————————————————
8.sql存储过程  按城市   分组统计管线总长度
——————————————————————————————
DROP FUNCTION public.gc_stasticslength(text);

CREATE OR REPLACE FUNCTION public.gc_stasticslength(
     cityname text  )
  RETURNS TABLE(plkind character varying, sumlength double precision) AS
$BODY$
declare 
sql varchar;  
BEGIN
sql :='select pltype, sum(len) from '||cityname||'.i_szpipeline_ln_341300_2016  group by pltype';
return query EXECUTE sql; 
    
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION public.gc_stasticslength(text)
  OWNER TO postgres;

  select * from public.gc_stasticslength('suzhou')
————————————————————————————————————————————————————
9.sql存储过程 按城市查找一个uid
————————————————————————————————————————————————

-- Function: public.gc_ods_queryid(text, text)

-- DROP FUNCTION public.gc_ods_queryid(text, text);

CREATE OR REPLACE FUNCTION public.gc_ods_queryid(
    IN cityname text,
    IN uuid text)
  RETURNS TABLE(id character varying, belong character varying, roadname character varying, plkind character varying, mat character varying, ds character varying, sdeep double precision, edeep double precision, length double precision, sx double precision, sy double precision, ex double precision, ey double precision) AS
$BODY$
BEGIN
  RETURN QUERY SELECT uid,b_code,road,pltype,material,d_s,s_deep,e_deep,len, st_x(st_startpoint(geom)),st_y(st_startpoint(geom)),st_x(st_endpoint(geom)),st_y(st_endpoint(geom)) from suzhou.i_szpipeline_ln_341300_2016  where uid=uuid ;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION public.gc_ods_queryid(text, text)
  OWNER TO postgres;

——————————————————————————————————————————————————
10.sql时间
——————————
UPDATE i_dl_gd_pt_341300_2016 set dupdate=now();
show time ZONE
set time ZONE 'PRC'
————————————————————
11.linux 常用目录的作用和存放的内容：
————————————————————————————
/bin 存放使用者最长用的命令，如：cp、ls、cat，等等。
/boot 启动linux时使用的一些核心文件。
/dev 是device(设备)的缩写，这个目录下是所有linux的外围设备。
/etc 这个目录用来存放系统管理所需要的配置文件和子目录。
/home 用户的主目录，比如说有个用户叫wang，那他的目录就是/home/wang也可以用~wang来表示。
/lib 这个目录是存放着系统最基本的动态连接库，几乎所有的应用程序都须用这些共享库。
/lost+found 这个目录平时是空的，当系统不正常关机后，这里就是一些无家可归文件的避难所。
/mnt 这个目录是空的，系统提供这个目录是让用户临时挂接别的文件系统。
/proc 这个目录是一个虚拟目录，它是系统内存映射，我们可以直接通过访问这个目录来获取系统信息。也就是说，这个目录的内容不在硬盘上而是在内存中。
/root 系统管理员（root）的主目录，作为系统的拥有者的特权。
/sbin s就是super user的意义，也就是说这里存放的是系统管理员使用的管理程序。
/tmp 这个目录是存放一些临时文件的地方。
/usr 我们用到的应用程序的文件几乎都存放这个目录下：/usr/X11R6存放X_Window的目录；/usr/bin存放着许多应用程序；/usr /sbin给超级用户使用的一些管理程序就放在这个里面；/usr/include开发和编译应用程序所需的头文件；/usr/lib存放一些常用的动态连接共享库和静态归档案库；/usr/local这是提供给一般用户的/usr目录，在这里安装软件最合适。/usr/man存放帮助文档。/usr /src开放的源代码就存在这个目录下。
/var 这个目录存放那些不断扩充的东西，为了保持usr的相对稳定，那些才、经常被修改的目录可以放在这个目录下，如/var/log日志文件。

————————————————————————
12常用st函数
———————————————————————————————— 
select ST_AsEWKT(geom) from admin.dkfg_2019320_399;
  select st_x(geom) from admin.dkfg_2019320_399;
    select st_y(geom) from admin.dkfg_2019320_399;
——————————————————————————————————————————————

13row_to_json() 函数
————————————————————————————————————
一个简单的用法就是使用 row_to_json() 函数，它接受 “行值”并返回 JSON 对象：

1https://www.cnblogs.com/my4piano/p/5658264.html
select row_to_json(tableName) from tableName;

14

https://www.cnblogs.com/alianbog/p/5628543.html
GiST的意思是通用的搜索树(Generalized Search Tree)

15添加pg扩展

create EXTENSION postgis扩展postgis的数据库

16创建几何类型为面的函数

create table public.bbox(name text,chinesename text, geo geometry(Polygon))

 

16. 选择public架构下的所有表，select tablename from pg_tables where schemaname='public'
17.更改表字段名alter table tset rename column no to name
18.更改表名alter public.table rename to table

19.COALESCE是一个函数， (expression_1, expression_2, ...,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。使用COALESCE在于大部分包含空值的表达式最终将返回空值。

20.unnest(array[10,20])   10  20

21.杂七杂八
alter table rename to tablename||'l' where tablename like '%line';
select tablename from pg_tables where schemaname='public' and tablename like '%line';
select  substring(tablename from 0 for char_length(tablename)-2) from pg_tables where schemaname='public' and tablename like '%line';
select * from pg_tables

22
-- 批量删除表, schema: cat_fish
CREATE OR REPLACE FUNCTION batch_del_tables()
RETURNS int AS
$$
DECLARE
    r RECORD;
    count int;
BEGIN
    count := 0;
-- 创建 表
FOR r IN SELECT tablename FROM pg_tables where tablename like 'os_%' LOOP
    RAISE NOTICE 'tablename: %', r.tablename;
    EXECUTE 'DROP TABLE cat_fish.' || r.tablename || ' CASCADE';
    count := count + 1;
END LOOP;

RETURN count;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;

-- SELECT batch_del_tables();

10批量删除sequence

create table test(sql text)
insert into public.test (sql) select  string_agg(relname,',') from pg_class where relnamespace='115117' and relname like '%seq'

insert into test(sql)
select string_agg( relname,',' )from pg_class where relnamespace='2200' and relname like 'bt_%'

23计算表student_info的列数

————————————————
information_schema.COLUMNS
“列”信息啊。

select count(*) from information_schema.COLUMNS where table_name='student_info'
意思就是计算表student_info的列数
24——————————————————————————
返回表达式中第一个非空表达式，如有以下语句：
SELECT COALESCE(NULL,NULL,3,4,5) FROM dual
其返回结果为：3

25————————————————————————
select c.database_schema,coalesce(b.belong_plkind,b.plkind_code) from      pipeline_meta_config.pipeline_kinds b,
    pipeline_meta_config.pipeline_workarea c

返回结果：
"xicheng20km";"DC"
"kunminmg";"DC"
 

26管点生成管线st_makeline

——————————————————————————————————————
select st_makeline(ps1.geom ,pe1.geom) ,st_makeline(ps2.geom ,pe2.geom )from baotou.bt_zhp ps1 ,  baotou.bt_zhp pe1 , baotou.bt_zhp ps2 ,  baotou.bt_zhp pe2 ,baotou.bt_zhl l where l.s_point1=ps1.exp_no and l.s_point2=ps2.exp_no and l.e_point1=pe1.exp_no and l.e_point2=pe2.exp_no