postgresql 如果满足条件则阻止插入

Prevent insert if condition is met

提问by Hugo Sousa

I have a table Contentlike this:

我有一张Content这样的表：

id | text | date | idUser → User | contentType 

And another table Answer:

另一个表Answer：

idAnswer → Content | idQuestion → Content | isAccepted

I want to ensure that the Answer's date is bigger than the Question's date. A question is a Contentwith contentType= 'QUESTION'.

我想确保Answer的日期大于Question的日期。问题是Content带有contentType= 'QUESTION' 的问题。

I tried to solve this with the following trigger, but when I try to insert an Answerthere's an error:

我尝试使用以下触发器解决此问题，但是当我尝试插入时Answer出现错误：

ERROR:  record "new" has no field "idanswer"
CONTEXT:  SQL statement "SELECT (SELECT "Content".date FROM "Content" WHERE "Content".id = NEW.idAnswer) < (SELECT "Content".date FROM "Content" WHERE "Content".id = NEW.idQuestion)"
PL/pgSQL function "check_valid_date_answer" line 2 at IF

ERROR:  record "new" has no field "idanswer"
CONTEXT:  SQL statement "SELECT (SELECT "Content".date FROM "Content" WHERE "Content".id = NEW.idAnswer) < (SELECT "Content".date FROM "Content" WHERE "Content".id = NEW.idQuestion)"
PL/pgSQL function "check_valid_date_answer" line 2 at IF

Trigger:

扳机：

CREATE TRIGGER check_valid_answer 
AFTER INSERT ON "Answer"
FOR EACH ROW EXECUTE PROCEDURE check_valid_date_answer();

Trigger function:

触发功能：

CREATE FUNCTION check_valid_date_answer() RETURNS trigger
    LANGUAGE plpgsql
    AS $$BEGIN
  IF (SELECT "Content".date FROM "Content"
      WHERE "Content".id = NEW.idAnswer)
   < (SELECT "Content".date FROM "Content"
      WHERE "Content".id = NEW.idQuestion) 
  THEN
    RAISE NOTICE 'This Answer is an invalid date';
  END IF;
  RETURN NEW;
END;$$;

So, my question is: do I really need to create a trigger for this? I saw that I can't use a CHECKin Answerbecause I need to compare with an attribute of another table. Is there any other (easier/better) way to do this? If not, why the error and how can I solve it?

所以，我的问题是：我真的需要为此创建触发器吗？我看到我不能使用CHECKinAnswer因为我需要与另一个表的属性进行比较。有没有其他（更简单/更好）的方法来做到这一点？如果没有，为什么会出现错误，我该如何解决？

回答by Erwin Brandstetter

Your basic approach is sound. The trigger is a valid solution. It should work except for 3 problems:

你的基本方法是合理的。触发器是一个有效的解决方案。除了3 个问题之外，它应该可以工作：

1) Your naming convention:

1）您的命名约定：

We would need to see your exact table definition to be sure, but the evidence is there. the error message says: has no field"idanswer"- lower case. Doesn't say "idAnswer"- CaMeL case. If you create CaMeL case identifiers in Postgres, you are bound to double-quote them everywhere for the rest of your life.

我们需要查看您的确切表定义才能确定，但​​证据就在那里。错误消息说：has no field"idanswer"- 小写。没有说"idAnswer"- 骆驼案例。如果您在 Postgres 中创建了 CaMeL 案例标识符，那么在您的余生中，您一定会在任何地方对它们进行双引号。

2) Abort violating insert

2) 中止违规插入

• Either raise an EXCEPTIONinstead of a friendly NOTICEto actually abort the whole transaction.

• Or RETURN NULLinstead of RETURN NEWto just abort the inserted row silently without raising an exception and without rolling anything back.

• 要么提高一个EXCEPTION而不是一个友好NOTICE的实际中止整个交易。

• 或者RETURN NULL不RETURN NEW只是静默中止插入的行而不引发异常并且不回滚任何内容。

I would do the first. This will probably fix the error at hand and work:

我会做第一个。这可能会修复手头的错误并起作用：

CREATE FUNCTION trg_answer_insbef_check()
  RETURNS trigger AS
$func$
BEGIN
   IF (SELECT c.date FROM "Content" c WHERE c.id = NEW."idAnswer")
    < (SELECT c.date FROM "Content" c WHERE c.id = NEW."idQuestion") THEN
      RAISE EXCEPTION 'This Answer is an invalid date';
   END IF;
   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

The proper solutionis to use legal, lower case namesexclusively and avoid such problems altogether. That includes your unfortunate table names as well as the column name date, which is a reserved wordin standard SQL and should not be used as identifier - even if Postgres allows it.

在妥善解决是使用合法的，小写的名字完全和完全避免这些问题。这包括您不幸的表名以及列名date，这是标准 SQL 中的保留字，不应用作标识符 - 即使 Postgres 允许。

3) Should be a BEFOREtrigger

3）应该是BEFORE触发器

CREATE TRIGGER insbef_check
BEFORE INSERT ON "Answer"
FOR EACH ROW EXECUTE PROCEDURE trg_answer_insbef_check();

You want to abort invalid inserts before you do anything else.

您想在执行任何其他操作之前中止无效插入。

Of course you will have to make sure that the timestamps table Contentcannot be changed or you need more triggers to make sure your conditions are met.
The same goes for the fk columns in Answer.

当然，您必须确保时间戳表Content无法更改，或者您需要更多触发器来确保满足您的条件。
中的 fk 列也是如此Answer。

回答by Str.

I would approach this in a different way.

我会以不同的方式解决这个问题。

Recommendation:

推荐：

• use a BEFORE INSERT trigger if you want to change data before inserting it
• use a AFTER INSERT trigger if you have to do additional work
• use a CHECK clause if you have additional data consistency requirements.

• 如果要在插入数据之前更改数据，请使用 BEFORE INSERT 触发器
• 如果您必须做额外的工作，请使用 AFTER INSERT 触发器
• 如果您有其他数据一致性要求，请使用 CHECK 子句。

So write a sql function that checks the condition that one date be earlier than the other, and add the check constraint. Yes, you can select from other tables in your function.

所以写一个sql函数来检查一个日期早于另一个的条件，并添加检查约束。是的，您可以从函数中的其他表中进行选择。

I wrote something similar (complex check) in answer to this question on SO.

我在 SO 上写了一些类似的（复杂检查）来回答这个问题。