PostgreSQL常用函数/语句整理（系统&自定义）

原创

哥们要飞 2024-02-03 22:20:13 ©著作权

©著作权归作者所有：来自51CTO博客作者哥们要飞的原创作品，请联系作者获取转载授权，否则将追究法律责任

〇、相关资料

一、系统函数/语句

1、死锁及会话join查询

SELECT * FROM pg_locks pl 
LEFT JOIN pg_stat_activity psa
ON pl.pid = psa.pid;

1.2 类型转换

1、结果转json数组

参考地址：pgsql 查询结果转换为json数组 - hziwei - 博客园 (cnblogs.com)

select array_to_json(array_agg(row_to_json(t))) 
from (SELECT * FROM test) t

二、自定义函数

2.1 雪花算法

CREATE OR REPLACE FUNCTION "tp"."snow_next_id"(OUT "result" int8)
  RETURNS "pg_catalog"."int8" AS $BODY$
DECLARE
our_epoch bigint := 1314220021721;
seq_id bigint;
now_millis bigint;
shard_id int := 5;
BEGIN
seq_id := nextval('table_id_seq') % 16384;
SELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000) INTO now_millis;
result := (now_millis - our_epoch) << 23;
result := result | (shard_id << 10);
result := result | (seq_id);
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

1.2 创建分区

CREATE OR REPLACE FUNCTION "ods"."createpartitionifnotexists"("tb_name" varchar, "partiton_val" varchar)
  RETURNS "pg_catalog"."void" AS $BODY$
  DECLARE master_name TEXT := tb_name; -- 创建分区表的表名
  DECLARE partition_name TEXT := tb_name || '_' || partiton_val; -- 分区的表名称

  BEGIN
    -- 判断分区名称是否存在，不存在时才需要创建
    IF to_regclass (partition_name) IS NULL THEN
      -- 执行创建分区
      EXECUTE format (
          'create table %L partition of %L for values in (%s)',
          partition_name,
          master_name,
          partiton_val
      );
    END IF;
  END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

1.3 字符串去空格

CREATE OR REPLACE FUNCTION "ods"."code_strip_char"("code" varchar)
  RETURNS "pg_catalog"."varchar" AS $BODY$
declare
    DECLARE
    code_strip VARCHAR = regexp_replace(code, '\D', '', 'g');
BEGIN
    IF code_strip is not null and code_strip != '' THEN
        RETURN code_strip;
    ELSE
        RETURN NULL;
    end if;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

1.4 执行SQL

CREATE OR REPLACE FUNCTION "base"."supcon_rule_call"("rule_id" int8, "rule_description" text=NULL::text)
  RETURNS TABLE("message" text) AS $BODY$
DECLARE
    execute_sql_content TEXT;
		execute_context_info TEXT;
		rows_affected BIGINT;
		sql_update TEXT;
		sql_insert TEXT;
BEGIN
    IF rule_description IS NOT NULL THEN
        RAISE NOTICE '规则id：%，名称：%将会执行',rule_id,rule_description;
				execute_context_info:='规则'||rule_id||'('||rule_description||')'||'得到执行';
    ELSE
        RAISE NOTICE '规则id：%将会执行',rule_id;
				execute_context_info:='规则'||rule_id||'得到执行';
    END IF;
		
    -- 根据参数值查询表中存储的SQL语句（包含更新为false和冲突更新）
    SELECT sql_custom
    INTO execute_sql_content
    FROM base.bd_rule_config_info
    WHERE bd_rule_config_info_id = rule_id;
    RAISE NOTICE '执行的SQL为：%',execute_sql_content;
		-- select SPLIT_PART(execute_sql_content,';',1) into sql_update;
    -- 执行获取到的SQL语句
    EXECUTE execute_sql_content;
		GET DIAGNOSTICS rows_affected = ROW_COUNT;
		RAISE NOTICE '影响行数：%',rows_affected;
		execute_context_info := execute_context_info||'，影响行数为'||rows_affected::varchar||'条';
    RETURN QUERY SELECT execute_context_info::TEXT AS message;
EXCEPTION
    -- 可根据需要进行错误处理
    WHEN OTHERS THEN
        RAISE EXCEPTION '执行SQL时出现异常，异常信息为： %', SQLERRM;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000