如何修改Supabase中的Postgres函数获取最新performed_on_date记录？

要实现仅获取blueprint_schedule_completion_mapping表中performed_on_date最新的关联记录，可通过**窗口函数ROW_NUMBER()**对匹配的记录分组排序，筛选出每组内的最新条目。修改后的完整函数如下：

DROP FUNCTION IF EXISTS get_schedule_checklist_data;

CREATE OR REPLACE FUNCTION get_schedule_checklist_data(
    schedule_id UUID,
    blueprint_id_value UUID,
    event_type service_configuration_type
) 
RETURNS TABLE (
    title TEXT,
    schedule_id UUID,
    checklist_id UUID, 
    completed BOOLEAN,
    performed_by TEXT,
    performed_on_date TIMESTAMPTZ,
    parent_id uuid,
    order_number INT8,
    event_type TEXT,
    archived BOOLEAN
)
LANGUAGE sql
AS $function$
SELECT
    bc.title, 
    filtered_bcm.schedule_id,
    bc.id AS checklist_id,
    filtered_bcm.completed,
    filtered_bcm.performed_by,
    filtered_bcm.performed_on_date,
    bc.parent_id,
    bc.order as order_number,
    bc.event_type,
    bc.archived
FROM
    blueprint_checklist bc
LEFT JOIN (
    -- 子查询筛选每个checklist对应的最新完成记录
    SELECT 
        bcm.*,
        ROW_NUMBER() OVER (
            PARTITION BY bcm.blueprint_checklist_id 
            ORDER BY bcm.performed_on_date DESC
        ) AS rn
    FROM blueprint_schedule_completion_mapping bcm
    WHERE bcm.schedule_id = schedule_id
) filtered_bcm ON bc.id = filtered_bcm.blueprint_checklist_id AND filtered_bcm.rn = 1
WHERE
    bc.blueprint_id = blueprint_id_value
    AND bc.event_type = event_type;
$function$;

核心修改说明 #

1. 子查询分组排序：在子查询中对blueprint_schedule_completion_mapping的记录按blueprint_checklist_id分组，每组内按performed_on_date倒序排列，用ROW_NUMBER()为每条记录分配行号，最新的记录行号为1。
2. 筛选最新记录：LEFT JOIN时仅关联行号为1的记录，确保每个checklist只匹配最新的完成记录。
3. 保留原逻辑：维持原LEFT JOIN的特性，即使某个checklist没有对应的完成记录，依然会返回该checklist的基础信息。

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