需要Postgres触发器和函数的帮助 [英] Need Assistance with a Postgres Trigger and Function

问题描述

我有一个查找表，其中包含一列资源（来自通过我创建的webservice API捕获的各种硬编码活动）以及应与其关联的各个品牌。这样我就可以给品牌记录品牌为空的记录 - 以便通过营销自动化工具使用特定的模板迎接他们。

I have a lookup table that holds a column of sources (from various hardcoded campaigns captured through a webservice API I created) and the respective brands that should be associated with them. This is so I can give a brand to records where brand is null - so that they can be welcomed with a certain template through a marketing automation tool.

我最终弃用这个API并将其替换为需要品牌的API，但同时我必须制定一个临时解决方案，直到我将所有品牌团队有时间改变他们的API调用。

I'm eventually deprecating this API and replacing it with one where brand is required, but in the meantime I have to craft a temporary solution until I give all my brand teams time to change their API calls.

我写了这个函数：

I wrote this function:

CREATE OR REPLACE FUNCTION public.brand_lookup(IN i_brand TEXT )
  RETURNS SETOF RECORD VOLATILE AS
$$
BEGIN
   RETURN QUERY
     UPDATE subscriber
     SET brand = (SELECT brand FROM brand_translation
                  WHERE source = subscriber.source);
END;
$$
LANGUAGE plpgsql;

在插入记录时触发函数的触发器：

And a trigger to fire the function when a record is inserted:

CREATE TRIGGER brand_translation 
  AFTER INSERT ON subscriber
  FOR EACH ROW EXECUTE PROCEDURE public.brand_lookup();

但是我的触发器返回一个错误，ERROR：function public.brand_lookup（）does not exist （但是它创建成功了）除了我的触发器没有看到我的函数这个事实之外，这个函数会按照我的意图做什么吗？我对函数很不在乎（正如你可能知道的那样）。 >

But my trigger comes back with an error that "ERROR: function public.brand_lookup() does not exist" (but it created successfully)". Besides the fact that my trigger doesn't see my function, will that function do what I'm intending? I'm fairly noob at functions (as you can probably tell).

推荐答案

它可能是这样工作的：

It might work like this:

CREATE OR REPLACE FUNCTION public.f_brand_lookup()
   RETURNS trigger AS
$func$
BEGIN
   SELECT INTO NEW.brand
          bt.brand
   FROM   brand_translation bt
   WHERE  bt.source = NEW.source;

   RETURN NEW;
END
$func$
LANGUAGE plpgsql;

CREATE TRIGGER brand_insert_before_lookup
BEFORE INSERT ON subscriber
FOR EACH ROW EXECUTE PROCEDURE public.f_brand_lookup();

你的例子完全错误。

你需要开始通过研究基础。与往常一样，我建议使用非常精细的手册。

开始此处和此处。

There is just too much completely wrong with your example.
You need to start by studying the basics. As always, I suggest the very fine manual.
Start here and here.

这篇关于需要Postgres触发器和函数的帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！