Postgresql Schemma with triggers and procedures

example_postgres.sql

%%Create a tablespace
CREATE SCHEMA  IF NOT EXISTS stations AUTHORIZATION david;
\dt stations.*
#DEfault:
SET search_path = stations;

CREATE TABLE IF NOT EXISTS stations.equipment(
    id bigserial PRIMARY KEY ,
    name varchar(50),
    model varchar(50),
    serie varchar(100) UNIQUE,
    brand varchar(50),
    n_inputs integer,
    n_outputs integer
)
WITH (OIDS = TRUE);

COMMENT ON TABLE stations.equipment IS 'Equipment description and main features';

CREATE TABLE IF NOT EXISTS stations.eq_output(
    id bigserial PRIMARY KEY,
    id_eq integer references stations.equipment (id) ON UPDATE CASCADE ON DELETE CASCADE,
    label varchar(50),
    active boolean
)
WITH (OIDS = TRUE);

COMMENT ON TABLE stations.eq_output IS 'Output caracterization from equipment';


CREATE TABLE IF NOT EXISTS stations.eq_input(
    id bigserial PRIMARY KEY,
    id_out integer references stations.eq_output (id) ON UPDATE CASCADE ON DELETE CASCADE,
    id_eq integer references stations.equipment (id) ON UPDATE CASCADE ON DELETE CASCADE,
    label varchar(50),
    active boolean
)
WITH (OIDS = TRUE);

COMMENT ON TABLE stations.eq_input IS 'Input caracterization from equipment';


CREATE TABLE IF NOT EXISTS stations.eq_status(
    id bigserial PRIMARY KEY ,
    id_eq integer references stations.equipment (id) ON UPDATE CASCADE ON DELETE CASCADE,
    cant_out integer,
    cant_in integer,
    st_date timestamp with time zone default current_timestamp
)
WITH (OIDS = TRUE);

COMMENT ON TABLE stations.eq_status IS 'Status connection in/out in equipment';


#====================================================
#Trigger para cuando se añade nuevo equipo
#Aun no se han especificado input outputs
#Solo se crea instancia inicializadora

CREATE OR REPLACE FUNCTION status_monitor () RETURNS trigger  
AS $status_mon$
    #Create a new instance in status for eq_id and 0 values in cant_(in,out)
    #https://www.postgresql.org/docs/9.5/static/plpython-trigger.html
    #Trigger variables:
    TG_OP=TD["event"]
    NEW=TD["new"]
    OLD=TD["old"]
    WHEN=TD["when"]        
    if (TG_OP == 'DELETE'):
        plan=plpy.prepare("INSERT INTO stations.eq_status (id_eq,cant_out,cant_in) VALUES ($1,0,0)", ["int4"])
        rv = plpy.execute(plan,[OLD["id"]]) 
    elif (TG_OP == 'UPDATE'):
        plan_cout=plpy.prepare("SELECT count(*) FROM stations.eq_output WHERE id_eq=$1 and active=TRUE", ["int4"])
        rv_cout=plpy.execute(plan_out,NEW["id"])
        plan_cin=plpy.prepare("SELECT count(*) FROM stations.eq_input WHERE id_eq=$1 and active=TRUE",["int4"])
        rv_cin=plpy.execute(plan_cin,NEW["id"])
        plan=plpy.prepare("INSERT INTO stations.eq_status (id_eq, cant_out, cant_in) VALUES ($1, $2,$3)",["int4","int4","int4"])
        rv=plpy.execute(plan,[NEW["id"],rv_cout,rv_cin])
    elif (TG_OP == 'INSERT'):
        plan=plpy.prepare("INSERT INTO stations.eq_status (id_eq,cant_out,cant_in) VALUES ($1,0,0)", ["int4"])
        rv=plpy.execute(plan, [NEW["id"]]) 
$status_mon$ LANGUAGE plpython3u;

CREATE TRIGGER status_monitor AFTER INSERT OR UPDATE OR DELETE ON stations.equipment FOR EACH ROW EXECUTE PROCEDURE status_monitor ();
#Example: add an equipment:
INSERT INTO stations.equipment (name, model, serie, brand, n_inputs, n_outputs) VALUES (GPS-CCSN, NetR9, 12312s12321r, Trimble, 3,2 )
#====================================================

