Практичне застосування можливостей PostgreSQL

CREATE TYPE ticket_status AS ENUM (
    'open',
    'in_progress',
    'resolved',
    'cancelled'
);

CREATE TYPE ticket_priority AS ENUM (
    'low',
    'normal',
    'high',
    'urgent'
);

tags text[] NOT NULL DEFAULT '{}'

metadata jsonb NOT NULL DEFAULT '{}'::jsonb

{
  "browser": "Firefox",
  "os": "Linux",
  "customer_id": 42
}

tstzrange('[2026-09-01 09:00:00+00,2026-09-01 17:00:00+00)')

search_vector tsvector GENERATED ALWAYS AS (
    to_tsvector(
        'simple'::regconfig,
        coalesce(title, '') || ' ' ||
        coalesce(description, '') || ' ' ||
        coalesce(array_to_string(tags, ' '), '')
    )
) STORED

CREATE EXTENSION IF NOT EXISTS pg_trgm;

DROP SCHEMA IF EXISTS support_demo CASCADE;
CREATE SCHEMA support_demo;
SET search_path TO support_demo;

CREATE TYPE ticket_status AS ENUM (
    'open',
    'in_progress',
    'resolved',
    'cancelled'
);

CREATE TYPE ticket_priority AS ENUM (
    'low',
    'normal',
    'high',
    'urgent'
);

CREATE TABLE customers (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    email text NOT NULL UNIQUE,
    created_at timestamptz NOT NULL DEFAULT clock_timestamp()
);

CREATE TABLE tickets (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

    customer_id bigint NOT NULL
        REFERENCES customers(id),

    title text NOT NULL
        CHECK (length(trim(title)) >= 5),

    description text NOT NULL,

    status ticket_status NOT NULL DEFAULT 'open',

    priority ticket_priority NOT NULL DEFAULT 'normal',

    tags text[] NOT NULL DEFAULT '{}'
        CHECK (array_position(tags, '') IS NULL),

    metadata jsonb NOT NULL DEFAULT '{}'::jsonb
        CHECK (jsonb_typeof(metadata) = 'object'),

    due_window tstzrange NOT NULL DEFAULT tstzrange(
        clock_timestamp(),
        clock_timestamp() + interval '24 hours',
        '[)'
    )
        CHECK (NOT isempty(due_window)),

    created_at timestamptz NOT NULL DEFAULT clock_timestamp(),

    updated_at timestamptz NOT NULL DEFAULT clock_timestamp(),

    search_vector tsvector GENERATED ALWAYS AS (
        to_tsvector(
            'simple'::regconfig,
            coalesce(title, '') || ' ' ||
            coalesce(description, '') || ' ' ||
            coalesce(array_to_string(tags, ' '), '')
        )
    ) STORED
);

CREATE TABLE ticket_events (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

    ticket_id bigint NOT NULL
        REFERENCES tickets(id)
        ON DELETE CASCADE,

    event_type text NOT NULL,

    old_status ticket_status,

    new_status ticket_status,

    details jsonb NOT NULL DEFAULT '{}'::jsonb,

    created_at timestamptz NOT NULL DEFAULT clock_timestamp()
);

CREATE INDEX tickets_search_vector_idx
    ON tickets USING GIN (search_vector);

CREATE INDEX tickets_title_trgm_idx
    ON tickets USING GIN (title gin_trgm_ops);

CREATE INDEX tickets_metadata_idx
    ON tickets USING GIN (metadata);

CREATE INDEX tickets_tags_idx
    ON tickets USING GIN (tags);

CREATE INDEX tickets_due_window_idx
    ON tickets USING GIST (due_window);

CREATE INDEX ticket_events_ticket_id_idx
    ON ticket_events (ticket_id);

CREATE OR REPLACE FUNCTION set_ticket_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at := clock_timestamp();
    RETURN NEW;
END;
$$;

CREATE TRIGGER tickets_set_updated_at
BEFORE INSERT OR UPDATE ON tickets
FOR EACH ROW
EXECUTE FUNCTION set_ticket_updated_at();

CREATE OR REPLACE FUNCTION audit_ticket_status_change()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF OLD.status IS DISTINCT FROM NEW.status THEN
        INSERT INTO ticket_events (
            ticket_id,
            event_type,
            old_status,
            new_status,
            details
        )
        VALUES (
            NEW.id,
            'status_changed',
            OLD.status,
            NEW.status,
            jsonb_build_object(
                'changed_at', clock_timestamp()
            )
        );
    END IF;

    RETURN NEW;
END;
$$;

CREATE TRIGGER tickets_audit_status_change
AFTER UPDATE OF status ON tickets
FOR EACH ROW
EXECUTE FUNCTION audit_ticket_status_change();

INSERT INTO customers (name, email)
VALUES
    ('Олена Коваль', 'olena@example.com'),
    ('Андрій Мельник', 'andrii@example.com'),
    ('Ірина Бондар', 'iryna@example.com');

