ON CONFLICT ON CONSTRAINT trigger from any constraint

Question

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;

You will need dynamic SQL for that. Or use four different insert statements (btw: you should really avoid those dreaded quoted identifiers. They are much more trouble in the long run then they are worth it) — , Jun 12 '17 at 20:40
Dynamic SQL? Like dynamic variables in place of my case statements? — ddibiase, Jun 12 '17 at 22:29
I've started toying with using EXECUTE and changing the values in my statement ie: CREATE OR REPLACE FUNCTION "SetUserMeta" ( "@Type" VARCHAR(10), "@ID" UUID, "@Namespace" VARCHAR(50), "@Data" JSONB ) RETURNS void AS $func$ BEGIN EXECUTE 'INSERT INTO "Meta" AS m ( "ID", "'|| "@Type" ||'ID" "Namespace", "Data" ) VALUES ( UUID_GENERATE_V4(), "'|| "@ID" ||'" ... etc. — ddibiase, Jun 12 '17 at 22:40

Erwin Brandstetter · Accepted Answer · 2017-07-19T03:27:34.090

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.)

Are PostgreSQL column names case-sensitive?

ON CONFLICT ON CONSTRAINT trigger from any constraint

1 Answers1