CREATE OR REPLACE FUNCTION status_out_monitor () RETURNS trigger  
AS $status_output$
    #--
    #--Create a new instance in status for an output modification
    #--
    TG_OP=TD["event"]
    NEW=TD["new"]
    OLD=TD["old"]
    WHEN=TD["when"] 
    if (TG_OP == 'DELETE'):    
        plan=plpy.prepare("INSERT INTO stations.eq_status (id_eq, cant_out, cant_in) VALUES (OLD.id_eq,SELECT COUNT(*) FROM stations.eq_output WHERE id_eq=$1 and active=True, SELECT COUNT(*) FROM stations.eq_input WHERE id_eq=$1 and active=True)", ["int4"])
        rv = plpy.execute(plan,[OLD["id_eq"]]) 
    elif (TG_OP == 'UPDATE'):
        plan_cout=plpy.prepare("SELECT count(*) FROM stations.eq_output WHERE id_eq=$1 and active=TRUE", ["int4"])
        rv_cout=plpy.execute(plan_cout,[NEW["id_eq"]])
        plan_cin=plpy.prepare("SELECT count(*) FROM stations.eq_input WHERE id_eq=$1 and active=TRUE",["int4"])
        rv_cin=plpy.execute(plan_cin,[NEW["id_eq"]])
        plan=plpy.prepare("INSERT INTO stations.eq_status (id_eq, cant_out, cant_in) VALUES ($1, $2,$3)",["int4","int4","int4"])
        rv=plpy.execute(plan,[NEW["id_eq"],rv_cout[0]['count'],rv_cin[0]['count']])
    elif (TG_OP == 'INSERT'):
        plan=plpy.prepare("INSERT INTO stations.eq_status (id_eq,cant_out,cant_in) VALUES ($1,$2,$3)", ["int4","int4","int4"])
        #count in out:
        plan_cout=plpy.prepare("SELECT count(*) FROM stations.eq_output WHERE id_eq=$1 and active=TRUE", ["int4"])
        rv_cout=plpy.execute(plan_cout,[NEW["id_eq"]])
        plan_cin=plpy.prepare("SELECT count(*) FROM stations.eq_input WHERE id_eq=$1 and active=TRUE",["int4"])
        rv_cin=plpy.execute(plan_cin,[NEW["id_eq"]])
        rv=plpy.execute(plan, [NEW["id_eq"],rv_cout[0]['count'],rv_cin[0]['count']]) 
$status_output$ LANGUAGE plpython3u;

CREATE TRIGGER status_output
AFTER INSERT OR UPDATE OR DELETE ON stations.eq_output
FOR EACH ROW EXECUTE PROCEDURE status_out_monitor ();
#====================================================

CREATE OR REPLACE FUNCTION status_in_monitor () RETURNS trigger  
AS $status_input$
    #--
    #--Create a new instance in status for an output modification
    #--
    TG_OP=TD["event"]
    NEW=TD["new"]
    OLD=TD["old"]
    WHEN=TD["when"] 
    if (TG_OP == 'DELETE'):    
        plan=plpy.prepare("INSERT INTO stations.eq_status (id_eq, cant_out, cant_in) VALUES (OLD.id_eq,SELECT COUNT(*) FROM stations.eq_output WHERE id_eq=$1 and active=True, SELECT COUNT(*) FROM stations.eq_input WHERE id_eq=$1 and active=True)", ["int4"])
        rv = plpy.execute(plan,[OLD["id_eq"]]) 
    elif (TG_OP == 'UPDATE'):
        plan_cout=plpy.prepare("SELECT count(*) FROM stations.eq_output WHERE id_eq=$1 and active=TRUE", ["int4"])
        rv_cout=plpy.execute(plan_cout,[NEW["id_eq"]])
        plan_cin=plpy.prepare("SELECT count(*) FROM stations.eq_input WHERE id_eq=$1 and active=TRUE",["int4"])
        rv_cin=plpy.execute(plan_cin,[NEW["id_eq"]])
        plan=plpy.prepare("INSERT INTO stations.eq_status (id_eq, cant_out, cant_in) VALUES ($1, $2,$3)",["int4","int4","int4"])
        rv=plpy.execute(plan,[NEW["id_eq"],rv_cout[0]['count'],rv_cin[0]['count']])
    elif (TG_OP == 'INSERT'):
        plan=plpy.prepare("INSERT INTO stations.eq_status (id_eq,cant_out,cant_in) VALUES ($1,$2,$3)", ["int4","int4","int4"])
        #count in out:
        plan_cout=plpy.prepare("SELECT count(*) FROM stations.eq_output WHERE id_eq=$1 and active=TRUE", ["int4"])
        rv_cout=plpy.execute(plan_cout,[NEW["id_eq"]])
        plan_cin=plpy.prepare("SELECT count(*) FROM stations.eq_input WHERE id_eq=$1 and active=TRUE",["int4"])
        rv_cin=plpy.execute(plan_cin,[NEW["id_eq"]])
        rv=plpy.execute(plan, [NEW["id_eq"],rv_cout[0]['count'],rv_cin[0]['count']]) 
$status_input$ LANGUAGE plpython3u;

CREATE TRIGGER status_input
AFTER INSERT OR UPDATE OR DELETE ON stations.eq_input
FOR EACH ROW EXECUTE PROCEDURE status_in_monitor ();
#====================================================



DROP FUNCTION stations.check_io_eqs (eid integer);

DROP TABLE stations.eq_input;
DROP TABLE stations.eq_output;
DROP TABLE stations.eq_status;
DROP TABLE stations.equipment CASCADE;