sql_labs/lab4.sql - master - nmktth/web-development-sem-4-advanced-git

sql_labs/lab4.sql
123 строки6 KB
Artem Ilin
first_commit
14 июн 2026, 21:45
14 июн 2026, 21:4567055a5
-- =========================================================================
-- ЛАБОРАТОРНАЯ РАБОТА №4: ЦИКЛЫ И МАССИВЫ 
-- =========================================================================

-- ЗАДАЧА 1: Функция для вытаскивания четных элементов. 
-- Чисто учебная штука для отработки цикла FOR.
CREATE OR REPLACE FUNCTION pick_even_indexed_ads(p_values INTEGER[])
RETURNS INTEGER[] LANGUAGE plpgsql AS $$
DECLARE res INTEGER[] := ARRAY[]::INTEGER[]; i INTEGER;
BEGIN
    FOR i IN 1..cardinality(p_values) LOOP
        IF i % 2 = 0 THEN res := array_append(res, p_values[i]); END IF;
    END LOOP;
    RETURN res;
END; $$;

-- ЗАДАЧА 2: То же самое, только через WHILE.
-- Помогает понять, как работать со счетчиками руками.
CREATE OR REPLACE FUNCTION pick_even_indexed_ads_while(p_values INTEGER[])
RETURNS INTEGER[] LANGUAGE plpgsql AS $$
DECLARE res INTEGER[] := ARRAY[]::INTEGER[]; i INTEGER := 1;
BEGIN
    WHILE i <= cardinality(p_values) LOOP
        IF i % 2 = 0 THEN res := array_append(res, p_values[i]); END IF;
        i := i + 1;
    END LOOP;
    RETURN res;
END; $$;

-- ЗАДАЧА 3: Алгоритм поиска простых чисел. 
-- Жесткая математика на вложенных циклах WHILE.
CREATE OR REPLACE FUNCTION first_n_primes_math(p_n INTEGER)
RETURNS INTEGER[] LANGUAGE plpgsql AS $$
DECLARE res INTEGER[] := '{}'; cand INTEGER := 2; div INTEGER; is_p BOOLEAN;
BEGIN
    WHILE cardinality(res) < p_n LOOP
        is_p := TRUE; div := 2;
        WHILE div * div <= cand LOOP
            IF cand % div = 0 THEN is_p := FALSE; EXIT; END IF;
            div := div + 1;
        END LOOP;
        IF is_p THEN res := array_append(res, cand); END IF;
        cand := cand + 1;
    END LOOP;
    RETURN res;
END; $$;

-- ЗАДАЧА 4: Генерилка уникальных промокодов. 
-- Полезно для маркетинга, чтобы коды не повторялись (проверка через ANY).
CREATE OR REPLACE FUNCTION generate_unique_promo_codes(p_count INTEGER)
RETURNS TEXT[] LANGUAGE plpgsql AS $$
DECLARE res TEXT[] := '{}'; cand TEXT;
BEGIN
    WHILE cardinality(res) < p_count LOOP
        cand := 'RENT-' || LPAD((FLOOR(RANDOM() * 1000000))::TEXT, 6, '0');
        IF NOT cand = ANY(res) THEN res := array_append(res, cand); END IF;
    END LOOP;
    RETURN res;
END; $$;

-- ЗАДАЧА 5: Ищет долгие затупы (паузы) в переписке юзера. 
-- Помогает вычислять тех, кто бросил приложение.
CREATE OR REPLACE FUNCTION find_message_gap(p_uid BIGINT, p_gap_hours INTEGER)
RETURNS TABLE (start_t TIMESTAMP, end_t TIMESTAMP, gap_h NUMERIC) LANGUAGE plpgsql AS $$
DECLARE rec RECORD; prev TIMESTAMP := NOW() - INTERVAL '1 year';
BEGIN
    FOR rec IN SELECT sent_at FROM realty_message WHERE sender_id = p_uid ORDER BY sent_at LOOP
        IF EXTRACT(EPOCH FROM (rec.sent_at - prev)) / 3600 >= p_gap_hours THEN
            start_t := prev; end_t := rec.sent_at; 
            gap_h := ROUND((EXTRACT(EPOCH FROM (rec.sent_at - prev)) / 3600)::NUMERIC, 2);
            RETURN NEXT;
        END IF;
        prev := rec.sent_at;
    END LOOP;
END; $$;

-- ЗАДАЧА 6: Удаляет старые сообщения аккуратными пачками. 
-- Мастхэв фича, чтобы не положить боевую БД блокировками при чистке.
CREATE OR REPLACE PROCEDURE purge_old_messages(p_batch INTEGER DEFAULT 500)
LANGUAGE plpgsql AS $$
DECLARE v_del INTEGER := 0;
BEGIN
    LOOP
        DELETE FROM realty_message WHERE id IN (SELECT id FROM realty_message WHERE sent_at < NOW() - INTERVAL '2 years' LIMIT p_batch);
        GET DIAGNOSTICS v_del = ROW_COUNT;
        EXIT WHEN v_del = 0;
        RAISE NOTICE 'Снесли пачку сообщений: %', v_del;
        COMMIT; -- Делаем коммит, чтобы отпустить блокировку таблиц
    END LOOP;
END; $$;

-- ЗАДАЧА 7: Архивирует обработанные жалобы. 
-- Перекидывает их в архивную таблицу, чтобы основная летала быстрее.
CREATE TABLE IF NOT EXISTS realty_complaint_archive AS SELECT * FROM realty_complaint WHERE 1=0;

CREATE OR REPLACE PROCEDURE archive_resolved_complaints(p_batch INTEGER DEFAULT 200)
LANGUAGE plpgsql AS $$
DECLARE v_moved INTEGER := 0;
BEGIN
    LOOP
        WITH moved AS (
            DELETE FROM realty_complaint WHERE id IN (SELECT id FROM realty_complaint WHERE status IN ('resolved', 'rejected') AND created_at < NOW() - INTERVAL '10 days' LIMIT p_batch)
            RETURNING *
        )
        INSERT INTO realty_complaint_archive SELECT * FROM moved;
        GET DIAGNOSTICS v_moved = ROW_COUNT;
        EXIT WHEN v_moved = 0;
        RAISE NOTICE 'Перекинуто в архив: % жалоб', v_moved;
        COMMIT;
    END LOOP;
END; $$;

-- ЗАДАЧА 8: Массово рубит заявки на поиск соседа в конкретном городе. 
-- Делает перебор через цикл FOR.
CREATE OR REPLACE PROCEDURE cancel_neighbor_requests_by_city(p_city TEXT)
LANGUAGE plpgsql AS $$
DECLARE rec RECORD;
BEGIN
    FOR rec IN SELECT id, user_id FROM realty_neighborrequest WHERE city = p_city AND is_active = true LOOP
        UPDATE realty_neighborrequest SET is_active = false WHERE id = rec.id;
        RAISE NOTICE 'Заявка % от чувака % в г. % отменена', rec.id, rec.user_id, p_city;
    END LOOP;
END; $$;