postgresql相关sql集锦

1、类似于oracle的listagg->string_agg

SELECT
  area_county,','||string_agg(to_char(is_end,'9'),',,')||',' is_ends,count(1)total
FROM
  project_info
GROUP BY area_county

注意：

string_agg的第一个参数一定是字符类型，第二个参数是分隔符。
to_char函数是将数字类型的数据转换为字符类型，第一个参数是要转换的数据，第二个参数是转换的格式。
（对于to_char相关说明可以参考：https://www.postgresql.org/docs/10/functions-formatting.html）

结果：

2、统计某个字符在指定字符串中出现的次数。

select array_length(regexp_split_to_array(',-1,,-1,,-1,,-1,,-1,,-1,',',-1,'),1)-1 total

注意：

regexp_split_to_array函数的第一个参数是源字符串，第二个参数是要统计的字符。

结果：

  3、树级结构的数据查询（根据父级目录查询所有的子目录）

with RECURSIVE tpath AS(
           select catalog_id, order_index, parent_id, catalog_name,(catalog_name::varchar(1024))as  path from dir_catalog
           <where>
                and is_delete = ${dto.isDelete}
                <choose>
                    <when test="0 != dto.catalogId">
                        and catalog_id = #{dto.catalogId}
                    </when>
                    <otherwise>
                        and catalog_id in( select catalog_id from dir_catalog where parent_id = ${dto.catalogId})
                    </otherwise>
                </choose>
           </where>
           union all
            -- 联合子节点
            select b.catalog_id, b.order_index, b.parent_id,b.catalog_name,
                (tpath.path || '/' || b.catalog_name)::varchar(1024) as path
            from (
            select a.catalog_id, a.order_index, a.parent_id,a.catalog_name,a.is_delete from dir_catalog a where  a.app_id = #{dto.appId} and a.catalog_name like concat('%',#{dto.catalogName},'%'))b
            inner join tpath on b.parent_id = tpath.catalog_id and b.is_delete = ${dto.isDelete}
    )
    select catalog_id, order_index,catalog_name, parent_id, path FROM tpath
    where  catalog_name like concat('%',#{dto.catalogName},'%')
    order by parent_id,path,order_index limit #{pageSize} offset #{pageNo}

4、懒加载查询根节点和叶子节点，并标识根节点的子节点个数

   select catalog_id, null resource_id, catalog_name as name,1 isCatalog,order_index,code,
       (select sum(total) from (select count(*)total from dir_catalog where parent_id = dc.catalog_id and is_delete= ${isDelete}
                                union all
                                select count(*)total from dir_resource where catalog_id = dc.catalog_id and is_delete= ${isDelete}) as dctdrt)totalChildren,is_publish
        from dir_catalog dc
        where parent_id = #{parentId} and app_id = #{appId} and is_delete = ${isDelete}
        union all
        select catalog_id,resource_id,resource_name  as name,0 isCatalog,order_index,code,0 totalChildren,is_publish from dir_resource
        where catalog_id = #{parentId} and app_id = #{appId} and is_delete= ${isDelete}

5、从指定序列中取出指定个数的序号

-- varying：序列名称，integer:返回个数 使用”;“隔开
create function getnextids(character varying, integer) returns character varying
    language plpgsql
as
$$
DECLARE str VARCHAR ;
    DECLARE nextid VARCHAR ;
BEGIN

    str = '';
    FOR i in 1..$2 loop
            nextid = (SELECT NEXTVAL($1));
            str = str || nextid || ';' ;
        END loop;

    RETURN str ;
END ;
$$;

alter function getnextids(varchar, integer) owner to postgres;

6、postgresql自动更新操作时间（自动更新update_time --名字一定要是update_time）

-- 创建函数
create or replace function upd_timestamp() returns trigger as
$$
begin
    new.update_time = current_timestamp(0);
    return new;
end
$$
language plpgsql;

-- 创建触发器
create trigger t_table_name before update on log_table_name for each row execute procedure upd_timestamp();

备注：1、自动更新create_time：default ('now'::text)::timestamp(0) with time zone

            2、创建表时自动为主键生成序列，则指定主键类型为：SERIAL 即可