CREATE OR REPLACE FUNCTION public.redirect_insert_generic()
RETURNS TRIGGER AS
$$
DECLARE
    sub_name TEXT;
    target_table TEXT;
    sub_rec RECORD;
BEGIN
    -- getting all active subscription
    FOR sub_rec IN
        SELECT subscription_name
        FROM pglogical.show_subscription_status()
        WHERE status = 'replicating'
    LOOP
        sub_name := sub_rec.subscription_name;
        -- redirecting data
        IF sub_name = 'brazil_public_subscription' THEN
            target_table := format('brazil_public.%I', TG_TABLE_NAME);
            RAISE NOTICE 'Inserting into %', target_table;
            EXECUTE format('INSERT INTO %s SELECT $1.*', target_table) USING NEW;
        ELSIF sub_name = 'usa_public_subscription' THEN
            target_table := format('grain_us_public.%I', TG_TABLE_NAME);
            RAISE NOTICE 'Inserting into %', target_table;
            EXECUTE format('INSERT INTO %s SELECT $1.*', target_table) USING NEW;
        ELSIF sub_name = 'russia_public_subscription' THEN
            target_table := format('grain_public.%I', TG_TABLE_NAME);
            RAISE NOTICE 'Inserting into %', target_table;
            EXECUTE format('INSERT INTO %s SELECT $1.*', target_table) USING NEW;
        ELSE
            RAISE EXCEPTION 'Unknown subscription: %', sub_name;
        END IF;
    END LOOP;

    RETURN NULL; -- Prevent the insert in the original table
END;
$$ LANGUAGE plpgsql;

DO $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = 'public'
        AND table_type = 'BASE TABLE'
    LOOP
        EXECUTE format('
            CREATE TRIGGER redirect_insert_%I_trigger
            BEFORE INSERT ON public.%I
            FOR EACH ROW
            EXECUTE FUNCTION public.redirect_insert_generic();
        ', rec.table_name, rec.table_name);
    END LOOP;
END;
$$;