PG_INDEXES Sistem Tablosu

Giriş
Sütunlar şöyle
-schemaname
-tablename
-indexname
-tablespace
-indexdef

Örnek
Şöyle yaparız.

SELECT indexname FROM pg_indexes;

Örnek

Şöyle yaparız.

-- List the index name
SELECT indexname, tablename, schemaname FROM pg_catalog.pg_indexes

CREATE TRIGGER

Giriş

Söz dizimi şöyle

CREATE TRIGGER Triggerİsmi + "AFTER|BEFORE" + "INSERT|UPDATE|DELETE" + "ON Tabloİsmi" + 
  "Effect of Trigger" + "Function to Execute"

1. AFTER ve BEFORE Farkı

Açıklaması şöyle. BEFORE ile bir veri tabanı işlemini engelleme şansımız var.

A trigger can run either BEFORE or AFTER an event.

If you want to block an event like an INSERT, you will want to run BEFORE. If you need to be sure the event actually is going to occur, AFTER is ideal.

Effect of the Trigger

Açıklaması şöyle. FOR EACH ROW veya FOR EACH STATEMENT değerlerini kullanabiliriz.

A trigger can run either per row, or per statement. Let's say you run a single UPDATE statement that changes 5 rows in a table.

If you specify FOR EACH ROW in the trigger, then the trigger will run 5 times. If you specified FOR EACH STATEMENT, then it would only run once.

And of course we can't forget the actual code to run when the trigger is activated. In Postgres, is placed in a function and separated from the trigger. Separating the trigger from the code it runs creates cleaner code and allows multiple triggers to execute the same code.

Function to Execute

Açıklaması şöyle. 

A trigger function is created like a regular Postgres function, except that it returns a trigger.

Eğer BEFORE işlemi yapılıyorsa açıklaması şöyle

The new keyword represents the values of the row that is to be inserted. It also is the object you can return to allow the insert to continue. Alternatively, when null is returned this stops the insertion.

Function to Execute İçinde Kullanılabilecek Anahtar Kelimeler

1. TG_TABLE_NAME ile değişen tablo ismi elde edilir

2. TG_OP ile işlem tipi (INSERT, UPDATE, DELETE elde edilir

Örnek

Elimizde şöyle bir kod olsun

CREATE TRIGGER audit_log_trigger
    BEFORE INSERT OR UPDATE OR DELETE 
 ON public.case_studies
    FOR EACH ROW
    EXECUTE FUNCTION audit_trigger(); 

Şöyle yaparız

CREATE OR REPLACE FUNCTION audit_trigger() RETURNS TRIGGER AS $$
DECLARE
    new_data jsonb;
    old_data jsonb;
    key text;
    new_values jsonb;
    old_values jsonb;
    user_id text;
BEGIN

    user_id := current_setting('audit.user_id', true);

    IF user_id IS NULL THEN
        user_id := current_user;
    END IF;

    new_values := '{}';
    old_values := '{}';

    IF TG_OP = 'INSERT' THEN
        new_data := to_jsonb(NEW);
        new_values := new_data;

    ELSIF TG_OP = 'UPDATE' THEN
        new_data := to_jsonb(NEW);
        old_data := to_jsonb(OLD);

        FOR key IN SELECT jsonb_object_keys(new_data) INTERSECT SELECT jsonb_object_keys(old_data)
        LOOP
            IF new_data ->> key != old_data ->> key THEN
                new_values := new_values || jsonb_build_object(key, new_data ->> key);
                old_values := old_values || jsonb_build_object(key, old_data ->> key);
            END IF;
        END LOOP;

    ELSIF TG_OP = 'DELETE' THEN
        old_data := to_jsonb(OLD);
        old_values := old_data;

        FOR key IN SELECT jsonb_object_keys(old_data)
        LOOP
            old_values := old_values || jsonb_build_object(key, old_data ->> key);
        END LOOP;

    END IF;

    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, record_id, operation_type, changed_by, original_values, new_values)
        VALUES (TG_TABLE_NAME, NEW.id, TG_OP, user_id, old_values, new_values);

        RETURN NEW;
    ELSE
        INSERT INTO audit_log (table_name, record_id, operation_type, changed_by, original_values, new_values)
        VALUES (TG_TABLE_NAME, OLD.id, TG_OP, user_id, old_values, new_values);

        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

1. DECLARE ile trigger içinde kullanılacak yerel değişkenler tanımlanır

2. current_setting() çağrısı ile bir konfigürasyon parametresi okunur. Bu parametre veri tabanına şöyle atanır

SELECT set_config('audit.user_id', 'test user', true);

