错误:位于或附近未终止的带引号的字符串
使用 ANT 执行下面所示的触发代码时,出现错误:
org.postgresql.util.PSQLException:错误:未终止的带引号的字符串位于或接近“'DECLARE超时整数” 排名:57
我能够通过 PGADmin (由 postgres 提供)和命令行实用程序“psql”成功执行以下代码,并且添加了触发函数,但是在通过 ANT 执行时每次都会失败
BEGIN TRANSACTION;
CREATE OR REPLACE FUNCTION sweeper() RETURNS trigger as '
DECLARE
timeout integer;
BEGIN
timeout = 30 * 24 * 60 * 60 ;
DELETE FROM diagnosticdata WHERE current_timestamp - teststarttime > (timeout * ''1 sec''::interval);
return NEW;
END;
' LANGUAGE 'plpgsql';
-- Trigger: sweep on diagnosticdata
CREATE TRIGGER sweep
AFTER INSERT
ON diagnosticdata
FOR EACH ROW
EXECUTE PROCEDURE sweeper();
END;
While executing below shown trigger code using ANT I am getting the error:
org.postgresql.util.PSQLException: ERROR: unterminated quoted string at or near "' DECLARE timeout integer"
Position: 57
I am able to sucessfully execute the below code through PGADmin (Provided by postgres) and command line utility "psql" and the trigger function is added but while executing through ANT it fails everytime
BEGIN TRANSACTION;
CREATE OR REPLACE FUNCTION sweeper() RETURNS trigger as '
DECLARE
timeout integer;
BEGIN
timeout = 30 * 24 * 60 * 60 ;
DELETE FROM diagnosticdata WHERE current_timestamp - teststarttime > (timeout * ''1 sec''::interval);
return NEW;
END;
' LANGUAGE 'plpgsql';
-- Trigger: sweep on diagnosticdata
CREATE TRIGGER sweep
AFTER INSERT
ON diagnosticdata
FOR EACH ROW
EXECUTE PROCEDURE sweeper();
END;
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(9)
我在 liquibase 中遇到了这个错误,并且此页面是第一个搜索结果之一,所以我想我在此页面分享我的解决方案:
您可以将整个 sql 放在一个单独的文件中,并将其包含在变更集中。
将
splitStatements
选项设置为false
非常重要。整个变更集看起来就像
我总是喜欢将那些大的 SQL 部分(如函数更新等)放在单独的文件中。
这样,您就可以在打开 sql 文件时获得正确的语法突出显示,并且不必在一个文件中混合 XML 和 SQL。
编辑:正如评论中提到的,值得注意的是
sql
change 也支持splitStatements
选项(感谢 AndreyT 指出这一点)。I encountered this error in liquibase and this page was one of the first search results so I guess I share my solution at this page:
You can put your whole sql in a separate file and include this in the changeset.
Its important to set the
splitStatements
option tofalse
.The whole changeset would then look like
I always like to have those big SQL parts (like function updates and such) in separate files.
This way you get proper syntax highlighting when opening the sql file and dont have to intermix XML and SQL in one file.
Edit: as mentioned in the comments its worth noting that the
sql
change supports thesplitStatements
option as well (thx to AndreyT for pointing that out).我对 Liquibase 使用的 JDBC 驱动程序也有同样的问题。
驱动程序似乎分解了以分号结尾的每一行,并将其作为单独的 SQL 命令运行。这就是为什么下面的代码将由 JDBC 驱动程序按以下顺序执行:
CREATE OR REPLACE FUNCTION test(text) RETURNS VOID AS ' DECLARE tmp text
BEGIN tmp := "test"
END;
' LANGUAGE plpgsql
当然,这是无效的 SQL 并会导致以下错误:
要更正此问题,您需要在每行结尾后使用反斜杠分号:
或者,您可以将整个定义放在一行中。
I had the same problem with the JDBC driver used by Liquibase.
It seems that the driver explodes each line ended by a semicolon and runs it as a separate SQL command. That is why the code below will be executed by the JDBC driver in the following sequence:
CREATE OR REPLACE FUNCTION test(text) RETURNS VOID AS ' DECLARE tmp text
BEGIN tmp := "test"
END;
' LANGUAGE plpgsql
Of course, this is invalid SQL and causes the following error:
To correct this, you need to use backslashes after each line ended with semicolon:
Alternatively, you can place the whole definition in one line.
我正在使用 HeidiSQL 客户端,这是通过在 CREATE OR REPLACE 语句之前放置 DELIMITER // 解决的。 HeidiSQL 中还有一个“一次性发送批次”选项,本质上实现了相同的目的。
I am using HeidiSQL client and this was solved by placing DELIMITER // before CREATE OR REPLACE statement. There is a also a 'Send batch in one go' option in HeidiSQL that essentially achieves the same thing.
此错误是由于用于连接到服务器的特定客户端与函数形式之间的交互而出现的。为了说明这一点:
以下代码将在 Netbeans 7、Squirrel、DbSchema、PgAdmin3 中正常运行。
请注意,“begin”语句紧跟在“$”引用的字符串之后。
下一个代码将停止除 PgAdmin3 之外的所有上述客户端。
第二个示例中的关键区别是“声明”部分。使用反斜杠的策略会引发 PgAdmin3 错误。
总之,我建议尝试不同的工具。有些工具即使应该写入文本文件,也会将不可见的内容放入文本中。众所周知,这种情况发生在 Unicode BOM 中,它将停止任何尝试实现会话或命名空间的 php 文件。
虽然这不是解决方案,但我希望它有所帮助。
This error arises as an interaction between the particular client used to connect to the server and the form of the function. To illustrate:
The following code will run without casualty in Netbeans 7, Squirrel, DbSchema, PgAdmin3
Please note that the 'begin' statement comes immediately after the '$' quoted string.
The next code will halt all the above clients except PgAdmin3.
The crucial difference in the second example is the 'declare' section. The ploy of using back-slashes raises an error with PgAdmin3.
In summary I suggest trying different tools. Some tools even though they are supposed to be writing text files put invisible stuff into the text. Notoriously this occurs with the Unicode BOM which will halt any php file that tries to implement sessions or namespaces.
Whilst this is no solution I hope it helps.
这个例子适用于 PostgreSQL 14.1 和 HeidiSQL 9.4.0.5125
This example worked for me with PostgreSQL 14.1 and HeidiSQL 9.4.0.5125
我对 zeos 和 c++ builder 也有同样的问题。
我的案例的解决方案:
将属性分隔符(通常是“;”)更改为我使用的组件(类)中的另一个分隔符。
也许蚂蚁金服也有类似的东西。
I had the same problem with zeos and c++ builder.
The solution in my case:
Change the property delimiter (usually ";") to another in the component (class) I used.
Perhaps Ant have something similar.
我知道这个问题很久以前就被问过,但我使用 Ant 的 SQL 任务的 Postgresql 脚本(从 Jenkins 运行)也遇到了同样的问题。
我尝试运行此 SQL(保存在名为audit.sql 的文件中):
但收到错误“未终止的美元引用字符串”。从 pgAdmin 运行它没有问题。
我发现并不是驱动程序在每个“;”处分割脚本。而是蚂蚁。
在 http://grokbase.com/ t/postgresql/pgsql-jdbc/06cjx3s3y0/ant-sql-tag-for-dollar-quoting 我找到了答案:
我的 Ant SQL 脚本如下所示并且可以运行:
I know this question was asked a long time ago but I had kind of the same issue with a Postgresql script (run from Jenkins) using Ant's SQL Task.
I tried to run this SQL (saved in a file named audit.sql):
but got the error "unterminated dollar-quoted string". No problem running it from pgAdmin.
I found out that it is not the driver that split the script at every ";" but rather Ant.
At http://grokbase.com/t/postgresql/pgsql-jdbc/06cjx3s3y0/ant-sql-tag-for-dollar-quoting I found the answer:
My Ant SQL script looks like this and it works:
我收到了同样的错误,因为我在新行中添加了分号,如下所示:
确保它们在一行中
I was receiving the same error because I had my semicolon in a new line like this:
Make sure they are in a single line as
我得到了同样的错误如下:
当我尝试在下面创建
my_func()
时:因此,我将
'
放在OK
如下所示,然后我可以创建my_func()
而不会出现错误:I got the same error below:
When I tried to create
my_func()
below:So, I put
'
just afterOK
as shown below, then I could createmy_func()
without error: