Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm new to PostgreSQL triggers & functions and such.

I've got a table: keywords and each keyword should be unique. So when I try to insert a keyword that already exists, it should simply update the already existing keyword.

With the trigger below, inserting works fine, updating however takes very long - and I've always canceled it after a few minutes so I'm not sure what it would output.

Table: keywords

   Column   |            Type             |                       Modifiers
------------+-----------------------------+-------------------------------------------------------
 id         | integer                     | not null default nextval('keywords_id_seq'::regclass)
 keyword    | character varying(255)      | not null
 views      | integer                     | not null
 visits     | integer                     | not null
 created_at | timestamp without time zone | not null
 updated_at | timestamp without time zone | not null


CREATE FUNCTION update_if_exists() RETURNS trigger AS $keyword$ DECLARE result INTEGER;
BEGIN
    -- Check whether the keyword already exists.
    result = (SELECT count(keyword) FROM keywords WHERE keyword = new.keyword);

    -- If we've got a match run an update statement.
    IF result = 1 THEN
        UPDATE keywords
        SET
            views       = views + new.views,
            visits      = visits + new.visits
        WHERE
            keyword     = new.keyword;

        RETURN NULL;
    END IF;

    RETURN new;
END; $keyword$ LANGUAGE plpgsql;


CREATE TRIGGER id BEFORE INSERT OR UPDATE ON keywords
    FOR EACH ROW EXECUTE PROCEDURE update_if_exists();
share|improve this question

2 Answers 2

up vote 2 down vote accepted

You caused an endless loop by making the trigger BEFORE INSERT OR UPDATE . Use instead:

CREATE FUNCTION update_if_exists()
  RETURNS trigger AS
$keyword$
BEGIN

-- Check whether the keyword alrady exists.
UPDATE keywords
SET    views  = views + NEW.views
      ,visits = visits + NEW.visits
WHERE  keyword = NEW.keyword;

IF FOUND THEN
   RETURN NULL;
ELSE
   RETURN NEW;
END IF;

END
$keyword$ LANGUAGE plpgsql;

CREATE TRIGGER id BEFORE INSERT ON keywords     -- not OR UPDATE!
FOR EACH ROW EXECUTE PROCEDURE update_if_exists();
share|improve this answer
    
Wow, nice catch. There was no reason for me to even use the OR UPDATE, should have noticed this earlier. Thanks! –  Xander Aug 6 '13 at 16:09
    
Works like a charm. Much appreciated. –  Xander Aug 6 '13 at 16:15

First question: Do you have an index on "keyword"? Secondly: you could rewrite your trigger function like the following, which probably narrows the race condition window somewhat (there are much better solutions to solve the latter than this trigger function). I haven't tested this function, so please verify that it works if you choose to use it.

CREATE FUNCTION update_if_exists() RETURNS trigger AS $keyword$
DECLARE
    result          INTEGER;
BEGIN
    UPDATE keywords
    SET view = view + NEW.views,
        visits = visits + NEW.visits
    WHERE keyword = NEW.keyword;

    GET DIAGNOSTICS result = ROW_COUNT;

    IF (NOT result > 0) THEN
        INSERT INTO keywords (keyword, views, visits, created_at, updated_at)
        VALUES (NEW.keyword, NEW.views, NEW.visits, NEW.created_at, NEW.updated_at);

        RETURN NEW;
    END IF;

    RETURN NULL;
END;
$keyword$ LANGUAGE plpgsql;
share|improve this answer
    
Thanks for the comment. I've just created the index, just been busy trying to figure out why the function didn't work as I thought it should. I'll try this out, would you recommend this function over the one @ErwinBrandstetter wrote? –  Xander Aug 6 '13 at 16:07
    
Rule of thumb: If there is a choice between any answer and one by Erwin, choose Erwin's. So to answer your question, use his... –  bma Aug 6 '13 at 16:10
    
Alright. I appreciate your feedback and don't sell yourself short! :) –  Xander Aug 6 '13 at 16:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.