-- 9  -Create a simple function
CREATE OR REPLACE FUNCTION trim_name()
  RETURNS trigger AS
$$
BEGIN
  NEW.name = LTRIM(NEW.name);
  RETURN NEW;
END;

$$
  LANGUAGE 'plpgsql';

-- -Create a simple trigger
CREATE TRIGGER Name_Trim
  BEFORE INSERT
  ON Student
  FOR EACH ROW
EXECUTE PROCEDURE trim_name();

-- test the trigger

INSERT INTO STUDENT
VALUES (998, '     Constantinos Costa  ', 2, 'CS');

SELECT REPLACE(name, ' ', '_')
FROM STUDENT;


-- Lets create a simple cursor
CREATE OR REPLACE FUNCTION get_names(name varchar(10))
  RETURNS text AS
$$

DECLARE
  names TEXT DEFAULT '';
  count INT = 0;
  row_student RECORD;
  cur_students CURSOR FOR SELECT *
                          FROM STUDENT;
BEGIN
  OPEN cur_students;
  LOOP
    -- fetch row into the film
    FETCH cur_students INTO row_student;
    -- exit when no more row to fetch
    EXIT WHEN NOT FOUND;

    -- build the output
    IF row_student.name LIKE '%' || name || '%' THEN
      names := names||' '|| count || ':' || row_student.name;
      count :=count +1;
    END IF;
  END LOOP;

  -- Close the cursor
  CLOSE cur_students;

  RETURN names;
END;
$$
  LANGUAGE plpgsql;

SELECT get_names('Costa');


-- Full example
CREATE TABLE employees
(
  id         serial primary key,
  first_name varchar(40) NOT NULL,
  last_name  varchar(40) NOT NULL
);

CREATE TABLE employee_audits
(
  id          serial primary key,
  employee_id int          NOT NULL,
  last_name   varchar(40)  NOT NULL,
  changed_on  timestamp(6) NOT NULL
);

CREATE OR REPLACE FUNCTION log_last_name_changes()
  RETURNS trigger AS
$$
BEGIN
  IF NEW.last_name <> OLD.last_name THEN
    INSERT INTO employee_audits(employee_id, last_name, changed_on)
    VALUES (OLD.id, OLD.last_name, now());
  END IF;

  RETURN NEW;
END;
$$
  LANGUAGE 'plpgsql';

CREATE TRIGGER last_name_changes
  BEFORE UPDATE
  ON employees
  FOR EACH ROW
EXECUTE PROCEDURE log_last_name_changes();

-- Insert Some Data
INSERT INTO employees (first_name, last_name)
VALUES ('John', 'Doe');

INSERT INTO employees (first_name, last_name)
VALUES ('Lily', 'Bush');

SELECT *
FROM employees;

-- Let trigger :)
UPDATE employees
SET last_name = 'Brown'
WHERE ID = 2;

SELECT *
FROM employees;

SELECT *
FROM employee_audits;

