PostgreSQL函数中查询计数并按需导出至AWS S3的优化方案

背景说明 #

现有一个绑定在public.mainTable上的语句级触发器，每次表中插入数据后，会调用view_function()函数。触发器定义如下：

CREATE TRIGGER my_tigger
AFTER INSERT ON public.mainTable
REFERENCING NEW TABLE AS table4
FOR EACH STATEMENT
EXECUTE FUNCTION view_function();

原view_function()函数负责关联多张表查询数据并返回结果，代码如下：

CREATE OR REPLACE FUNCTION public.view_function()
 RETURNS TABLE (
    uid VARCHAR,
    namingid VARCHAR,
    service VARCHAR,
    servicetype VARCHAR,
    name VARCHAR,
    location VARCHAR )

 LANGUAGE plpgsql
AS $function$
declare

    count     Integer;

begin

    RETURN QUERY 
  SELECT DISTINCT etm.uid,
  etm.namingid,
  etm.service,
  etm.servicetype,
  el.name,
  el.location
  FROM public.table1 etm
   LEFT JOIN public.table2 bt ON etm.namingid = bt.businessid
   LEFT JOIN public.table3 at2 ON etm.namingid = at2.aliasid
   LEFT JOIN table4 el ON etm.uid::text = el.uid::text AND etm.namingid::text = el.name::text;

END; $function$
;

现在需要实现：将查询结果上传至AWS S3，但仅当结果行数大于0时才执行导出，同时避免核心查询重复执行两次。

解决方案 #

核心思路是先用临时表存储单次查询的结果，后续的计数判断和S3导出都基于这个临时表操作，彻底避免重复执行关联查询。修改后的函数代码如下：

CREATE OR REPLACE FUNCTION public.view_function()
 RETURNS TABLE (
    uid VARCHAR,
    namingid VARCHAR,
    service VARCHAR,
    servicetype VARCHAR,
    name VARCHAR,
    location VARCHAR )
 LANGUAGE plpgsql
AS $function$
declare
    result_count Integer;
begin
    -- 1. 创建临时表存储查询结果，仅执行一次核心关联查询
    CREATE TEMP TABLE temp_query_results ON COMMIT DROP AS
    SELECT DISTINCT etm.uid,
           etm.namingid,
           etm.service,
           etm.servicetype,
           el.name,
           el.location
    FROM public.table1 etm
    LEFT JOIN public.table2 bt ON etm.namingid = bt.businessid
    LEFT JOIN public.table3 at2 ON etm.namingid = at2.aliasid
    LEFT JOIN table4 el ON etm.uid::text = el.uid::text AND etm.namingid::text = el.name::text;

    -- 2. 获取结果行数
    SELECT COUNT(*) INTO result_count FROM temp_query_results;

    -- 3. 仅当结果数大于0时，执行S3导出
    IF result_count > 0 THEN
        PERFORM aws_s3.query_export_to_s3(
            'SELECT * FROM temp_query_results',
            'your-bucket-name', -- 替换为实际S3桶名
            'exported-data/' || NOW()::TEXT || '.csv', -- 自定义S3存储路径和文件名
            'your-aws-region' -- 替换为实际AWS区域
        );
    END IF;

    -- 4. 保持原函数的返回逻辑
    RETURN QUERY SELECT * FROM temp_query_results;
END; $function$
;

关键细节说明 #

• 临时表特性：ON COMMIT DROP参数确保临时表在事务结束后自动删除，不会在数据库中留下冗余数据，仅当前会话有效。
• 避免重复查询：核心关联查询只执行一次，后续计数、导出、返回结果都基于临时表，大幅提升性能。
• 兼容原有功能：最后依然从临时表返回结果，原函数的返回逻辑不受影响。
• S3参数调整：需要将代码中的桶名、区域替换为实际值，文件名可根据业务需求调整格式（比如加入时间戳避免重名）。

内容的提问来源于stack exchange，提问作者sammy morgan