Postgres常用SQL和方法封装_postgresql封装

一、递归

1.建表和添加数据

-- ----------------------------
-- Table structure for tbl_cyc
-- ----------------------------
DROP TABLE IF EXISTS "public"."tbl_cyc";
CREATE TABLE "public"."tbl_cyc" (
  "id" int4 NOT NULL,
  "code" int2,
  "parent" int2,
  "description" varchar(32) COLLATE "pg_catalog"."default"
)
;

-- ----------------------------
-- Records of tbl_cyc
-- ----------------------------
INSERT INTO "public"."tbl_cyc" VALUES (1, 0, -1, '中国');
INSERT INTO "public"."tbl_cyc" VALUES (2, 1, 0, '北京');
INSERT INTO "public"."tbl_cyc" VALUES (3, 2, 0, '天津');
INSERT INTO "public"."tbl_cyc" VALUES (4, 35, 1, '天安门');
INSERT INTO "public"."tbl_cyc" VALUES (5, 36, 1, '万里长城');
INSERT INTO "public"."tbl_cyc" VALUES (6, 38, 2, '南开区');
INSERT INTO "public"."tbl_cyc" VALUES (7, 39, 2, '西青区');
INSERT INTO "public"."tbl_cyc" VALUES (9, 1002, 39, '天津师范大学');
INSERT INTO "public"."tbl_cyc" VALUES (8, 1001, 38, '南开大学');

-- ----------------------------
-- Primary Key structure for table tbl_cyc
-- ----------------------------
ALTER TABLE "public"."tbl_cyc" ADD CONSTRAINT "tbl_cyc_pkey" PRIMARY KEY ("id");
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

2.递归查询 （通过父节点查询所有子节点）

WITH RECURSIVE C (code,parent,description)
AS (
SELECT code,parent,description FROM tbl_cyc
WHERE parent = 2
UNION DISTINCT
SELECT T.code,T.parent,T.description FROM tbl_cyc T
JOIN C ON
T.parent = C.code
)
SELECT code,description FROM C
1
2
3
4
5
6
7
8
9
10

3.递归查询 （通过子节点查询所有父节点）

WITH RECURSIVE C (code,parent,description)
AS (
SELECT code,parent,description FROM tbl_cyc
WHERE code = 1001
UNION DISTINCT
SELECT T.code,T.parent,T.description FROM tbl_cyc T
JOIN C ON
T.code = C.parent
)
SELECT code,description FROM C
1
2
3
4
5
6
7
8
9
10

4.结果查看

1.递归父查子

2.递归子查父

二、创建索引方法

1.函数

----------------------------------------------------------
---Create Index Manage Function 2021-08-10 ---------------
---indexnames    索引名
---tablename     表名
---columnname    字段名
---operationtype 操作：create 新建 delete 删除
CREATE OR REPLACE FUNCTION "public"."index_manage" ( "indexnames" VARCHAR, "tablename" VARCHAR, "columnname" VARCHAR, "operationtype" VARCHAR ) RETURNS "pg_catalog"."void" AS $BODY$ 
 DECLARE
 strSQL TEXT;----定义SQL语句拼接变量
BEGIN
 IF ( operationtype = 'create' ) THEN
    raise notice'Param:%',operationtype;
  IF ( SELECT COUNT ( 0 ) FROM pg_indexes T WHERE T.indexname = indexnames ) = 0 THEN
    raise notice'Param:%',tablename;
      strSQL := 'CREATE INDEX IF NOT EXISTS '||indexnames||' ON '|| tablename || '('||columnname||');';
   EXECUTE strSQL;
  ELSE
    strSQL := 'REINDEX INDEX ' || indexnames;
          EXECUTE strSQL;
  END IF;
 ELSIF ( operationtype = 'delete' ) THEN
  IF ( SELECT COUNT ( 0 ) FROM pg_indexes T WHERE T.indexname = indexnames ) = 1 THEN
    raise notice'Param:%',operationtype;
   strSQL := 'DROP INDEX '||indexnames;
   EXECUTE strSQL;
  END IF;
  END IF;
 
