title: Rails Postgres Insert Duplicate Row Check Issues
body: <p>I'm trying to detect and silently discard any duplicate INSERTs into a table, and if a dupe detected, return the ID (Primary Key) of the existing record. Otherwise, insert the record and return the new ID.</p>
  
  <p>I can do this with either a RULE or a TRIGGER, however both have drawbacks.  Here's an example of my RULE:</p>
  
  <pre><code>CREATE OR REPLACE RULE territory_products_ignore_duplicate_inserts AS
  ON INSERT TO territory_products
  WHERE (EXISTS ( SELECT 1
         FROM territory_products tp
        WHERE tp.territory_id = NEW.territory_id AND tp.product_id = NEW.product_id)) DO INSTEAD SELECT tp.id
         FROM territory_products tp WHERE tp.territory_id = NEW.territory_id AND tp.product_id = NEW.product_id LIMIT 1;
  </code></pre>
  
  <p>Testing this in a SQL console or psql with an INSERT works fine.  If there's a dupe, it'll return the first existing record's id and not do the INSERT. Otherwise, it will go ahead with the INSERT. However, in Rails, it fails and returns this error:</p>
  
  <p>ERROR:  cannot perform INSERT RETURNING on relation "territory_products"
  HINT:  You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause.</p>
  
  <p>Moving to a TRIGGER, I try this:</p>
  
  <pre><code>CREATE OR REPLACE Function territory_products_ignore_dups() Returns Trigger
  As $$
  Begin
  If Exists (
      Select id From territory_products tp
      Where tp.territory_id = NEW.territory_id And tp.product_id = NEW.product_id            
  ) Then
      Return NULL;
  End If;
  Return NEW;
  End;
  $$ Language plpgsql;
  
  Create Trigger territory_products_ignore_dups
  Before Insert On territory_products
  For Each Row
  Execute Procedure territory_products_ignore_dups();
  </code></pre>
  
  <p>This also works fine, except that I can't get it to return the existing ID, because of the Return NULL (which is required to disallow the INSERT).</p>
  
  <p>Can anyone resolve either of these issues, so I get the result I'm looking for? (e.g. silently discard the INSERT in case of a dupe and return the ID of the existing record.  Or if the INSERT is successful, return the ID of the new record).</p>