INSERT INTO tickets (
    customer_id,
    title,
    description,
    priority,
    tags,
    metadata,
    due_window
)
VALUES
(
    1,
    'Не працює вхід до облікового запису',
    'Користувач отримує помилку під час входу після зміни пароля.',
    'high',
    ARRAY['login', 'authentication', 'password'],
    jsonb_build_object(
        'browser', 'Firefox',
        'os', 'Linux',
        'attempts', 3
    ),
    tstzrange(
        clock_timestamp(),
        clock_timestamp() + interval '4 hours',
        '[)'
    )
),
(
    2,
    'Повільно завантажується сторінка замовлення',
    'Сторінка замовлення відкривається приблизно 15 секунд.',
    'normal',
    ARRAY['performance', 'orders'],
    jsonb_build_object(
        'browser', 'Chrome',
        'os', 'Windows',
        'response_time_ms', 15000
    ),
    tstzrange(
        clock_timestamp() + interval '2 hours',
        clock_timestamp() + interval '2 days',
        '[)'
    )
),
(
    3,
    'Потрібно додати нового користувача',
    'Створіть користувачу доступ до внутрішньої панелі.',
    'low',
    ARRAY['users', 'administration'],
    jsonb_build_object(
        'department', 'sales',
        'role', 'manager'
    ),
    tstzrange(
        clock_timestamp() + interval '1 day',
        clock_timestamp() + interval '3 days',
        '[)'
    )
);

SELECT
    id,
    title,
    search_vector
FROM tickets;

CREATE OR REPLACE FUNCTION search_tickets(
    p_query text,
    p_status ticket_status DEFAULT NULL
)
RETURNS TABLE (
    ticket_id bigint,
    title text,
    status ticket_status,
    priority ticket_priority,
    score real
)
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
    v_query tsquery;
BEGIN
    IF p_query IS NULL OR length(trim(p_query)) = 0 THEN
        RAISE EXCEPTION 'Пошуковий запит не може бути порожнім';
    END IF;

    v_query := plainto_tsquery(
        'simple'::regconfig,
        trim(p_query)
    );

    RETURN QUERY
    SELECT
        t.id,
        t.title,
        t.status,
        t.priority,
        (
            ts_rank(t.search_vector, v_query)
            + similarity(t.title, trim(p_query))
        )::real AS score
    FROM tickets AS t
    WHERE
        (p_status IS NULL OR t.status = p_status)
        AND (
            t.search_vector @@ v_query
            OR t.title % trim(p_query)
        )
    ORDER BY score DESC, t.created_at DESC;
END;
$$;

SELECT *
FROM search_tickets('вхід пароль');

SELECT *
FROM search_tickets('сторінка', 'in_progress');

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM search_tickets('вхід пароль');

CREATE OR REPLACE PROCEDURE resolve_ticket(
    p_ticket_id bigint,
    p_resolution text
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_current_status ticket_status;
BEGIN
    IF p_resolution IS NULL OR length(trim(p_resolution)) = 0 THEN
        RAISE EXCEPTION 'Опис вирішення не може бути порожнім';
    END IF;

    SELECT status
    INTO v_current_status
    FROM tickets
    WHERE id = p_ticket_id
    FOR UPDATE;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Заявку з id % не знайдено', p_ticket_id;
    END IF;

    IF v_current_status IN ('resolved', 'cancelled') THEN
        RAISE EXCEPTION
            'Заявку з id % не можна завершити зі статусу %',
            p_ticket_id,
            v_current_status;
    END IF;

    UPDATE tickets
    SET
        status = 'resolved',
        metadata = metadata || jsonb_build_object(
            'resolution', trim(p_resolution),
            'resolved_at', clock_timestamp()
        )
    WHERE id = p_ticket_id;
END;
$$;

CALL resolve_ticket(
    1,
    'Пароль було скинуто, користувач підтвердив успішний вхід.'
);

SELECT
    id,
    status,
    metadata,
    updated_at
FROM tickets
WHERE id = 1;

SELECT
    ticket_id,
    event_type,
    old_status,
    new_status,
    details,
    created_at
FROM ticket_events
WHERE ticket_id = 1
ORDER BY created_at;

SELECT
    id,
    title,
    metadata
FROM tickets
WHERE metadata @> '{"os": "Linux"}'::jsonb;

SELECT
    id,
    title,
    metadata->>'browser' AS browser
FROM tickets;

SELECT
    id,
    title,
    tags
FROM tickets
WHERE tags @> ARRAY['performance'];

SELECT
    id,
    title,
    due_window
FROM tickets
WHERE due_window && tstzrange(
    clock_timestamp(),
    clock_timestamp() + interval '8 hours',
    '[)'
);

BEGIN;

CALL resolve_ticket(
    2,
    'Оптимізовано запит до таблиці замовлень.'
);

INSERT INTO ticket_events (
    ticket_id,
    event_type,
    details
)
VALUES (
    2,
    'resolution_notified',
    jsonb_build_object(
        'channel', 'email',
        'sent_at', clock_timestamp()
    )
);

COMMIT;

BEGIN;

CALL resolve_ticket(
    3,
    'Доступ створено та перевірено.'
);

-- Якщо наступна операція помилкова, усі зміни можна скасувати.
-- ROLLBACK;

-- 1. Пошук заявки.
SELECT *
FROM search_tickets('пароль');

-- 2. Переведення заявки у статус виконаної.
CALL resolve_ticket(
    1,
    'Користувачу надіслано інструкцію зі скидання пароля.'
);

-- 3. Перевірка автоматичного аудиту.
SELECT
    ticket_id,
    old_status,
    new_status,
    created_at
FROM ticket_events
WHERE ticket_id = 1
ORDER BY created_at;

-- 4. Пошук завершених заявок.
SELECT *
FROM search_tickets('пароль', 'resolved');

-- 5. Пошук за додатковим атрибутом.
SELECT id, title
FROM tickets
WHERE metadata @> '{"browser": "Firefox"}'::jsonb;

-- 6. Пошук заявок за тегом.
SELECT id, title
FROM tickets
WHERE tags @> ARRAY['login'];

CHECK (jsonb_typeof(metadata) = 'object')

WHERE title ILIKE '%пароль%'

SELECT ...
FOR UPDATE;