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

sql_labs/lab4.sql
253 строки11 KB
artem
Небольшие исправления SQL-лабораторных
15 июн 2026, 01:49
15 июн 2026, 01:49c75995d
-- =========================================================================
-- ЛАБОРАТОРНАЯ РАБОТА №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: Генерилка уникальных промокодов.
-- Аналог генерации уникальных номеров бронирований: код проверяется и внутри
-- массива текущей выдачи, и в отдельной таблице уже созданных промокодов.
CREATE TABLE IF NOT EXISTS realty_promo_codes (
    promo_code TEXT PRIMARY KEY,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

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)
           AND NOT EXISTS (SELECT 1 FROM realty_promo_codes WHERE promo_code = cand)
        THEN
            res := array_append(res, cand);
        END IF;
    END LOOP;
    RETURN res;
END; $$;

-- ЗАДАЧА 5: Ищет окно для продвижения объявления.
-- Аналог поиска окна между рейсами для техобслуживания: перебираем публикации
-- в городе и ищем промежуток, куда можно поставить рекламный показ/модерацию.
CREATE OR REPLACE FUNCTION find_ad_promotion_window(p_city TEXT, p_gap_hours INTEGER)
RETURNS TABLE (prev_ad_id BIGINT, next_ad_id BIGINT, window_start TIMESTAMP, window_end TIMESTAMP, gap_h NUMERIC)
LANGUAGE plpgsql AS $$
DECLARE rec RECORD; prev_id BIGINT; prev_time TIMESTAMP;
BEGIN
    FOR rec IN
        SELECT id, created_at
        FROM realty_advertisement
        WHERE city = p_city
        ORDER BY created_at
    LOOP
        IF prev_time IS NOT NULL
           AND EXTRACT(EPOCH FROM (rec.created_at - prev_time)) / 3600 >= p_gap_hours
        THEN
            prev_ad_id := prev_id;
            next_ad_id := rec.id;
            window_start := prev_time;
            window_end := rec.created_at;
            gap_h := ROUND((EXTRACT(EPOCH FROM (rec.created_at - prev_time)) / 3600)::NUMERIC, 2);
            RETURN NEXT;
            RETURN;
        END IF;
        prev_id := rec.id;
        prev_time := rec.created_at;
    END LOOP;
END; $$;

-- Дополнительный прикладной пример на цикл FOR: ищет долгие паузы в переписке
-- пользователя, чтобы оценивать потерю интереса к сервису.
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; $$;

-- ЗАДАЧА 8.1: Отмена заявок с попыткой подобрать альтернативу.
-- Аналог сбоя в аэропорту и пересадки пассажиров: при проблеме в городе
-- отключаем заявки и предлагаем похожие активные объявления в других городах.
CREATE OR REPLACE PROCEDURE cancel_neighbor_requests_with_relocation(p_city TEXT)
LANGUAGE plpgsql AS $$
DECLARE rec RECORD; alt RECORD;
BEGIN
    FOR rec IN
        SELECT id, user_id
        FROM realty_neighborrequest
        WHERE city = p_city AND is_active = true
        ORDER BY created_at
    LOOP
        UPDATE realty_neighborrequest SET is_active = false WHERE id = rec.id;

        SELECT id, title, city, price
        INTO alt
        FROM realty_advertisement
        WHERE is_active = true AND city <> p_city
        ORDER BY price, created_at DESC
        LIMIT 1;

        IF alt.id IS NULL THEN
            RAISE NOTICE 'Заявка % отменена, альтернативных объявлений нет', rec.id;
        ELSE
            RAISE NOTICE 'Заявка % отменена. Альтернатива: объявление %, город %, цена %',
                rec.id, alt.id, alt.city, alt.price;
        END IF;
    END LOOP;
END; $$;

-- ЗАДАЧА 8.2: Групповое создание заявок на совместную аренду.
-- Аналог бронирования на нескольких пассажиров: принимаем массив пользователей,
-- получаем JSON-профиль каждого и создаём отдельную заявку для участника.
CREATE OR REPLACE FUNCTION get_user_profile_json(p_uid BIGINT)
RETURNS JSONB LANGUAGE plpgsql AS $$
DECLARE v_profile JSONB;
BEGIN
    SELECT jsonb_build_object(
        'id', id,
        'email', email,
        'name', CONCAT_WS(' ', last_name, first_name, middle_name),
        'role', role
    )
    INTO v_profile
    FROM realty_user
    WHERE id = p_uid;

    IF v_profile IS NULL THEN
        RAISE EXCEPTION 'Пользователь % не найден', p_uid;
    END IF;
    RETURN v_profile;
END; $$;

CREATE OR REPLACE FUNCTION get_active_ads_count_for_user(p_uid BIGINT)
RETURNS INTEGER LANGUAGE plpgsql AS $$
DECLARE v_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM realty_advertisement
    WHERE user_id = p_uid AND is_active = true;
    RETURN COALESCE(v_count, 0);
END; $$;

CREATE OR REPLACE PROCEDURE create_group_neighbor_requests(
    p_user_ids BIGINT[],
    p_city TEXT,
    p_description TEXT
)
LANGUAGE plpgsql AS $$
DECLARE v_uid BIGINT; v_profile JSONB; v_ads_count INTEGER;
BEGIN
    FOREACH v_uid IN ARRAY p_user_ids LOOP
        v_profile := get_user_profile_json(v_uid);
        v_ads_count := get_active_ads_count_for_user(v_uid);

        INSERT INTO realty_neighborrequest(user_id, city, description, looking_for_apartment, is_active, created_at)
        VALUES (
            v_uid,
            p_city,
            p_description || ' | профиль=' || v_profile::TEXT || ' | активных объявлений=' || v_ads_count,
            true,
            true,
            NOW()
        );

        RAISE NOTICE 'Создана групповая заявка для пользователя %', v_uid;
    END LOOP;
END; $$;