LINUX.ORG.RU

История изменений

Исправление annerleen, (текущая версия) :

Максимум, на что меня хватило в нашем недо-продакшене — это триггер на INSERT с процедуркой на pl/pgsql, которая передаёт это на обработку дальше

CREATE OR REPLACE FUNCTION cdr_to_json() RETURNS TRIGGER AS $$

    DECLARE 
        data json;

    BEGIN
    

        IF (TG_OP = 'INSERT') THEN
            data = row_to_json(NEW);
            INSERT INTO cdr_to_upload(data) values(data);
        END IF;

        -- Result is ignored since this is an AFTER trigger
        RETURN NULL; 
    END;
    
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION cdr_upload() RETURNS TRIGGER AS $$

    DECLARE 
        url      varchar := 'http://localhost';
        apikey   varchar := '';
        data     varchar;
        result   varchar;

        cdr cdr_to_upload%rowtype;

    BEGIN
    

        IF (TG_OP = 'INSERT') THEN

                    FOR cdr IN SELECT * FROM cdr_to_upload LOOP

                        data = CAST(cdr.data As text);
                        result := (SELECT content FROM http_post(billing_url, data, 'application/x-www-form-urlencoded'));
                        result := SUBSTR(result, 1, 4);

                        IF (result = 'true') THEN
                                        DELETE from cdr_to_upload WHERE id = cdr.id;
                        END IF;

                    END LOOP;

        END IF;

        -- Result is ignored since this is an AFTER trigger
        RETURN NULL; 
    END;
    
$$ LANGUAGE plpgsql;


create trigger voice_cdr_to_json after insert on tm_voice_cdr for each row execute procedure cdr_to_json();
create trigger sms_cdr_to_json after insert on tm_sms_cdr for each row execute procedure cdr_to_json();
create trigger gprs_cdr_to_json after insert on tm_gprs_cdr for each row execute procedure cdr_to_json();
create trigger cdr_upload_trigger after insert on cdr_to_upload for each row execute procedure cdr_upload();


Обрабатывать данные в БД — изврат, хоть иногда и быстрее.

Исправление annerleen, :

Максимум, на что меня хватило в нашем недо-продакшене — это триггер на INSERT с процедуркой на pl/pgsql, которая передаёт это на обработку дальше

CREATE OR REPLACE FUNCTION cdr_to_json() RETURNS TRIGGER AS $$

    DECLARE 
        data json;

    BEGIN
    

        IF (TG_OP = 'INSERT') THEN
            data = row_to_json(NEW);
            INSERT INTO cdr_to_upload(data) values(data);
        END IF;

        -- Result is ignored since this is an AFTER trigger
        RETURN NULL; 
    END;
    
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION cdr_upload() RETURNS TRIGGER AS $$

    DECLARE 
        apikey      varchar := '';
        billing_url varchar := 'http://localhost/gsmproxy';
        data        varchar;
        result      varchar;

        cdr cdr_to_upload%rowtype;

    BEGIN
    

        IF (TG_OP = 'INSERT') THEN

                    FOR cdr IN SELECT * FROM cdr_to_upload LOOP

                        data = CAST(cdr.data As text);
                        result := (SELECT content FROM http_post(billing_url, data, 'application/x-www-form-urlencoded'));
                                result := SUBSTR(result, 1, 4);

                        IF (result = 'true') THEN
                                        DELETE from cdr_to_upload WHERE id = cdr.id;
                        END IF;

                    END LOOP;

        END IF;

        -- Result is ignored since this is an AFTER trigger
        RETURN NULL; 
    END;
    
$$ LANGUAGE plpgsql;


create trigger voice_cdr_to_json after insert on tm_voice_cdr for each row execute procedure cdr_to_json();
create trigger sms_cdr_to_json after insert on tm_sms_cdr for each row execute procedure cdr_to_json();
create trigger gprs_cdr_to_json after insert on tm_gprs_cdr for each row execute procedure cdr_to_json();
create trigger cdr_upload_trigger after insert on cdr_to_upload for each row execute procedure cdr_upload();


Обрабатывать данные в БД — изврат, хоть иногда и быстрее.

Исходная версия annerleen, :

Максимум, на что меня хватило в нашем недо-продакшене — это триггер на INSERT с процедуркой на pl/pgsql, которая передаёт это на обработку дальше

CREATE OR REPLACE FUNCTION cdr_to_json() RETURNS TRIGGER AS $$

    DECLARE 
        data json;

    BEGIN
    

        IF (TG_OP = 'INSERT') THEN
            data = row_to_json(NEW);
            INSERT INTO cdr_to_upload(data) values(data);
        END IF;

        -- Result is ignored since this is an AFTER trigger
        RETURN NULL; 
    END;
    
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION cdr_upload() RETURNS TRIGGER AS $$

    DECLARE 
        apikey      varchar := '';
        billing_url varchar := 'http://localhost/gsmproxy';
        data        varchar;
        result      varchar;

        cdr cdr_to_upload%rowtype;

    BEGIN
    

        IF (TG_OP = 'INSERT') THEN

                    FOR cdr IN SELECT * FROM cdr_to_upload LOOP

                        data = CAST(cdr.data As text);
                        result := (SELECT content FROM http_post(billing_url, data, 'application/x-www-form-urlencoded'));
                                result := SUBSTR(result, 1, 4);

                        IF (result = 'true') THEN
                                        DELETE from cdr_to_upload WHERE id = cdr.id;
                        END IF;

                    END LOOP;

        END IF;

        -- Result is ignored since this is an AFTER trigger
        RETURN NULL; 
    END;
    
$$ LANGUAGE plpgsql;


create trigger voice_cdr_to_json after insert on tm_voice_cdr for each row execute procedure cdr_to_json();
create trigger sms_cdr_to_json after insert on tm_sms_cdr for each row execute procedure cdr_to_json();
create trigger gprs_cdr_to_json after insert on tm_gprs_cdr for each row execute procedure cdr_to_json();
create trigger cdr_upload_trigger after insert on cdr_to_upload for each row execute procedure cdr_upload();


Обрабатывать данные в БД — изврат, хоть иногда и быстрее.