35.10. ����������

Example 35-1. һ��PL/pgSQL����������

��������Ӵ������������ǣ��κ�ʱ����в����������У� ��ǰ���û�����ʱ�䶼��¼�����С� ��������֤�����˹�Ա���Ʋ���нˮ��һ��������

CREATE TABLE emp (
    empname text,
    salary integer,
    last_date timestamp,
    last_user text
);

CREATE FUNCTION emp_stamp () RETURNS trigger AS $emp_stamp$
    BEGIN
        -- ����Ƿ������ empname �� salary
        IF NEW.empname ISNULL THEN
            RAISE EXCEPTION 'empname cannot be null';
        END IF;
        IF NEW.salary ISNULL THEN
            RAISE EXCEPTION '% cannot have null salary', NEW.empname;
        END IF;

        -- ���DZ��븶�ʸ�˭��
        IF NEW.salary < 0 THEN
            RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
        END IF;

        -- ��ס��ʱ���˵�нˮ���޸���
        NEW.last_date := 'now';
        NEW.last_user := current_user;
        RETURN NEW;
    END;
$emp_stamp$ LANGUAGE plpgsql;

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE PROCEDURE emp_stamp();

Example 35-2. һ��������Ƶ� PL/pgSQL ����������

������Ӵ�������֤���� emp ���ϵ��κβ��룬 ���»���ɾ������������¼���� emp_audit ���Ҳ���ǣ���ƣ��� ��ǰʱ����û����ᱻ��¼����������Լ�����ִ�еIJ�����

CREATE TABLE emp (
    empname           text NOT NULL,
    salary            integer
);

CREATE TABLE emp_audit(
    operation         char(1)   NOT NULL,
    stamp             timestamp NOT NULL,
    userid            text      NOT NULL,
    empname           text      NOT NULL,
    salary integer
);

CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
    BEGIN
        --
        -- �� emp_audit �ﴴ��һ�У���ӳ�� emp �IJ�����
        -- ʹ��������� TG_OP ��ȡ�������͡�
        --
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
            RETURN NEW;
        END IF;
        RETURN NULL; -- ���Խ������Ϊ���Ǹ� AFTER ������
    END;
$emp_audit$ language plpgsql;

CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
    FOR EACH ROW EXECUTE PROCEDURE process_emp_audit()
;

Example 35-3. һ��ά����Ҫ��� PL/pgSQL ����������

�����ģʽ��һ�����ǻ��� Ralph Kimball ��The Data Warehouse Toolkit ����� Grocery Store ���ӡ�

--
-- ���� - ʱ��ά�Լ�������ʵ��
--
CREATE TABLE time_dimension (
    time_key                    integer NOT NULL,
    day_of_week                 integer NOT NULL,
    day_of_month                integer NOT NULL,
    month                       integer NOT NULL,
    quarter                     integer NOT NULL,
    year                        integer NOT NULL
);
CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);

CREATE TABLE sales_fact (
    time_key                    integer NOT NULL,
    product_key                 integer NOT NULL,
    store_key                   integer NOT NULL,
    amount_sold                 numeric(12,2) NOT NULL,
    units_sold                  integer NOT NULL,
    amount_cost                 numeric(12,2) NOT NULL
);
 CREATE INDEX sales_fact_time ON sales_fact(time_key);

--
-- ժҪ�� - ����ʱ������ۡ�
--
CREATE TABLE sales_summary_bytime (
    time_key                    integer NOT NULL,
    amount_sold                 numeric(15,2) NOT NULL,
    units_sold                  numeric(12) NOT NULL,
    amount_cost                 numeric(15,2) NOT NULL
);
 CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);

--
-- �� UPDATE��INSERT��DELETE ��ʱ����¸�Ҫ�ֶεĺ����ʹ�������
--
CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER AS $maint_sales_summary_bytime$
    DECLARE
        delta_time_key          integer;
        delta_amount_sold       numeric(15,2);
        delta_units_sold        numeric(12);
        delta_amount_cost       numeric(15,2);
    BEGIN

        -- ������/������
        IF (TG_OP = 'DELETE') THEN

            delta_time_key = OLD.time_key;
            delta_amount_sold = -1 * OLD.amount_sold;
            delta_units_sold = -1 * OLD.units_sold;
            delta_amount_cost = -1 * OLD.amount_cost;

        ELSIF (TG_OP = 'UPDATE') THEN

            -- ��ֹ�ı� time_key �ĸ��� -
            -- �����ܲ����Ǻ�ǿ�ƣ���Ϊ DELETE + INSERT �Ǵ��������
            -- �������޸ģ���
            IF ( OLD.time_key != NEW.time_key) THEN
                RAISE EXCEPTION 'Update of time_key : % -> % not allowed', OLD.time_key, NEW.time_key;
            END IF;

            delta_time_key = OLD.time_key;
            delta_amount_sold = NEW.amount_sold - OLD.amount_sold;
            delta_units_sold = NEW.units_sold - OLD.units_sold;
            delta_amount_cost = NEW.amount_cost - OLD.amount_cost;

        ELSIF (TG_OP = 'INSERT') THEN

            delta_time_key = NEW.time_key;
            delta_amount_sold = NEW.amount_sold;
            delta_units_sold = NEW.units_sold;
            delta_amount_cost = NEW.amount_cost;

        END IF;


        -- ������ֵ���¸�Ҫ�С�
        UPDATE sales_summary_bytime
            SET amount_sold = amount_sold + delta_amount_sold,
                units_sold = units_sold + delta_units_sold,
                amount_cost = amount_cost + delta_amount_cost
            WHERE time_key = delta_time_key;


        -- There might have been no row with this time_key (e.g new data!).
        IF (NOT FOUND) THEN
            BEGIN
                INSERT INTO sales_summary_bytime (
                            time_key,
                            amount_sold,
                            units_sold,
                            amount_cost)
                    VALUES (
                            delta_time_key,
                            delta_amount_sold,
                            delta_units_sold,
                            delta_amount_cost
                           );
            EXCEPTION
                --
                -- ������������άһ���� time_key �������ݵij�ͻ����
                --
                WHEN UNIQUE_VIOLATION THEN
                    UPDATE sales_summary_bytime
                        SET amount_sold = amount_sold + delta_amount_sold,
                            units_sold = units_sold + delta_units_sold,
                            amount_cost = amount_cost + delta_amount_cost
                        WHERE time_key = delta_time_key;

            END;
        END IF;
        RETURN NULL;

    END;
$maint_sales_summary_bytime$ LANGUAGE plpgsql;

CREATE TRIGGER maint_sales_summary_bytime
AFTER INSERT OR UPDATE OR DELETE ON sales_fact
    FOR EACH ROW EXECUTE PROCEDURE maint_sales_summary_bytime();