Having problem with Merge statement in Postgres

Question

I have written a function to use a Merge statement to update a table called Device. The function spSetOrUpdateDevice will take in a set of parameters and then load these into a temporary table called LoadParameterData

I then attempt to do a Merge statement between this LoadParameterData and Device table but its not working

here is my Device table

CREATE TABLE public.device (
    deviceid uuid NOT NULL,
    serialnumber character varying(255),
    productcode character varying(255),
    description character varying(255),
    softwareversion character varying(255),
    build character varying(255),
    builddate timestamp with time zone,
    assigned boolean,
    groupid uuid,
    updateddatetime timestamp with time zone,
    restartpointerno integer,
    deviceconnectionindex integer,
    organisationid uuid
);


ALTER TABLE public.device OWNER TO postgres;
--
-- Data for Name: device; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO public.device (deviceid, serialnumber, productcode, description, softwareversion, build, builddate, assigned, groupid, updateddatetime, restartpointerno, deviceconnectionindex, organisationid) VALUES ('189a3642-64ca-4bf8-ae58-0e1f0ef27d22', '939029', 'ACSR-3600-A', 'Metrology Test Receiver', '1.2.928', '4829', NULL, NULL, '89c49f24-2a34-4517-a7e4-fb81a52d82dd', '2023-09-29 11:18:24.52+01', 9060068, 1, NULL);
INSERT INTO public.device (deviceid, serialnumber, productcode, description, softwareversion, build, builddate, assigned, groupid, updateddatetime, restartpointerno, deviceconnectionindex, organisationid) VALUES ('7315a69e-1dad-4c5b-bf8e-5433b90b62c6', '876302', 'TR-3020-A', 'TR-3020-A', '1.4.1', '4829', NULL, NULL, NULL, '2023-09-29 11:18:24.666667+01', 4683540, 2, '272ef7b7-244b-4911-a011-d941774028c3');
INSERT INTO public.device (deviceid, serialnumber, productcode, description, softwareversion, build, builddate, assigned, groupid, updateddatetime, restartpointerno, deviceconnectionindex, organisationid) VALUES ('51a64e1f-c7b4-4ea8-bfaf-2fdff527b751', '940860', 'TGRF-4024-A', 'Office Desk', '1.2.928', '4843', NULL, NULL, NULL, '2023-09-29 11:18:24.506667+01', 8870307, 2, NULL);
INSERT INTO public.device (deviceid, serialnumber, productcode, description, softwareversion, build, builddate, assigned, groupid, updateddatetime, restartpointerno, deviceconnectionindex, organisationid) VALUES ('95e1610d-e3f7-42fc-8303-9da9b9aaf784', '545415', 'TGRF-4026-A', 'Test Logger3', '1.2.3', '4848', NULL, NULL, NULL, NULL, 7341269, NULL, NULL);
INSERT INTO public.device (deviceid, serialnumber, productcode, description, softwareversion, build, builddate, assigned, groupid, updateddatetime, restartpointerno, deviceconnectionindex, organisationid) VALUES ('7036b270-1ddd-4ce6-aef0-9b8508db1c6b', '856365', 'TGRF-4024-A', 'Test Logger1', '1.2.3', '4848', NULL, NULL, NULL, NULL, 461912, NULL, NULL);
INSERT INTO public.device (deviceid, serialnumber, productcode, description, softwareversion, build, builddate, assigned, groupid, updateddatetime, restartpointerno, deviceconnectionindex, organisationid) VALUES ('c525e43d-a53b-41da-a9ef-a971a2dd14ab', '455455', 'TGRF-4025-A', 'Test Logger2', '1.2.3', '4848', NULL, NULL, NULL, NULL, 2572984, NULL, NULL);
INSERT INTO public.device (deviceid, serialnumber, productcode, description, softwareversion, build, builddate, assigned, groupid, updateddatetime, restartpointerno, deviceconnectionindex, organisationid) VALUES ('ba5ca162-95ba-4fc7-9335-710af1d3c783', '737675', 'TK-4014', 'Test Logger2', '1.2.3', '4848', NULL, NULL, NULL, NULL, 1587554, NULL, NULL);
INSERT INTO public.device (deviceid, serialnumber, productcode, description, softwareversion, build, builddate, assigned, groupid, updateddatetime, restartpointerno, deviceconnectionindex, organisationid) VALUES ('ed86ef25-e131-4591-98ae-11f4cfca448d', '894339', 'TR-3020-A', 'TR-3020-A', NULL, NULL, NULL, NULL, NULL, NULL, 6017319, NULL, NULL);
INSERT INTO public.device (deviceid, serialnumber, productcode, description, softwareversion, build, builddate, assigned, groupid, updateddatetime, restartpointerno, deviceconnectionindex, organisationid) VALUES ('04a2feec-d61d-49cd-88df-dadb3aa66e6c', '926371', 'TGRF-4602-A', 'Office Bookcase', '1.2.928', '4843', NULL, NULL, '6071392c-ca4a-4967-9ec2-f75e222d8b41', '2023-09-29 11:18:24.526667+01', 3050536, 2, NULL);

