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

问题描述

我使用PostgreSQL 9.1与PostGIS 1.5。

我想让这个触发器功能在终端（Ubuntu）工作：

  CREATE FUNCTION insert_trigger（）
 RETURNS触发器AS 
 $ insert_trigger $ 
 BEGIN 
 IF > = 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超出范围。 insert_trigger（）函数出错了！'; 
 END IF; 
 RETURN NULL; 
 END 
 $ insert_trigger $ LANGUAGE plpgsql; 
  

我得到这个例外：

<$ p $

SQLException：ERROR：在第1行第1列发生了ELSIF。

SQLException：ERROR：在第1行第1列发生ELSE。

SQLException：当autoCommit启用时无法提交。

SQLException：错误：在第1行第1列发生了RETURN。

SQLException：当autoCommit启用时无法提交。

解决方案

我引用我在在线文档：

Stado是用Java编写的，通过 JDBC 与底层数据库进行通信。

大胆强调我。基于此，让我提出这个假设：

很多人都知道网站 SQL Fiddle 。它也使用JDBC。以下是当我尝试在默认模式下创建您的函数时会发生什么：

失败的

但这个工作原理：

工作小提琴

区别？我将; 的查询终止符（右下角）改为 // ，以防止JDBC篡改语句。显然，JDBC不能处理 美元计价 。请参阅下面的 @ Craig的评论。

您可以使用不会显示在代码中的查询终止符来规避问题，例如在我的小提琴中。或者用纯单引号替换美元报价。您必须正确转义每个单引号，但：

• 在PostgreSQL中使用单引号插入varchar

I use PostgreSQL 9.1 with PostGIS 1.5.

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;

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.

解决方案

I quote what I found in online documentation:

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:

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:

Failing fiddle

But this one works:

Working Fiddle

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:

• Insert varchar with single quotes in PostgreSQL

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