在 PostgreSQL 9.1 中创建触发器时的异常 [英] Exceptions when creating a trigger in PostgreSQL 9.1

问题描述

我使用 PostgreSQL 9.1 和 PostGIS 1.5.

I use PostgreSQL 9.1 with PostGIS 1.5.

我正在尝试让这个触发函数在终端 (Ubuntu) 中工作:

I'm trying to get this trigger function to work in terminal (Ubuntu):

CREATE FUNCTION insert_trigger()
RETURNS trigger AS
$insert_trigger$
BEGIN
IF ( NEW.id >= 10 AND NEW.id < 100 ) THEN 
INSERT INTO part_id_p10 VALUES (NEW.*); 
ELSIF ( NEW.id >= 100 AND NEW.id < 200 ) THEN 
INSERT INTO part_id_p20 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'id out of range.  Something wrong with the insert_trigger() function!';
END IF;
RETURN NULL;
END
$insert_trigger$ LANGUAGE plpgsql;

我得到这个例外:

SQLException: ERROR: Encountered "FUNCTION" at line 1, column 8.

SQLException: ERROR: Encountered "ELSIF" at line 1, column 1.

SQLException: ERROR: Encountered "ELSE" at line 1, column 1.

SQLException: Cannot commit when autoCommit is enabled.

SQLException: ERROR: Encountered "RETURN" at line 1, column 1.

SQLException: Cannot commit when autoCommit is enabled.

推荐答案

我引用了我在 在线文档:

Stado 是用 Java 编写的，并通过 JDBC 与底层数据库通信.

Stado is written in Java and communicates with the underlying databases via JDBC.

粗体强调我的.基于此，让我提出这个假设:

Bold emphasis mine. Based on this, let me present a this hypothesis:

这里的许多人都知道网站 SQL Fiddle.它也使用 JDBC.这是我尝试在默认模式下创建函数时发生的情况:

Many here know the website SQL Fiddle. It uses JDBC, too. Here is what happens when I try to create your function in default mode:

失败的小提琴

但是这个有效:

工作小提琴

区别?我将查询终止符"(右下角)从 ; 更改为 // 以防止 JDBC 破坏语句.显然，JDBC 不能(还)处理 美元引用 正确.请参阅下面的@Craig 的评论.

The difference? I changed the "Query Terminator" (bottom right) from ; to // to keep JDBC from butchering the statement. Obviously, JDBC cannot (yet) deal with dollar-quoting correctly. See @Craig's comment below.

您可以通过使用不会出现在您的代码中的查询终止符来规避该问题，就像在我的小提琴中一样.或者用简单的单引号替换美元引号.不过，您必须正确地转义每个单引号:

You can circumvent the problem by using a query terminator that does not show up in your code, like in my fiddle. Or replace dollar-quotes with plain single-quotes. You'll have to escape every single-quote properly, though:

• 在 PostgreSQL 中插入带单引号的文本

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