END 
$BODY$ 
LANGUAGE plpgsql VOLATILE COST 100;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

2.使用

SELECT index_manage('tbl_cyc','tbl_tfa_static_hour','description','create');
1

3.查看索引是否创建成功

select * from pg_indexes where tablename = 'tbl_cyc';
1

三、表插入字段方法

1.函数

-----------------------------------------------------------
---Create Add New Column Function 2021-08-09 --------------
---tablename        表名
---columnname       字段名
---fieldtype        类型，varchar(255) int int8
---fielddescription 字段描述
---updatesql        新增字段默认值
---Add From Version 5211 ----------------------------------
CREATE OR REPLACE FUNCTION "public"."add_new_cloumn"("tablename" varchar, "columnname" varchar, "fieldtype" varchar, "fielddescription" varchar, "updatesql" varchar)
  RETURNS "pg_catalog"."void" AS $BODY$
 DECLARE
    ifExist   INT;  ----表字段,是否已存在
   strSQL    TEXT;      ----定义SQL语句拼接变量
BEGIN
 ---strSQL := 'SELECT COUNT ( 0 ) FROM information_schema.COLUMNS T WHERE T.table_name = '''||tablename||''' AND T.column_name ='''||columnname||'''';
 ---EXECUTE strSQL
 SELECT COUNT ( 0 ) INTO ifExist FROM information_schema.COLUMNS T WHERE T.table_name = ''||tablename||'' AND T.column_name = ''||columnname||'' ;
 ---raise notice 'Param:%',ifExist;
 IF
  ( ifExist = 0 ) THEN
   strSQL := 'ALTER TABLE ' ||tablename|| ' ADD COLUMN ' || columnname || ' ' || fieldtype ||' ;';
    EXECUTE strSQL;
   strSQL := 'COMMENT ON COLUMN "public"."'||tablename||'"."'|| columnName ||'" IS '''||fielddescription||'''';
   EXECUTE strSQL;
   strSQL := 'update ' || tablename || ' set ' || columnName || ' = ' || updatesql || ';';
   EXECUTE strSQL;
 END IF;
 
END 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

2.使用

SELECT add_new_cloumn('tbl_cyc','create_date','varchar(255)','创建时间','''2022-03-21''');
1

3.结果

四、多语句更新返回条数方法

1.函数

--
-- Name: update_count(character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE OR REPLACE FUNCTION public.update_count(updatesql character varying)
  RETURNS integer
  LANGUAGE plpgsql
AS $$
declare
  rowcnt integer := 0;
  allcnt integer := 0;
  strSQL text;         ----定义SQL语句拼接变量
  strRep text;
  updatearr text[];
begin
  -- RAISE NOTICE 'Counter: %', 'start update';
  -- 字符串转数组
  strSQL := 'select string_to_array(''' || updatesql || ''',' || ''';''' || ')';
  EXECUTE strSQL into updatearr;
  -- 获取长度
  -- RAISE NOTICE 'SQL: %', array_length(updatearr,1);

  -- 循环执行update 并累计结果
  <<label1>> foreach strSQL in array updatearr
    loop
      strSQL := 'SELECT replace(''' || strSQL || ''', '|| '''^''' || ',''' || '''''' || ''')';
      RAISE NOTICE 'SQL_UPDATE: %', strSQL;
      execute strSQL into strRep;
      EXECUTE strRep;
      get diagnostics rowcnt := row_count;
      allcnt := (allcnt + rowcnt);
    end loop label1;
  -- 返回结果
  return allcnt;
END;
$$
;
alter function public.update_count(varchar) owner to postgres;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

2.使用

SELECT update_count('UPDATE tbl_cyc set create_date = ^a^ where id = 2;UPDATE tbl_cyc set create_date = ^b^ where id = 10');
1

3.结果：实际只更新一条