I am frontend engineer building something with supabase, I have written this postgres function
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,
bcm.schedule_id, -- This could be null
bc.id AS checklist_id,
bcm.completed,
bcm.performed_by,
bcm.performed_on_date,
bc.parent_id,
bc.order as order_number,
bc.event_type,
bc.archived
FROM
blueprint_checklist bc
LEFT JOIN
blueprint_schedule_completion_mapping bcm
ON
bcm.schedule_id = schedule_id AND bc.id = bcm.blueprint_checklist_id
WHERE
bc.blueprint_id = blueprint_id_value
AND bc.event_type = event_type;
$function$;
in this, for bcm.schedule_id = schedule_id AND bc.id = bcm.blueprint_checklist_id
condition, I only want the record with most recent performed_on_date
in table.
How can I do it?
2
Answers
I think, if do not consider performance, you can get most resent row (rows)
Using
DISTINCT ON (bc.id, bcm.schedule_id)
and ordering bybc.id, bcm.schedule_id, bcm.performed_on_date
will return the desired results. The following also addresses ambiguous references in the original code caused by unqualified identifiers:If the result of this function is to be joined to other tables, then consider implementing it as a view to take advantage of the query engine’s optimization features. Functions act as optimization fences.
(I’ve not yet tested this code since the original post lacked DDL, sample data, and expected output and I haven’t had time to create my own test cases.)