Triggery

CREATE TABLE lide 
(jmeno VARCHAR(50) PRIMARY KEY,
 bydliste VARCHAR(50),
 vek INT,
 plat NUMERIC(8,2),
 posledni_zmena TIMESTAMP);

INSERT INTO lide (jmeno, bydliste, vek, plat) VALUES
('Alice', 'Praha', 30, 45000),
('Bob', 'Brno', 28, 30150),
('Cyril', 'Brno', 70, 27000),
('David', 'Olomouc', 45, 13000),
('Ivan', 'Olomouc', 70, 6500),
('Jana', 'Praha', 35, 12300);

----------------------------

CREATE OR REPLACE FUNCTION foo (INT, TEXT) RETURNS int AS $$
DECLARE
        bar int;
        baz int;
        arg1 ALIAS FOR $1;
        arg2 ALIAS FOR $2;
BEGIN
        SELECT vek INTO bar FROM lide WHERE jmeno = arg2;
        baz := 10;
        RETURN arg1 * bar + baz;
END;
$$ LANGUAGE plpgsql;


SELECT foo(2, 'Alice');

----------------------------

CREATE OR REPLACE FUNCTION bar () RETURNS VOID AS $$
DECLARE
        r RECORD;
        x int;
BEGIN
        SELECT count(*) INTO x FROM pg_tables WHERE tablename = 'xyz';
        IF x = 0 THEN
                RAISE NOTICE 'Vytvoril jsem tabulku xyz';
                CREATE TABLE xyz (a int, b int);
        ELSE
                RAISE NOTICE 'Vyprazdnil jsem tabulku xyz';
                DELETE FROM xyz;
        END IF;

        x := 1;
        FOR r IN SELECT * FROM lide
        LOOP
                RAISE NOTICE 'Kopiruji radek %', x;
                INSERT INTO xyz (a, b) VALUES (r.vek, r.plat::int);
                x := x + 1;
        END LOOP;
        RETURN;
END;
$$ LANGUAGE plpgsql;


SELECT bar();
SELECT bar();

---------------------------

CREATE OR REPLACE FUNCTION timestamp_maker() RETURNS TRIGGER AS $$
BEGIN
        new.posledni_zmena = now();
        RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER timestamp_trigger BEFORE UPDATE ON lide FOR EACH ROW EXECUTE PROCEDURE timestamp_maker();


UPDATE lide SET plat = plat + 100 WHERE bydliste = 'Olomouc';

----------------------------

CREATE TABLE log
(id SERIAL PRIMARY KEY,
 ts TIMESTAMP NOT NULL,
 jmeno TEXT,
 msg TEXT NOT NULL,
 zmena INTEGER);


CREATE OR REPLACE FUNCTION salary_audit() RETURNS TRIGGER AS $$
BEGIN
  IF (new.plat > old.plat) THEN
    INSERT INTO log (ts, jmeno, msg, zmena) VALUES
    (now(), new.jmeno, 'Zvyseni platu', new.plat - old.plat);
  END IF;
  IF (new.plat < old.plat) THEN
    INSERT INTO log (ts, jmeno, msg, zmena) VALUES
    (now(), new.jmeno, 'Snizeni platu', old.plat - new.plat);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER salary_update AFTER UPDATE ON lide FOR EACH ROW EXECUTE PROCEDURE salary_audit();


UPDATE lide SET plat = plat + 100 WHERE bydliste = 'Olomouc';
UPDATE lide SET plat = plat - 100 WHERE bydliste = 'Brno';