Skip to content
Snippets Groups Projects
Select Git revision
  • main default protected
  • gtest-custom
  • gtest
  • account-balance
  • hugo-rebase-mr-23
  • fix-23-before-rebase
  • add-certifications-date
  • proxy-limiter
  • convert-hash-bytes-to-hexa
  • 0.2.9
  • 0.2.8
  • 0.2.7
  • 0.2.6
  • 0.2.4
  • 0.2.3
  • 0.2.2
  • 0.2.0
  • 0.1.3
  • 0.1.2
  • 0.1.1
20 results

udHistoryFunction_up.sql

Blame
  • 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;
    $$;