Advanced SQL — Views, Indexes, Stored Procedures & Triggers

-- Regular view: virtual table — query runs every time view is accessed
CREATE VIEW StudentFullInfo AS
SELECT
    s.StudentID,
    s.FirstName || ' ' || s.LastName AS FullName,
    s.Email, s.GPA, s.Status,
    d.DeptName,
    COUNT(e.CourseID) AS CoursesEnrolled
FROM Student s
JOIN Department d ON s.DeptID = d.DeptID
LEFT JOIN Enrollment e ON s.StudentID = e.StudentID
GROUP BY s.StudentID, s.FirstName, s.LastName, s.Email, s.GPA, s.Status, d.DeptName;

-- Query the view like a table
SELECT FullName, GPA, DeptName FROM StudentFullInfo WHERE GPA > 3.5;

-- Security view: expose only non-sensitive columns
CREATE VIEW PublicEmployeeInfo AS
SELECT EmpID, Name, DeptName, Title    -- No Salary, SSN, DOB
FROM Employee JOIN Department USING (DeptID);
GRANT SELECT ON PublicEmployeeInfo TO api_user;

-- Updatable view with check option
CREATE VIEW ActiveStudents AS
SELECT StudentID, Name, DeptID FROM Student WHERE Status = 'Active'
WITH CHECK OPTION;   -- Prevents INSERT/UPDATE that would violate WHERE condition

-- MATERIALIZED VIEW: physically stores query result (like a cache)
CREATE MATERIALIZED VIEW DeptStats AS
SELECT DeptID, COUNT(*) AS Headcount, AVG(Salary) AS AvgSalary
FROM Employee GROUP BY DeptID;

CREATE INDEX ON DeptStats (DeptID);

-- Must manually refresh (data can become stale)
REFRESH MATERIALIZED VIEW DeptStats;
REFRESH MATERIALIZED VIEW CONCURRENTLY DeptStats;  -- Non-blocking

CREATE OR REPLACE PROCEDURE EnrollStudent(
    p_student_id INT,
    p_course_id  VARCHAR(10),
    OUT p_result VARCHAR(100)
) LANGUAGE plpgsql AS $$
DECLARE
    v_gpa         DECIMAL(3,2);
    v_credits     INT;
    v_total       INT;
    v_max         INT := 20;
BEGIN
    -- Verify student exists and is active
    SELECT GPA INTO v_gpa FROM Student
    WHERE StudentID = p_student_id AND Status = 'Active';
    IF NOT FOUND THEN
        p_result := 'ERROR: Student not found or inactive'; RETURN;
    END IF;

    -- Check course exists
    SELECT Credits INTO v_credits FROM Course WHERE CourseID = p_course_id;
    IF NOT FOUND THEN
        p_result := 'ERROR: Course not found'; RETURN;
    END IF;

    -- Check already enrolled
    IF EXISTS (SELECT 1 FROM Enrollment
               WHERE StudentID = p_student_id AND CourseID = p_course_id) THEN
        p_result := 'ERROR: Already enrolled'; RETURN;
    END IF;

    -- Enroll
    INSERT INTO Enrollment (StudentID, CourseID, EnrollDate)
    VALUES (p_student_id, p_course_id, CURRENT_DATE);
    p_result := 'SUCCESS: Enrolled in ' || p_course_id;
    COMMIT;
EXCEPTION WHEN OTHERS THEN
    ROLLBACK; p_result := 'ERROR: ' || SQLERRM;
END; $$;

CALL EnrollStudent(101, 'CS301', NULL);

-- Function (returns value, usable in SELECT)
CREATE OR REPLACE FUNCTION GetGrade(p_student INT, p_course VARCHAR(10))
RETURNS CHAR(2) LANGUAGE plpgsql AS $$
DECLARE v_grade CHAR(2);
BEGIN
    SELECT Grade INTO v_grade FROM Enrollment
    WHERE StudentID = p_student AND CourseID = p_course;
    RETURN COALESCE(v_grade, 'NA');
END; $$;

SELECT Name, GetGrade(StudentID, 'CS301') AS CS301Grade FROM Student;

-- Audit log table
CREATE TABLE SalaryAudit (
    AuditID   SERIAL       PRIMARY KEY,
    EmpID     INT,
    OldSalary DECIMAL(10,2),
    NewSalary DECIMAL(10,2),
    ChangedBy VARCHAR(100) DEFAULT CURRENT_USER,
    ChangedAt TIMESTAMPTZ  DEFAULT NOW()
);

-- Trigger function
CREATE OR REPLACE FUNCTION log_salary_change()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    IF OLD.Salary IS DISTINCT FROM NEW.Salary THEN
        INSERT INTO SalaryAudit(EmpID, OldSalary, NewSalary)
        VALUES (NEW.EmpID, OLD.Salary, NEW.Salary);
    END IF;
    RETURN NEW;
END; $$;

-- AFTER trigger: runs after the DML statement
CREATE TRIGGER salary_audit
AFTER UPDATE ON Employee
FOR EACH ROW EXECUTE FUNCTION log_salary_change();

-- BEFORE trigger: modify data before it is written
CREATE OR REPLACE FUNCTION normalise_name()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    NEW.Name      = TRIM(INITCAP(NEW.Name));   -- Normalise case
    NEW.UpdatedAt = NOW();                      -- Auto-set timestamp
    RETURN NEW;
END; $$;

CREATE TRIGGER auto_normalise
BEFORE INSERT OR UPDATE ON Employee
FOR EACH ROW EXECUTE FUNCTION normalise_name();