Postgresql trigger function working but producing duplicates

Question

Three Postgresql tables (Windows V 9.1.3) The table obx is a dynamic table that recieves data from several machines. We want to produce an after insert trigger function The table testcode is a fixed table, which contains values we are trying to match to the obx table. If testcode TestID an integer field is not null, we want to insert the data into a new table finaldata using an after insert trigger on the table obx.

The first trigger works, but it produces duplicate data. The trigger has to contain "LIMIT 3" as one of the machines send three results at a time. Trying second option using SQL only but not working.

The fields marked XY in the table finaldata are for internal use.

    CREATE TABLE "public"."obx" (
"obxID" serial primary key,
"Pid" varchar,
"Sid" varchar,
"SidOrig" varchar,
"Parameter" varchar,
"Result" varchar,
"ResultOrig" varchar,
"Units" varchar,
"RefRange" varchar,
"Flag" varchar,
"FlagOrig" varchar,
"OperatorID" varchar,
"ObsTime" char(14),
"MsgTime" char(14),
"UnixTime" int4,
"Analyzer" varchar,
"Segment" varchar
)
;

CREATE TABLE "public"."testcode" (
"TcodeID" serial primary key,
"Analyzer" varchar,
"Parameter" varchar,
"TestName" varchar,
"ShortTestName" varchar,
"TestID" int4
)
;

CREATE TABLE "public"."finaldata" (
"FdataID" serial primary key,
"Pid" varchar,
"Sid" varchar,
"SidOrig" varchar,
"Parameter" varchar,
"Result" varchar,
"ResultOrig" varchar,
"Units" varchar,
"OperatorID" varchar,
"ObsTime" varchar,
"MsgTime" varchar,
"Analyzer" varchar,
"TestName" varchar,
"ShortTestName" varchar,
"TestID" varchar,
"XYchar1" varchar,
"XYchar2" varchar,
"XYchar3" varchar,
"XYint1" int4,
"XYint2" int4,
"XYint3" int4,
"XYGuid" uuid
)
;

  DECLARE
  var INTEGER;
  name text;
  short text;
  id integer;
  BEGIN
  SELECT count("TestID") from testcode WHERE "testcode"."Parameter" = NEW."Parameter" into var;
  IF var > 0 THEN
  SELECT "TestName", "ShortTestName", "TestID" from testcode where "Parameter" = NEW."Parameter" Limit 1 into name, short, id;
  INSERT INTO finaldata ("Pid", "Sid", "SidOrig", "Parameter", "Result", "ResultOrig", "Units", "OperatorID", "ObsTime", "MsgTime", "Analyzer", "TestName", "ShortTestName", "TestID") 
  SELECT "Pid", "Sid", "SidOrig", "Parameter", "Result", "ResultOrig", "Units", "OperatorID", "ObsTime", "MsgTime", "Analyzer", name, short, id
  from obx WHERE "obx"."Parameter" = NEW."Parameter"
  LIMIT 3;
  END if;
  RETURN NEW;
  END;


  ----------------------------
  -- Triggers structure for table "public"."obx"
  -- ----------------------------
  CREATE TRIGGER finaldata_ins
  AFTER INSERT ON obx
  FOR EACH ROW
  EXECUTE PROCEDURE testcode_matches()
  ;


  CREATE FUNCTION testcode_matches()
    RETURNS TRIGGER AS $meat$
  BEGIN
      INSERT INTO finaldata ("Pid", "Sid", "SidOrig", "Parameter", "Result", "ResultOrig", "Units", "OperatorID", "ObsTime", "MsgTime", "Analyzer", "TestName", "ShortTestName", "TestID")
      SELECT "obx"."Pid", "obx"."Sid", "obx"."SidOrig", "obx"."Parameter", "obx"."Result", "obx"."ResultOrig", "obx"."Units", "obx"."OperatorID", "obx"."ObsTime", "obx"."MsgTime", "obx"."Analyzer", "TestName", "ShortTestName", "TestID"
      FROM obx testcode
      JOIN obx ON "obx"."Parameter" = "testcode"."Parameter"
      WHERE "testcode"."Parameter" = NEW."Parameter"
      AND "testcode"."TestID" = NEW."TestID"
          ;
      RETURN NEW;
  END;
  $meat$
    LANGUAGE plpgsql;


     ----------------------------
    -- Triggers structure for table "public"."obx"
    -- ----------------------------
    CREATE TRIGGER finaldata_ins
    AFTER INSERT ON obx
    FOR EACH ROW
    EXECUTE PROCEDURE testcode_matches()
    ;

