I use PostgreSQL 9.1 with PostGIS 1.5.
我使用PostgreSQL 9.1和PostGIS 1.5。
I'm trying to get this trigger function to work in terminal (Ubuntu):
我正在尝试让这个触发函数在终端(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;
i get this exceptions:
我得到这个例外:
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.
1 个解决方案
#1
2
I quote what I found in online documentation:
我引用我在网上找到的文件:
Stado is written in Java and communicates with the underlying databases via JDBC.
Stado是用Java编写的，通过JDBC与底层数据库通信。
Bold emphasis mine. Based on this, let me present a this hypothesis:
我大胆的重点。基于此，让我提出一个假设:
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:
这里的很多人都知道这个网站的SQL Fiddle。它使用JDBC。当我尝试在默认模式下创建您的函数时会发生以下情况:
没有小提琴
But this one works:
但是这个工作:
工作小提琴
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.
区别呢?我将“查询终止符”(右下角)从;to //防止JDBC破坏语句。显然，JDBC还不能正确地处理引用美元的问题。请参阅下面的@Craig的评论。
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:
您可以通过使用在代码中不显示的查询终止符来绕过这个问题，就像在我的fiddle一样。或者用简单的单引号代替美元引号。不过，你必须正确地避开每一个单引号:
- Insert text with single quotes in PostgreSQL
- 在PostgreSQL中插入带有单引号的文本
#1
2
I quote what I found in online documentation:
我引用我在网上找到的文件:
Stado is written in Java and communicates with the underlying databases via JDBC.
Stado是用Java编写的，通过JDBC与底层数据库通信。
Bold emphasis mine. Based on this, let me present a this hypothesis:
我大胆的重点。基于此，让我提出一个假设:
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:
这里的很多人都知道这个网站的SQL Fiddle。它使用JDBC。当我尝试在默认模式下创建您的函数时会发生以下情况:
没有小提琴
But this one works:
但是这个工作:
工作小提琴
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.
区别呢?我将“查询终止符”(右下角)从;to //防止JDBC破坏语句。显然，JDBC还不能正确地处理引用美元的问题。请参阅下面的@Craig的评论。
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:
您可以通过使用在代码中不显示的查询终止符来绕过这个问题，就像在我的fiddle一样。或者用简单的单引号代替美元引号。不过，你必须正确地避开每一个单引号:
- Insert text with single quotes in PostgreSQL
- 在PostgreSQL中插入带有单引号的文本