Search code examples
postgresqlplpgsqlupsert

ON CONFLICT ON CONSTRAINT trigger from any constraint

I'm trying to trigger an UPSERT on any constraint but can't seem to find a syntax that would be similar to ON CONFLICT ON CONSTRAINT (*). I've attempted using cases to declare my specific constraint:

ON CONFLICT ON CONSTRAINT (
  CASE "@Type"
    WHEN 'user' THEN "Meta_User_Namespace"
    WHEN 'device' THEN "Meta_Device_Namespace"
    WHEN 'profile' THEN "Meta_UserProfile_Namespace"
    WHEN 'group' THEN "Meta_Group_Namespace"
  END
)

But I don't think this is an appropriate way of doing it and this syntax doesn't seem to work.

How should I solve this? I basically have this SP which handles meta for various table types. Rather than 4 separate tables linking to my meta I think just 4 columns with unique indexes should be adequate. Note that this SP acts as an UPSERT.

CREATE OR REPLACE FUNCTION "SetMeta" (
  "@Type"      VARCHAR(10),
  "@ID"        UUID,
  "@Namespace" VARCHAR(50),
  "@Data"      JSONB
)
RETURNS void AS
$func$
BEGIN
  INSERT INTO
    "Meta" AS um (
      "ID",
      "UserID",
      "UserProfileID",
      "DeviceID",
      "Namespace",
      "Data"
    )
  VALUES (
    UUID_GENERATE_V4(),
    CASE "@Type" WHEN 'user' THEN "@UserID" ELSE null END,
    CASE "@Type" WHEN 'profile' THEN "@UserProfileID" ELSE null END,
    CASE "@Type" WHEN 'device' THEN "@DeviceID" ELSE null END,
    CASE "@Type" WHEN 'group' THEN "@Namespace" ELSE null END,
    "@Namespace",
    "@Data"
  )
  ON CONFLICT ON CONSTRAINT (
    CASE "@Type"
      WHEN 'user' THEN "Meta_User_Namespace"
      WHEN 'device' THEN "Meta_Device_Namespace"
      WHEN 'profile' THEN "Meta_UserProfile_Namespace"
      WHEN 'group' THEN "Meta_Group_Namespace"
    END
  )
  DO UPDATE SET
    "Data" = "@Data",
    "Updated" = NOW()
  WHERE
    (
      um."UserID" = "@UserID" OR
      um."UserProfileID" = "@UserProfileID" OR
      um."DeviceID" = "@DeviceID" OR
      um."GroupID" = "@GroupID"
    ) AND
    um."Namespace" = "@Namespace";
END;
$func$ LANGUAGE PLPGSQL;
Solution

  • I'm trying to trigger an UPSERT on any constraint but can't seem to find a syntax that would be similar to ON CONFLICT ON CONSTRAINT (*).

    Just omit the optional conflict target clause ON CONSTRAINT to fire for any unique violation:

    INSERT INTO "Meta" AS um ( ... )
VALUES ( ... )
ON CONFLICT                            -- that's all!
DO UPDATE SET ...

    Details in the manual on INSERT.

    I would provide a working version of your function, but what you posted is inconsistent. "@UserID" is undefined, etc.

    And use simple, unquoted, legal, lower-case identifiers everywhere in Postgres, like @a_horse already suggested. (That includes parameter and variable names in SQL and PL/pgSQL functions.)