PostgreSQL创建触发器

Postgre中正确创建触发器的SQL语句

-- 创建触发器函数
DO $$
BEGIN
    -- 如果触发器函数不存在，则创建
    IF NOT EXISTS (
        SELECT 1
        FROM pg_proc
        WHERE proname = 'notify_stock_updates'
    ) THEN
        CREATE FUNCTION notify_stock_updates()
        RETURNS TRIGGER AS $FUNC$
        BEGIN
            -- 触发器逻辑：发送通知
            PERFORM pg_notify('stock_updates', NEW.symbol || ': new data available');
            RETURN NEW;
        END;
        $FUNC$ LANGUAGE plpgsql;
    END IF;
END;
$$;

-- 创建触发器
DO $$
BEGIN
    -- 如果触发器不存在，则创建
    IF NOT EXISTS (
        SELECT 1
        FROM pg_trigger t
        JOIN pg_class c ON t.tgrelid = c.oid
        JOIN pg_namespace n ON c.relnamespace = n.oid
        WHERE t.tgname = 'stock_update_trigger'
          AND c.relname = 'astock_5m'
          AND n.nspname = 'market'
    ) THEN
        CREATE TRIGGER stock_update_trigger
        AFTER INSERT ON market.astock_5m
        FOR EACH ROW
        EXECUTE FUNCTION notify_stock_updates();
    END IF;
END;
$$;