SQL input - how to update a reference table when new input?-CodePudding

I've got a table where I'm inputting new doses as they arrive.

I want to have a reference table so it doesn't get messy over time for what is being inputted, but am confused about how to write the insert statements to accommodate for this.

The Schema is something like this,

-- Drop table

-- DROP TABLE public.users;

CREATE TABLE users (
    id serial NOT NULL,
    user_id varchar(50) NOT NULL,
    CONSTRAINT users_pkey PRIMARY KEY (user_id)
);


-- Drop table

-- DROP TABLE public.devices;

CREATE TABLE devices (
    id int4 NOT NULL,
    devices varchar NOT NULL,
    CONSTRAINT devices_pk PRIMARY KEY (id)
);
CREATE INDEX devices_id_idx ON devices (id int4_ops);


-- Drop table

-- DROP TABLE public.substances;

CREATE TABLE substances (
    id int4 NOT NULL,
    "input" varchar(255) NOT NULL,
    CONSTRAINT substances_pk PRIMARY KEY (id)
);
CREATE UNIQUE INDEX substances_id_idx ON substances (id int4_ops);


-- Drop table

-- DROP TABLE public.substanceactual;

CREATE TABLE substanceactual (
    id int4 NOT NULL,
    user_id varchar NOT NULL,
    "input" int4 NOT NULL,
    dose float4 NULL,
    device int4 NOT NULL,
    duration float4 NULL,
    timetaken timestamptz NOT NULL,
    "timestamp" timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT substanceactual_pk PRIMARY KEY (id),
    CONSTRAINT substanceactual_fk FOREIGN KEY (user_id) REFERENCES users(user_id),
    CONSTRAINT substanceactual_fk_1 FOREIGN KEY (input) REFERENCES substances(id),
    CONSTRAINT substanceactual_fk_2 FOREIGN KEY (device) REFERENCES devices(id)
);
CREATE INDEX substanceactual_id_idx ON substanceactual USING btree (id);

An example input would be,

INSERT INTO substanceactual (user_id,"input",dose,device,duration)
    VALUES ('xxax',"Dexamphetamine",15.0,1,4.0,'2016-01-25 10:10:10.555555-05:00','2016-01-25 10:10:10.555555-05:00');

How would I make it so when it inserts, it creates a new value, or uses the existing on in the table for substances?

CodePudding user response：

I changed a bit your schema for the purpose of simplicity, as other tables are not involved in the insert process. If I understood you correctly, your aim is to compare "input" field.

By using trigger before insert you can check if the row is already presented in your table, if that is the case you would update it will new values; in other case you would insert new row.

All code:

CREATE TABLE substances (
    id int4 NOT NULL,
    "input" varchar(255) NOT NULL,
    CONSTRAINT substances_pk PRIMARY KEY (id)
);

CREATE TABLE substanceactual (
    id int4 NOT NULL,
    user_id varchar NOT NULL,
    "input" int4 not null,
    dose float4 NULL,
    device int4 NOT NULL,
    duration float4 NULL,
    timetaken timestamptz NOT NULL,
    "timestamp" timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT substanceactual_pk PRIMARY KEY (id),
    CONSTRAINT substanceactual_fk_1 FOREIGN KEY (input) REFERENCES substances(id)
);


insert into substances
values 
(
    1, 'Dexamphetamine'
)


INSERT INTO substanceactual (id, user_id, input, dose, device, duration, timetaken, timestamp)
VALUES 
(
    1,
    'xxax',
    1,
    7.0, -- old
    1,
    2.0, -- old
    '2016-01-10 10:10:10.555555-05:00', -- old
    '2016-01-15 10:10:10.555555-05:00' -- old
);

CREATE OR REPLACE FUNCTION on_before_insert_input() 
    RETURNS trigger
    LANGUAGE plpgsql 
AS
$$
BEGIN
    --to escape endless recursion
    IF pg_trigger_depth() = 1 THEN
        
        --if row with the same input exists, it will update it, otherwise it will insert a new one
        IF EXISTS (SELECT 1 FROM substanceactual WHERE input = NEW.input) THEN
                UPDATE substanceactual
                SET 
                    --I excluded new.id on purpose, you can update it also if you don't identity set for you id column
                    user_id = NEW.user_id,
                    dose = NEW.dose,
                    device = NEW.device,
                    duration = NEW.duration,
                    timetaken = NEW.timetaken,
                    timestamp = NEW.timestamp
                WHERE input = NEW.input;
        ELSE
                INSERT INTO substanceactual (id, user_id, input, dose, device, duration, timetaken, timestamp) 
                VALUES 
                (
                    NEW.id,
                    NEW.user_id,
                    NEW.input,
                    NEW.dose,
                    NEW.device,
                    NEW.duration,
                    NEW.timetaken,
                    NEW.timestamp
                );
        END IF;
        RETURN NULL;
    ELSE
        RETURN NEW;
    END IF;
END;
$$;

CREATE OR REPLACE TRIGGER input_trigger BEFORE INSERT ON substanceactual
FOR EACH ROW EXECUTE PROCEDURE on_before_insert_input();

select * from substanceactual;

INSERT INTO substanceactual (id, user_id, input, dose, device, duration, timetaken, timestamp)
VALUES 
(
    2,
    'xxax',
    1,
    15, -- new
    1,
    4, -- new
    '2016-01-25 10:10:10.555555-05:00', -- new
    '2016-01-25 10:10:10.555555-05:00' -- new
);

select * from substanceactual;