PostgreSQL触发器创建问题 [英] PostgreSQL Trigger creation issues

问题描述

我已经遍及整个网络和堆栈溢出，尽管尝试做所有事情，但我发现我无法正常工作。代码：

I've looked all over the web and stackoverflow and despite trying to do everything I see I can't get this to work. Code:

CREATE TABLE "ExpressionHistory" (
ExpressionHistoryId SERIAL PRIMARY KEY,
ExpressionId INT NOT NULL,
UserId INT NOT NULL,
AccessDate TIMESTAMP NOT NULL,
CreatedBy VARCHAR(12) NOT NULL,
CreatedDate TIMESTAMP NOT NULL,
ModifiedBy VARCHAR(12) NOT NULL,
ModifiedDate TIMESTAMP NOT NULL);

ALTER TABLE "ExpressionHistory"
ADD CONSTRAINT FK_EXPRESSIONHISTORY_EXPRESSION
FOREIGN KEY ("expressionid")
REFERENCES "Expression";

CREATE OR REPLACE FUNCTION prune_expression_history() RETURNS TRIGGER AS $pruned_expression_history$
    BEGIN
        DELETE FROM ExpressionHistory WHERE ExpressionHistoryId = 
            (SELECT ExpressionHistoryId FROM ExpressionHistory WHERE 
            UserId = NEW.UserId AND
            (SELECT COUNT(1) FROM ExpressionHistory WHERE UserId = NEW.UserId) > 10
            ORDER BY AccessDate DESC 
            LIMIT 1 OFFSET 10 );
        RETURN NULL;
    END;
$pruned_expression_history$ LANGUAGE plpgsql;

CREATE TRIGGER PruneExpressionHistoryTable
    AFTER INSERT ON ExpressionHistory
    FOR EACH ROW
    EXECUTE PROCEDURE prune_expression_history();

错误：

ERROR:  relation "expressionhistory" does not exist
********** Error **********

ERROR: relation "expressionhistory" does not exist
SQL state: 42P01

因此，目标是插入到此表时，在表上发生插入时，我会删除用户的最旧记录10。我无法实例化触发器，这显然是一个问题。我怀疑这个问题有些微妙，但我无法隔离。这在CREATE TRIGGER执行中发生。提前致谢。

So, the goal is that on an insert to this table, I delete the oldest record of 10 for a user when an insert on the table occurs. I can't get the trigger to instantiate and this is clearly a problem. I suspect the issue is something subtle but I can't isolate it. This occurs in the CREATE TRIGGER execution. Thanks in advance.

推荐答案

问题是您正在混用命名约定。
原理很简单：双引号中的标识符区分大小写；不带引号的标识符不区分大小写。
因此， ExpressionHistory 与 expressionhistory 相同，但 ExpressionHistory 与 ExpressionHistory 不同。

The problem is that you are mixing naming conventions. The principle is simple: identifiers in double quotes are case sensitive; identifiers without quotes are case insensitive. So ExpressionHistory is the same as expressionhistory, but "ExpressionHistory" is not the same as ExpressionHistory.

您需要确定一个清晰的命名约定。大多数Postgres用户更喜欢不带双引号的标识符（不区分大小写）。

You need to decide on a clear naming convention. Most Postgres users prefer identifiers without double quotes (case insensitive).

这篇关于PostgreSQL触发器创建问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！