PostgreSQL Trigger, Cursor, Transaction ve Exception Tanımlamak

create [ or replace ] [ constraint ] trigger name { before | after | instead of } { event [ or ... ] }
    on table_name
    [ from referenced_table_name ]
    [ not deferrable | [ deferrable ] [ initially immediate | initially deferred ] ]
    [ referencing { { old | new } table [ as ] transition_relation_name } [ ... ] ]
    [ for [ each ] { row | statement } ]
    [ when ( condition ) ]
    execute { function | procedure } function_name ( arguments )

create function insertCountryForBackup() returns trigger as $func$
begin
    insert into country_backup (id, code, title, createddate) values (new.id, new.code, new.title, new.createdDate);
    return new;
end;
$func$
language plpgsql;

create trigger backupCountry after insert on country
for each row execute function insertCountryForBackup();

insert into country (code, title, createddate) values ('UZ', 'Özbekistan', '2023-02-04');

create function logDeleteCountry() returns trigger as $func$
begin
    insert into country_log (countryID, countryTitle, command, username, commandDate) values (old.id, old.title, 'Delete', current_user, now());
    return new;
end;
$func$
language plpgsql;

create trigger logCountryDelete after delete on country
for each row execute function logDeleteCountry();

create trigger check_salary_update
    before update of salary on employee
    for each row
    execute function logSalaryUpdate();

select tgname from pg_trigger;

alter table country
disable trigger backupCountry;

alter table country
enable trigger backupCountry;

drop trigger backupCountry

create or replace function fn_test_cursor() returns text
language plpgsql as $func$


    declare cursorSetEmail cursor for select id, firstName, lastName from employee where email is null;

    currentID int;
    currentFirstName varchar(100);
    currentLastName varchar(100);
    
    name varchar(100);
    lastname varchar(100);
    emailAddress varchar(500);


begin


    open cursorSetEmail;
    
    loop
        fetch cursorSetEmail into currentID, currentFirstName, currentLastName;
        
        exit when not found;
        
        name := lower(replace(currentFirstName, ' ', '.'));
        lastname := lower(replace(currentLastName, ' ', '.'));
        emailAddress := concat(name, '.', lastName, '@amazing.com');
        
        raise notice '% % % : %', currentID, currentFirstName, currentLastName, emailAddress;
        
        update employee set email = emailAddress where id = currentID;
        
    end loop;
    
    close cursorSetEmail;
    
    
    return 'Done';
    
end

$func$;

select * from fn_test_cursor();

do
$func$
declare
    username varchar := 'aydan';
begin

    raise exception 'Kullanıcı adı zaten mevcut: "%"', username
        using hint = 'Farklı bir kullanıcı adı giriniz.';
        
end
$func$;

create or replace function getPersonTableCount()
    returns int
as
$func$
    declare personCount integer;
begin
    begin
    
        select count(*) into personCount from persoooonnnn;
        return personCount;
        
    exception
        when others then
            return -1;
    end;
end;
$func$
language plpgsql;

    exception
        when no_data_found then
            return -1;
        when zero_divide then
            return -2;
        when too_many_rows then
            return 5000;

begin transaction;

update account set balance = balance - 500 where id = 1;

update account set balance = balance + 500 where id = 2;

commit transaction;

begin transaction;

update account set balance = balance - 750 where id = 1;

update account set balance = balance + 750 where id = 2;

rollback transaction;