title: postgresql trigger function - duplicates or missing data
body: <p>A postgresql 9.1 DB writes data which is sent automatically from a machine. This works fine.</p>
  
  <p>I have an AFTER INSERT ...FOR EACH ROW trigger - this also works.
  The trigger works by looking at another table and if there is a value in a field it fires.</p>
  
  <p>I have two problems -
  a. Some machines send more than 1 result. I am using LIMIT 1 to prevent duplicates and this means that I miss data from a machine that sends more than one result. What is an alternative to this?
  b. Some machines share the same test code and I can`t find a way of qualifying this, to make it machine specific in the SELECT when I use the NEW value. Here is the code</p>
  
  <pre><code>CREATE FUNCTION testcode_matches()
      RETURNS TRIGGER as $$
  DECLARE
      var INTEGER;
      name text;
      short text;
      id integer;
  BEGIN
      SELECT count("TestID") from testcode WHERE "testcode"."Parameter" = NEW."Parameter" into var;
      IF var &gt; 0 THEN
          SELECT "TestName", "ShortTestName", "TestID" 
          from testcode where "Parameter" = NEW."Parameter" Limit 1 into name, short, id;
  
          INSERT INTO finaldata /* various fields */
          SELECT /* various fields */ name, short, id
          from obx 
          WHERE "obx"."Parameter" = NEW."Parameter"
          LIMIT 1;
      END if;
      RETURN NEW;
  END;
  $$ language plpgsql;
  </code></pre>