PL/pgSQL funksiyalari va triggerlar

CREATE TABLE xodim (
    id      int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ism     text NOT NULL,
    lavozim text NOT NULL,
    maosh   numeric(10,2) NOT NULL CHECK (maosh > 0)
);

INSERT INTO xodim (ism, lavozim, maosh) VALUES
    ('Husanboy', 'dasturchi',  9000000.00),
    ('Malika',   'tahlilchi',  7500000.00),
    ('Nodira',   'dasturchi', 10500000.00),
    ('Aziza',    'menejer',    6800000.00);

CREATE FUNCTION yillik_maosh(oylik numeric)
RETURNS numeric
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT oylik * 12;
$$;

SELECT ism, maosh, yillik_maosh(maosh) AS yillik
FROM xodim
ORDER BY id;

CREATE FUNCTION maosh_darajasi(qiymat numeric)
RETURNS text
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
    IF qiymat >= 10000000 THEN
        RETURN 'yuqori';
    ELSIF qiymat >= 7000000 THEN
        RETURN 'orta';
    ELSE
        RETURN 'boshlangich';
    END IF;
END;
$$;

SELECT ism, maosh, maosh_darajasi(maosh) AS daraja
FROM xodim
ORDER BY maosh DESC;

CREATE FUNCTION lavozim_royxati()
RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
    natija text := '';
    qator  record;
BEGIN
    FOR qator IN
        SELECT lavozim, count(*) AS soni
        FROM xodim GROUP BY lavozim ORDER BY lavozim
    LOOP
        natija := natija || qator.lavozim || '=' || qator.soni || ' ';
    END LOOP;

    RETURN trim(natija);
END;
$$;

SELECT lavozim_royxati();

CREATE FUNCTION maoshi_kop(chegara numeric)
RETURNS TABLE (ism text, lavozim text, maosh numeric)
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
    RETURN QUERY
        SELECT x.ism, x.lavozim, x.maosh
        FROM xodim x
        WHERE x.maosh > chegara
        ORDER BY x.maosh DESC;
END;
$$;

SELECT * FROM maoshi_kop(7000000);

ALTER TABLE xodim
    ADD COLUMN yangilangan timestamptz NOT NULL DEFAULT now();

CREATE FUNCTION vaqtni_yangila()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.yangilangan := now();
    RETURN NEW;
END;
$$;

CREATE TRIGGER xodim_vaqt
    BEFORE UPDATE ON xodim
    FOR EACH ROW
    EXECUTE FUNCTION vaqtni_yangila();

UPDATE xodim SET maosh = maosh + 500000 WHERE ism = 'Malika';

SELECT ism, maosh, yangilangan > now() - interval '1 minute' AS hozir
FROM xodim
WHERE ism IN ('Malika', 'Aziza')
ORDER BY ism;

CREATE TABLE maosh_tarixi (
    id        int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    xodim_id  int NOT NULL,
    eski      numeric(10,2),
    yangi     numeric(10,2),
    kim       text NOT NULL
);

CREATE FUNCTION maoshni_yoz()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.maosh IS DISTINCT FROM OLD.maosh THEN
        INSERT INTO maosh_tarixi (xodim_id, eski, yangi, kim)
        VALUES (OLD.id, OLD.maosh, NEW.maosh, current_user);
    END IF;

    RETURN NULL;
END;
$$;

CREATE TRIGGER xodim_maosh_audit
    AFTER UPDATE ON xodim
    FOR EACH ROW
    EXECUTE FUNCTION maoshni_yoz();

UPDATE xodim SET maosh = 11000000 WHERE ism = 'Nodira';
UPDATE xodim SET lavozim = 'katta menejer' WHERE ism = 'Aziza';

SELECT xodim_id, eski, yangi FROM maosh_tarixi ORDER BY id;