Select Git revision
udHistoryFunction_up.sql
-
Hugo Trentesaux authored
functions should be removed to change their return type
Hugo Trentesaux authoredfunctions should be removed to change their return type
udHistoryFunction_up.sql 1.71 KiB
CREATE FUNCTION public.get_ud_history(identity_row public.identity) RETURNS SETOF public.ud_history
LANGUAGE plpgsql STABLE
AS $$
BEGIN
RETURN QUERY
SELECT
CAST('ud-' || identity_row.name || '-' || ud.block_number || '-' || ud.event_id AS character varying) AS id,
ud.amount,
ud.block_number,
b.timestamp,
identity_row.id AS identity_id
FROM
public.universal_dividend ud
JOIN public.block b ON ud.block_number = b.height
WHERE
EXISTS (
SELECT 1
FROM (
SELECT
me1.block_number as creation_block,
COALESCE(
(
SELECT me2.block_number
FROM public.membership_event me2
WHERE me2.identity_id = me1.identity_id
AND me2.event_type = 'Removal'
AND me2.block_number > me1.block_number
ORDER BY me2.block_number
LIMIT 1
),
(SELECT MAX(block_number) FROM public.universal_dividend)
) as removal_block
FROM public.membership_event me1
WHERE me1.identity_id = identity_row.id
AND me1.event_type = 'Creation'
) as membership_periods
WHERE ud.block_number >= membership_periods.creation_block
AND ud.block_number <= membership_periods.removal_block
);
END;
$$;