Answer 1

It seems you were confusing yourself by giving obx the correlation name "testcode" :

TESTTABLES:

   SET search_path='tmp';
   DROP TABLE "obx" CASCADE;
   CREATE TABLE "obx" (
    "obxID" serial primary key,
    "Pid" varchar,
    "Sid" varchar,
    "SidOrig" varchar,
    "Parameter" varchar,
    "Result" varchar,
    "ResultOrig" varchar,
    "Units" varchar,
    "RefRange" varchar,
    "Flag" varchar,
    "FlagOrig" varchar,
    "OperatorID" varchar,
    "ObsTime" char(14),
    "MsgTime" char(14),
    "UnixTime" int4,
    "Analyzer" varchar,
    "Segment" varchar
    );

DROP TABLE "testcode" CASCADE;
CREATE TABLE "testcode" (
    "TcodeID" serial primary key,
    "Analyzer" varchar,
    "Parameter" varchar,
    "TestName" varchar,
    "ShortTestName" varchar,
    "TestID" int4
    ) ;
DROP TABLE "finaldata" CASCADE;
CREATE TABLE "finaldata" (
    "FdataID" serial primary key,
    "Pid" varchar,
    "Sid" varchar,
    "SidOrig" varchar,
    "Parameter" varchar,
    "Result" varchar,
    "ResultOrig" varchar,
    "Units" varchar,
    "OperatorID" varchar,
    "ObsTime" varchar,
    "MsgTime" varchar,
    "Analyzer" varchar,
    "TestName" varchar,
    "ShortTestName" varchar,
    "TestID" varchar,
    "XYchar1" varchar,
    "XYchar2" varchar,
    "XYchar3" varchar,
    "XYint1" int4,
    "XYint2" int4,
    "XYint3" int4,
    "XYGuid" uuid
    ) ;

Function && trigger:

  DROP FUNCTION testcode_matches() CASCADE;
  CREATE FUNCTION testcode_matches()
    RETURNS TRIGGER AS $meat$
  BEGIN
      INSERT INTO finaldata ("Pid", "Sid", "SidOrig", "Parameter", "Result", "ResultOrig", "Units"
                , "OperatorID", "ObsTime", "MsgTime", "Analyzer", "TestName", "ShortTestName", "TestID")
      SELECT ob."Pid", ob."Sid", ob."SidOrig", ob."Parameter", ob."Result", ob."ResultOrig", ob."Units"
                , ob."OperatorID", ob."ObsTime", ob."MsgTime", ob."Analyzer"
                , tc."TestName", tc."ShortTestName", tc."TestID"
      FROM obx ob
      JOIN testcode tc ON ob."Parameter" = tc."Parameter"
      WHERE ob."Parameter" = NEW."Parameter"
      -- AND ob."TestID" = NEW."TestID" -- Column does not exist
      AND ob."obxID" = NEW."obxID" -- This appears to be the PK for obx
          ;
      RETURN NEW;
  END;
  $meat$ LANGUAGE plpgsql;


    CREATE TRIGGER finaldata_ins
    AFTER INSERT ON obx
    FOR EACH ROW
    EXECUTE PROCEDURE testcode_matches()
    ;

TESTDATA:

INSERT INTO testcode ("TestID", "Parameter") VALUES ( 101, 'Fudge');

INSERT INTO obx -- i"obxID" serial primary key,
        ( "Pid", "Sid" , "Parameter" ) VALUES ('Foo', 'Bar' ,'Fudge' ) ;

SELECT * FROM finaldata;

Also: I am missing a few foreign key constraints. I would expect the testdata to have at least the same keyfields as obx as candidate key. (plus som version/date,Parameter keyfield) It seems your datamodel is not fit to support the data you feed into it.