title: Adding logic to a PostgreSQL stored procedure
body: <p>I'm using PostgreSQL (8.3+) and have defined an enum and a table as follows:</p>
  
  <pre><code>CREATE TYPE "viewer_action" AS ENUM ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H');
  
  CREATE TABLE "preferences"  ( 
      "user_id"       integer NOT NULL,
      "item_id"       integer NOT NULL,
      "rating"        viewer_action NOT NULL,
      "time_created"  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY ("user_id","video_id")
  );
  </code></pre>
  
  <p>I've also created a stored procedure to upsert new rows into the preferences table, using the example from 
  <a href="http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE" rel="nofollow">http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE</a>:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION add_preference(u INT, i INT, r viewer_action) RETURNS VOID AS $add_preference$
      BEGIN
          LOOP
              -- first try to update the key
              UPDATE preferences SET rating = r WHERE user_id = u AND item_id = i;
              IF found THEN
                  RETURN;
              END IF;
              -- not there, so try to insert the key
              -- if someone else inserts the same key concurrently,
              -- we could get a unique-key failure
              BEGIN
                  INSERT INTO preferences(user_id,item_id,rating) VALUES (u,i,r);
                  RETURN;
              EXCEPTION WHEN unique_violation THEN
                  -- do nothing, and loop to try the UPDATE again
              END;
          END LOOP;
      END;
  $add_preference$ LANGUAGE plpgsql;
  </code></pre>
  
  <p>I need to add some additional logic to the upsert to prevent some values from overwriting other values. Specifically:</p>
  
  <ul>
  <li>A can be overwritten by B, which can by overwritten by C, which can be overwritten by D, and so on through F. But B cannot by overwritten by A, nor C overwritten by B, etc.</li>
  <li>F, G, or H can overwrite any value, regardless if an existing value is lower or higher.</li>
  </ul>
  
  <p>In pseudocode, this might look like:</p>
  
  <pre><code>if (rating &gt;= F) {
      insert;
  } else if (rating &gt; existing_rating) {
      insert;
  } else {
      return;
  }
  </code></pre>