aonestar.public

Parameters

Name	Type	Mode
p_calendar_id	integer	IN (DEFAULT NULL)
p_start	date	IN (DEFAULT NULL)
p_end	date	IN (DEFAULT NULL)
p_search	text	IN (DEFAULT NULL)
p_page	integer	IN (DEFAULT 1)
p_limit	integer	IN (DEFAULT 20)

Definition

DECLARE
    v_result json;
    v_page  integer := greatest(coalesce(p_page, 1), 1);
    v_limit integer := greatest(coalesce(p_limit, 20), 1);
    v_state text; v_message text; v_detail text; v_hint text; v_context text;
BEGIN
    WITH filtered AS (
        SELECT e.id, e.calendar_id, c.name AS calendar_name, c.calendar_type,
               e.name, e.start_date, e.end_date, e.all_day, e.start_time, e.end_time,
               coalesce(e.color, c.color) AS color, e.source_id, e.external_uid,
               e.remark, e.enabled
        FROM public.calendar_event e
            JOIN public.calendar c ON c.id = e.calendar_id
        WHERE (p_calendar_id IS NULL OR e.calendar_id = p_calendar_id)
          AND (p_start IS NULL OR coalesce(e.end_date, e.start_date) >= p_start)
          AND (p_end   IS NULL OR e.start_date <= p_end)
          AND (nullif(p_search, '') IS NULL OR e.name ILIKE '%' || p_search || '%')
    ), total AS (
        SELECT count(*)::int AS total_rows FROM filtered
    ), paged AS (
        SELECT * FROM filtered
        ORDER BY start_date, id
        LIMIT v_limit OFFSET (v_page - 1) * v_limit
    )
    SELECT json_build_object(
        'items', coalesce((SELECT json_agg(p ORDER BY p.start_date, p.id) FROM paged p), '[]'::json),
        'paging', json_build_object(
            'current', v_page,
            'limit', v_limit,
            'total_rows', total.total_rows,
            'row_count', (SELECT count(*)::int FROM paged))
    )
    INTO v_result
    FROM total;
    RETURN v_result;
EXCEPTION WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
        v_state = RETURNED_SQLSTATE, v_message = MESSAGE_TEXT,
        v_detail = PG_EXCEPTION_DETAIL, v_hint = PG_EXCEPTION_HINT, v_context = PG_EXCEPTION_CONTEXT;
    RETURN fn_handle_error(v_state, v_message, v_detail, v_hint, v_context, 'public.fn_get_calendar_events_list_js', null::jsonb);
END