Views and Routines

CREATE TABLE demo_emp AS SELECT id, name, dept_id, salary FROM employees;
ALTER TABLE demo_emp ADD PRIMARY KEY (id);
SELECT count(*) AS rows_copied FROM demo_emp;

CREATE VIEW demo_dept_summary AS
SELECT d.name AS department, count(e.id) AS headcount, round(avg(e.salary)) AS avg_salary
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
GROUP BY d.name;

SELECT * FROM demo_dept_summary ORDER BY department;

SELECT department FROM demo_dept_summary WHERE headcount = 0;

CREATE VIEW demo_engineers AS
SELECT id, name, dept_id, salary
FROM demo_emp
WHERE dept_id = 1
WITH CHECK OPTION;

UPDATE demo_engineers SET salary = salary + 1000 WHERE name = 'Arjun';

SELECT id, name, salary FROM demo_emp WHERE name = 'Arjun';

UPDATE demo_engineers SET dept_id = 2 WHERE name = 'Arjun';

DELETE FROM demo_engineers WHERE name = 'Divya';

SELECT count(*) AS divya_rows FROM demo_emp WHERE name = 'Divya';

UPDATE demo_dept_summary SET headcount = 10 WHERE department = 'HR';

CREATE MATERIALIZED VIEW demo_dept_totals AS
SELECT dept_id, count(*) AS headcount, sum(salary) AS payroll
FROM demo_emp
GROUP BY dept_id;

SELECT * FROM demo_dept_totals WHERE dept_id = 2;

UPDATE demo_emp SET salary = salary + 10000 WHERE dept_id = 2;

SELECT * FROM demo_dept_totals WHERE dept_id = 2;

REFRESH MATERIALIZED VIEW demo_dept_totals;

SELECT * FROM demo_dept_totals WHERE dept_id = 2;

CREATE UNIQUE INDEX demo_dept_totals_uq ON demo_dept_totals (dept_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY demo_dept_totals;

CREATE INDEX demo_emp_dept_idx ON demo_emp (dept_id);
SET enable_seqscan = off;

EXPLAIN (COSTS OFF)
SELECT * FROM demo_engineers WHERE dept_id = 1;

EXPLAIN (COSTS OFF)
SELECT * FROM demo_dept_totals WHERE dept_id = 2;

RESET enable_seqscan;

CREATE FUNCTION demo_annual_tax(salary numeric, rate numeric DEFAULT 0.10)
RETURNS numeric
LANGUAGE sql IMMUTABLE
AS $$ SELECT round(salary * rate, 2) $$;

SELECT name, salary, demo_annual_tax(salary) AS tax_default, demo_annual_tax(salary, 0.2) AS tax_20
FROM demo_emp
WHERE dept_id = 4;

CREATE FUNCTION demo_top_earners(p_dept int, p_limit int)
RETURNS TABLE (name text, salary int)
LANGUAGE plpgsql STABLE
AS $$
BEGIN
    IF p_limit < 1 THEN
        RAISE EXCEPTION 'limit must be positive, got %', p_limit;
    END IF;
    RETURN QUERY
        SELECT e.name, e.salary
        FROM demo_emp e
        WHERE e.dept_id = p_dept
        ORDER BY e.salary DESC, e.name
        LIMIT p_limit;
END;
$$;

SELECT * FROM demo_top_earners(1, 2);

SELECT * FROM demo_top_earners(1, 0);

CREATE TABLE demo_accounts (id int PRIMARY KEY, owner text, balance numeric(10,2) CHECK (balance >= 0));
INSERT INTO demo_accounts VALUES (1, 'Asha', 500), (2, 'Bala', 100);

CREATE PROCEDURE demo_transfer(p_from int, p_to int, p_amount numeric)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE demo_accounts SET balance = balance - p_amount WHERE id = p_from;
    UPDATE demo_accounts SET balance = balance + p_amount WHERE id = p_to;
END;
$$;

CALL demo_transfer(1, 2, 200);
SELECT * FROM demo_accounts ORDER BY id;

CALL demo_transfer(1, 2, 1000);
SELECT * FROM demo_accounts ORDER BY id;

CREATE TABLE demo_salary_audit (
    audit_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    emp_id     int,
    action     text,
    old_salary int,
    new_salary int,
    changed_at timestamptz DEFAULT now()
);

CREATE FUNCTION demo_log_salary() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO demo_salary_audit (emp_id, action, old_salary)
        VALUES (OLD.id, TG_OP, OLD.salary);
        RETURN OLD;
    ELSIF TG_OP = 'UPDATE' AND NEW.salary IS DISTINCT FROM OLD.salary THEN
        INSERT INTO demo_salary_audit (emp_id, action, old_salary, new_salary)
        VALUES (OLD.id, TG_OP, OLD.salary, NEW.salary);
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER demo_emp_audit
AFTER UPDATE OR DELETE ON demo_emp
FOR EACH ROW EXECUTE FUNCTION demo_log_salary();

UPDATE demo_emp SET salary = 300000 WHERE name = 'Meera';
UPDATE demo_emp SET name = 'Anita R.' WHERE name = 'Anita';
DELETE FROM demo_emp WHERE name = 'Tara';

SELECT emp_id, action, old_salary, new_salary FROM demo_salary_audit ORDER BY audit_id;

CREATE FUNCTION demo_no_pay_cut() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.salary < OLD.salary THEN
        RAISE EXCEPTION 'salary cut for employee % is not allowed', OLD.id;
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER demo_emp_no_cut
BEFORE UPDATE ON demo_emp
FOR EACH ROW EXECUTE FUNCTION demo_no_pay_cut();

UPDATE demo_emp SET salary = 100 WHERE name = 'Ravi';

SELECT name, salary FROM demo_emp WHERE name = 'Ravi';

DROP MATERIALIZED VIEW demo_dept_totals;
DROP VIEW demo_engineers, demo_dept_summary;
DROP TABLE demo_salary_audit, demo_accounts, demo_emp;
DROP FUNCTION demo_log_salary(), demo_no_pay_cut(), demo_annual_tax(numeric, numeric),
              demo_top_earners(int, int);
DROP PROCEDURE demo_transfer(int, int, numeric);