Zabbix: PostgreSQL query to migrate the old item prototype macros ($1) to its discovery macro

query.sql

WITH macro AS (
    SELECT
        itemid,
        (regexp_matches(key_, '(\{#[^\}]*\})'))[1] AS m
    FROM
        items)
UPDATE
    items
SET
    name = regexp_replace(name, '(\$1)', macro.m)
FROM
    macro
WHERE
    macro.itemid = items.itemid;

WITH macro AS (
    SELECT
        itemid,
        (regexp_matches(key_, '\{#[^\}]*\},(\{#[^\}]*\})'))[1] AS m
    FROM
        items)
UPDATE
    items
SET
    name = regexp_replace(name, '(\$2)', macro.m)
FROM
    macro
WHERE
    macro.itemid = items.itemid;

WITH macro AS (
    SELECT
        itemid,
        (regexp_matches(key_, '\{#[^\}]*\},\{#[^\}]*\},(\{#[^\}]*\})'))[1] AS m
    FROM
        items)
UPDATE
    items
SET
    name = regexp_replace(name, '(\$3)', macro.m)
FROM
    macro
WHERE
    macro.itemid = items.itemid;

WITH macro AS (
    SELECT
        itemid,
        (regexp_matches(key_, '\{#[^\}]*\},\{#[^\}]*\},\{#[^\}]*\},(\{#[^\}]*\})'))[1] AS m
    FROM
        items)
UPDATE
    items
SET
    name = regexp_replace(name, '(\$4)', macro.m)
FROM
    macro
WHERE
    macro.itemid = items.itemid;

WITH macro AS (
    SELECT
        itemid,
        (regexp_matches(key_, '\{#[^\}]*\},\{#[^\}]*\},\{#[^\}]*\},\{#[^\}]*\},(\{#[^\}]*\})'))[1] AS m
    FROM
        items)
UPDATE
    items
SET
    name = regexp_replace(name, '(\$5)', macro.m)
FROM
    macro
WHERE
    macro.itemid = items.itemid;

Deprecated in 4.0 and removed in 6.0

This just changes $1, $2, $3, $4 and $5, as I did not have positional macros for $6, etc.

To check if there are positional macros for $6:

select name from items where name like '%$6%' limit 1;