CODEWITHSUNDEEP
sqlpostgresqldatabase-trigger
Unfolder
Unfolder

Reputation: 1

PostgreSQL - How to keep a column updated

I'm new to SQL and I'm trying to update a column (ex_counter) of a table (ex_table). This column consists of a counter of the number of times an ID (ex_id) appears on a second table (ex2_id in ex2_table).

An ID can be inserted into the second table at any moment. If that ID is already existing, the counter of its corresponding ID in the first table must be updated (by simply adding 1, I guess).

These are the two tables:

CREATE TABLE ex_table(
 ex_id SMALLINT,
 ex_counter SMALLINT;)

CREATE TABLE ex2_table(
 ex2_id SMALLINT;)

I think it should be done more or less like this. The commented code is the pseudocode that I don't know how to implement:

CREATE TRIGGER ex_trigger AFTER
INSERT ON ex2_table
FOR EACH ROW EXECUTE PROCEDURE ex_func();



CREATE FUNCTION ex_func() RETURNS trigger AS $$ BEGIN

/* 
if ex2_id = ex_id 
    ex_counter = ex_counter + 1
*/

RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Thanks in advance!

Upvotes: 0

Views: 32

Answers (1)

Joakim Danielson
Joakim Danielson

Reputation: 52053

Something like this

IF EXISTS (SELECT * FROM ex_table WHERE ex_id = new.ex2_id) THEN
    UPDATE ex_table 
    SET ex_counter = ex_counter + 1
    WHERE ex_id = new.ex2_id
ELSE
    INSERT INTO ex_table VALUES (new.ex2_id, 1)
END IF;

Note that it is no point really to store a counter since you so easily can retrieve the value by doing a SELECT COUNT()...

Upvotes: 1

Related Questions