PostgreSQL untuk Developer: Panduan Lengkap

-- Function sederhana: hitung IPK mahasiswa
CREATE OR REPLACE FUNCTION hitung_ipk(p_mahasiswa_id INTEGER)
RETURNS NUMERIC(3,2) AS $$
DECLARE
    v_ipk NUMERIC(3,2);
BEGIN
    SELECT ROUND(AVG(nilai) / 25.0, 2) INTO v_ipk
    FROM nilai
    WHERE mahasiswa_id = p_mahasiswa_id;

    RETURN COALESCE(v_ipk, 0.00);
END;
$$ LANGUAGE plpgsql;

-- Menggunakan function
SELECT nama, hitung_ipk(id) AS ipk FROM mahasiswa;

-- Function: konversi nilai ke grade
CREATE OR REPLACE FUNCTION konversi_grade(p_nilai NUMERIC)
RETURNS VARCHAR(2) AS $$
BEGIN
    RETURN CASE
        WHEN p_nilai >= 90 THEN 'A'
        WHEN p_nilai >= 85 THEN 'A-'
        WHEN p_nilai >= 80 THEN 'B+'
        WHEN p_nilai >= 75 THEN 'B'
        WHEN p_nilai >= 70 THEN 'B-'
        WHEN p_nilai >= 65 THEN 'C+'
        WHEN p_nilai >= 60 THEN 'C'
        WHEN p_nilai >= 50 THEN 'D'
        ELSE 'E'
    END;
END;
$$ LANGUAGE plpgsql;

-- Function: cek apakah mahasiswa bisa lulus
CREATE OR REPLACE FUNCTION cek_kelulusan(p_mahasiswa_id INTEGER)
RETURNS TABLE(nama TEXT, ipk NUMERIC, total_sks INTEGER, lulus BOOLEAN) AS $$
BEGIN
    RETURN QUERY
    SELECT
        m.nama::TEXT,
        hitung_ipk(m.id),
        COALESCE(SUM(mk.sks), 0)::INTEGER,
        (hitung_ipk(m.id) >= 2.00 AND COALESCE(SUM(mk.sks), 0) >= 144)
    FROM mahasiswa m
    LEFT JOIN nilai n ON m.id = n.mahasiswa_id
    LEFT JOIN mata_kuliah mk ON n.mata_kuliah_id = mk.id
    WHERE m.id = p_mahasiswa_id
    GROUP BY m.id, m.nama;
END;
$$ LANGUAGE plpgsql;

-- Menggunakan function
SELECT * FROM cek_kelulusan(1);

-- Procedure: proses pemberian nilai
CREATE OR REPLACE PROCEDURE berikan_nilai(
    p_mahasiswa_id INTEGER,
    p_mata_kuliah_id INTEGER,
    p_nilai NUMERIC
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_grade VARCHAR(2);
BEGIN
    -- Konversi nilai ke grade
    v_grade := konversi_grade(p_nilai);

    -- Insert atau update nilai
    INSERT INTO nilai (mahasiswa_id, mata_kuliah_id, nilai, grade)
    VALUES (p_mahasiswa_id, p_mata_kuliah_id, p_nilai, v_grade)
    ON CONFLICT (mahasiswa_id, mata_kuliah_id)
    DO UPDATE SET
        nilai = EXCLUDED.nilai,
        grade = EXCLUDED.grade;

    -- Update IPK mahasiswa
    UPDATE mahasiswa
    SET ipk = hitung_ipk(p_mahasiswa_id),
        updated_at = CURRENT_TIMESTAMP
    WHERE id = p_mahasiswa_id;

    RAISE NOTICE 'Nilai % (grade: %) berhasil diberikan', p_nilai, v_grade;
END;
$$;

-- Menjalankan procedure
CALL berikan_nilai(1, 3, 92.50);

-- Trigger: otomatis update timestamp
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Pasang trigger pada tabel mahasiswa
CREATE TRIGGER trigger_update_timestamp
    BEFORE UPDATE ON mahasiswa
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();