Now this is my function spSetOrUpdateDevice defined:

DROP FUNCTION IF EXISTS spSetOrUpdateDevice( text,  text,  text,  text,  text,timestamp without time zone);

CREATE OR REPLACE FUNCTION spSetOrUpdateDevice(serialnumber2 text,productcode text, description text, softwareversion text, build text,builddate timestamp without time zone)
RETURNS INT AS $$
    DECLARE DeviceID2 UUID;
BEGIN

    -- DROP TEMPORARY TABLES
    DROP TABLE IF EXISTS LoadParameterData;

    CREATE TEMPORARY TABLE LoadParameterData (
        DeviceID        UUID,
        SerialNumber    VARCHAR(60),
        ProductCode     VARCHAR(255),
        Description     VARCHAR(255),
        SoftwareVersion VARCHAR(255),
        Build           VARCHAR(255),
        BuildDate       TIMESTAMP WITHOUT TIME ZONE
    );


   IF COALESCE(serialnumber2,'')='' THEN
        RAISE NOTICE 'Serial Number not supplied: %', serialnumber2;
    END IF;

   select Deviceid into DeviceID2 from device where Serialnumber = serialnumber2;

   Insert into LoadParameterData(DeviceID, SerialNumber, ProductCode, Description, SoftwareVersion, Build, BuildDate)
   Values(DeviceID2 ,serialnumber2,productcode , description , softwareversion , build, BuildDate );

   RAISE NOTICE 'The value of Deviceid is %', Deviceid2;

    MERGE into Device
    USING LoadParameterData SOURCE
    ON (TARGET.DeviceID = SOURCE.DeviceID AND TARGET.SerialNumber = SOURCE.SerialNumber)
    --When records are matched, update the records if there is any change
    -- Need to use Coalesce to set Null Values to '' so that they can be compared
    WHEN MATCHED
        AND
                 Coalesce(SOURCE.ProductCode,'') <> Coalesce(ProductCode,'') OR
                 Coalesce(SOURCE.Description,'') <> Coalesce(Description,'') OR
                 Coalesce(SOURCE.SoftwareVersion,'') <> Coalesce(SoftwareVersion,'') OR
                 Coalesce(SOURCE.Build,'') <> Coalesce(Build,'') OR
                 Coalesce(SOURCE.BuildDate,'') <> Coalesce(BuildDate,'');

        UPDATE Device
        SET ProductCode     = COALESCE(SOURCE.ProductCode, ProductCode),
            Description     = COALESCE(SOURCE.Description, Description),
            SoftwareVersion = COALESCE(SOURCE.SoftwareVersion, SoftwareVersion),
            Build           = COALESCE(SOURCE.Build, Build),
            BuildDate       = COALESCE(SOURCE.BuildDate, BuildDate),
            UpdatedDateTime = current_timestamp;
    WHEN NOT MATCHED BY TARGET
    INSERT Device (SerialNumber, ProductCode, Description, SoftwareVersion, Build, BuildDate,UpdatedDateTime)
    VALUES (SOURCE.SerialNumber, SOURCE.ProductCode, SOURCE.Description, SOURCE.SoftwareVersion, SOURCE.Build, SOURCE.BuildDate, current_timestamp)
    RETURNING DeviceID;

  RETURN 1;
END;
$$ LANGUAGE plpgsql;

alter function spSetOrUpdateDevice(text,  text,  text,  text,  text,timestamp without time zone) owner to postgres;

So you can see that what im doing is doing the When Matched clause..setting NULL values to empty string '', so that they can be favourably compared to the source table

If a new record is inserted then I want to return that out of the function

However when i run this , I get the following error [2024-01-08 11:59:21] [42601] ERROR: syntax error at end of input [2024-01-08 11:59:21] Position: 2367

What am I doing wrong here?

Answer

OK I have now solved this as follows:

    MERGE INTO Device Source
    USING LoadParameterData  Target
    ON Target.DeviceID = Source.DeviceID
    WHEN MATCHED AND
                 Coalesce(SOURCE.ProductCode,'')                            <> Coalesce(Target.ProductCode,'')              OR
                 Coalesce(SOURCE.Description,'')                            <> Coalesce(Target.Description,'')              OR
                 Coalesce(SOURCE.SoftwareVersion,'')                        <> Coalesce(Target.SoftwareVersion,'')          OR
                 Coalesce(SOURCE.Build,'')                                  <> Coalesce(Target.Build,'')                    OR
                 Coalesce(SOURCE.BuildDate,CURRENT_DATE )                   <> Coalesce(Target.BuildDate,CURRENT_DATE  )
        THEN
      UPDATE SET    ProductCode = Target.ProductCode,
                    Description = Target.Description,
                    Build = Target.Build,
                    BuildDate = Target.BuildDate,
                    UpdatedDateTime = CURRENT_DATE
    WHEN NOT MATCHED THEN
      INSERT (ProductCode)
      VALUES ('test');