Postgres常用SQL和方法封装

太过爱你忘了你带给我的痛 2023-09-30 10:16 10阅读 0赞

### Postgres常用SQL ###

*  一、递归
 *   *  1.建表和添加数据
     *  2.递归查询 （通过父节点查询所有子节点）
     *  3.递归查询 （通过子节点查询所有父节点）
     *  4.结果查看
 *  二、创建索引方法
 *  三、表插入字段方法
 *  四、多语句更新返回条数方法

# 一、递归 #

## 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");

## 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

## 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

## 4.结果查看 ##

1.递归父查子  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54yq5oKf6YGT_size_20_color_FFFFFF_t_70_g_se_x_16]  
2.递归子查父  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54yq5oKf6YGT_size_20_color_FFFFFF_t_70_g_se_x_16 1]

# 二、创建索引方法 #

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;

2.使用

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

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

select * from pg_indexes where tablename = 'tbl_cyc';

# 三、表插入字段方法 #

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;

2.使用

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

3.结果  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54yq5oKf6YGT_size_20_color_FFFFFF_t_70_g_se_x_16 2]

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

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;

2.使用

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

3.结果：实际只更新一条  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54yq5oKf6YGT_size_20_color_FFFFFF_t_70_g_se_x_16 3]

[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54yq5oKf6YGT_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/6406e527a5814b0b89ee2eb9a407f8b2.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54yq5oKf6YGT,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54yq5oKf6YGT_size_20_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/ba5d5dcb1c7241af833a536c305f51ca.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54yq5oKf6YGT,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54yq5oKf6YGT_size_20_color_FFFFFF_t_70_g_se_x_16 2]: https://img-blog.csdnimg.cn/55b172fdf7204161aa9667b9feddf646.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54yq5oKf6YGT,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54yq5oKf6YGT_size_20_color_FFFFFF_t_70_g_se_x_16 3]: https://img-blog.csdnimg.cn/f82d911d0233486db2228702e827caea.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54yq5oKf6YGT,size_20,color_FFFFFF,t_70,g_se,x_16