3. OLD ve NEW eski ve değişen satırları temsil eder. Açıklaması şöyle

In PostgreSQL, OLD represents the row before update while the NEW represents the new row that will be updated (see docs).

Sütunlara erişmek için NEW.id, OLD.id şeklinde kullanırız. Tabii ki sütunun tabloda mevcut olması gerekir.

 

4. TG_OP ile işlem tipi bulunur. Açıklaması şöyle

TG_OP which is a keyword representing the type of operation (INSERT, UPDATE, or DELETE)

2. AFTER

Örnek - INSERT OR UPDATE OR DELETE

Şöyle yaparız. table_name yerine istediğimiz tablo ismi gelir.

CREATE TRIGGER table_change 
    AFTER INSERT OR UPDATE OR DELETE ON table_name
    FOR EACH ROW EXECUTE PROCEDURE notify_change();

Tablo değişince şu metod çağrılır. Burada değişen tablo ismi TG_TABLE_NAME ile elde edilir.

CREATE OR REPLACE FUNCTION notify_change() RETURNS TRIGGER AS $$
    BEGIN
        SELECT pg_notify('test', TG_TABLE_NAME);
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

Örnek - UPDATE

Elimizde şöyle bir trigger olsun

CREATE TRIGGER change_time_punch_audit AFTER UPDATE ON time_punch
FOR EACH ROW EXECUTE PROCEDURE fn_change_time_punch_audit();

Bu trigger time_punch tablosuna kayıt eklendikten sonra audit tablosuna bir satır ekler. Şöyledir

CREATE OR REPLACE FUNCTION fn_change_time_punch_audit() RETURNS TRIGGER AS $psql$
  BEGIN
    INSERT INTO time_punch_audit (change_time,change_employee_id,time_punch_id,punch_time)
    VALUES
      (now(), new.change_employee_id, new.id, old.punch_time);
    RETURN new;
  END;
$psql$ language plpgsql;

3. BEFORE

Örnek - INSERT

Elimizde şöyle bir tablo olsun

CREATE TABLE employee ( id SERIAL PRIMARY KEY, username VARCHAR );
CREATE TABLE time_punch (
  id SERIAL PRIMARY KEY,
  employee_id INT NOT NULL REFERENCES employee(id),
  is_out_punch BOOLEAN NOT NULL DEFAULT FALSE,
  punch_time TIMESTAMP NOT NULL DEFAULT now()
);
INSERT INTO employee (username) VALUES ('Bear');
INSERT INTO time_punch (employee_id, is_out_punch, punch_time)
VALUES
(1, false, '2020-01-01 10:00:00'),
(1, true, '2020-01-01 11:30:00'); 

Şöyle yaparız

CREATE TRIGGER check_time_punch BEFORE INSERT ON time_punch
FOR EACH ROW EXECUTE PROCEDURE fn_check_time_punch(); 

Trigger'ın çağırdığı metod şöyledir. Burada trigger yeni kayıt out_punch ise bir select çalıştırır ve bunun sonucunda null döner.

CREATE OR REPLACE FUNCTION fn_check_time_punch() RETURNS TRIGGER AS $psql$
  BEGIN
    IF new.is_out_punch = (
      SELECT tps.is_out_punch
      FROM time_punch tps
      WHERE tps.employee_id = new.employee_id
      ORDER BY tps.id desc limit 1
    ) THEN
      RETURN NULL;
    END IF;
    RETURN new;
  END;
$psql$ language plpgsql; 

Örnek

Şöyle yaparız

-- Create a trigger function to update the last_modified timestamp on table updates
CREATE OR REPLACE FUNCTION update_last_modified() RETURNS TRIGGER AS $$
BEGIN
  NEW.last_modified := NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create a trigger that uses the function
CREATE TRIGGER update_last_modified_trigger
BEFORE UPDATE ON your_table
FOR EACH ROW
EXECUTE FUNCTION update_last_modified();

Örnek - UPDATE

Trigger içinden function çağırmak için şöyle yaparız. Burada OLD ile eski, NEW ile yeni değer elde edilir.

CREATE TRIGGER update_last_edit_date BEFORE UPDATE ON employee
            FOR EACH ROW
            WHEN (OLD.* IS DISTINCT FROM NEW.*)
            EXECUTE PROCEDURE update_last_edit_date();

update_last_edit_date kodu için şöyle yaparız.

CREATE FUNCTION update_last_edit_date() RETURNS trigger AS $update_last_edit_date$
                BEGIN
                    NEW.last_edit_date := localtimestamp(0);
                    RETURN NEW;
                END;
$update_last_edit_date$ LANGUAGE plpgsql;