Trigger after insert, update a related model Postgresql

Question

This is the first time I'm creating a trigger, so I've got a little bit confused. I'm following this guide.

Here is what I've done so far:

DROP TRIGGER IF EXISTS "update_metas" ON "post";
CREATE TRIGGER "update_metas"
    AFTER INSERT ON "post"
    FOR EACH ROW EXECUTE PROCEDURE update_post_count();

I have two tables: user and post. What I need to do is to increment the column user.postCount for each new post created. The foreign key is post.user_id

The procedure I'm creating is the following:

CREATE FUNCTION update_post_count() RETURNS TRIGGER AS $updates_user_postCount$
    BEGIN
        -- I know that NEW contains the new post info, so I
        -- can gather the user_id by doing NEW.post_id.
        -- 
        -- What exactly should I do here?
        RETURN NEW;
    END;
$updates_user_postCount$ LANGUAGE plpgsql;

How should I structure this procedure? Can I just use a direct SQL query, something like:

UPDATE "user"
    SET "user"."post_count" = "user"."post_count" + 1
    WHERE "user"."_id" = NEW.idol_id;

UPDATE

I've tried using that SQL statement inside the procedure, but it returns the error error: column "user" of relation "user" does not exist.

Here is the SQL statement that I used to create both user and post tables:

CREATE TABLE IF NOT EXISTS "user" (
    _id BIGSERIAL UNIQUE,
    __id TEXT UNIQUE,
    fbid VARCHAR(100),
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(512) NOT NULL,
    profile_picture VARCHAR(512),
    profile_cover VARCHAR(512),
    profile_about TEXT,
    profile_birth_date BIGINT,
    social_facebook VARCHAR(256),
    social_twitter VARCHAR(256),
    social_instagram VARCHAR(256),
    post_count BIGINT,
    highlighted BOOLEAN,
    idol BOOLEAN,
    free BOOLEAN,
    blocked BOOLEAN
);

CREATE TABLE IF NOT EXISTS "post" (
    _id BIGSERIAL UNIQUE,
    __id TEXT UNIQUE,
    idol_id BIGINT,
    removed BOOLEAN,
    free BOOLEAN,
    created_at BIGINT,
    hashtags VARCHAR(1024),
    audio_src VARCHAR(512),
    audio_size INTEGER,
    audio_length INTEGER,
    FOREIGN KEY ("idol_id") REFERENCES "user"("_id")
);

score 1 · Accepted Answer · answered May 29 '15 at 02:58

1

Your trigger function is largely correct. The only problem is that an UPDATE statement cannot use the table.column notation.

From the documentation: Do not include the table's name in the specification of a target column — for example, UPDATE tab SET tab.col = 1 is invalid.

CREATE FUNCTION update_post_count() RETURNS TRIGGER AS $updates_user_postCount$
BEGIN
  UPDATE "user"
  SET "post_count" = "post_count" + 1
  WHERE "_id" = NEW.idol_id;
  RETURN NEW;
END;
$updates_user_postCount$ LANGUAGE plpgsql;

answered May 29 '15 at 02:58

Patrick

29,357
6
62
90

I've added to your answer about the usage of `FOR EACH ROW` instead of `FOR EACH STATEMENT`, otherwise the `NEW` is NULL. See: http://stackoverflow.com/questions/11001118/postgres-trigger-after-insert-accessing-new – Maurício Giordano May 29 '15 at 03:04

Trigger after insert, update a related model Postgresql

1 Answers1