postgresql 错误:未终止的带引号的字符串位于或附近
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/3499483/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ERROR: unterminated quoted string at or near
提问by Anuj
While executing below shown trigger code using ANT I am getting the error
在使用 ANT 执行下面显示的触发代码时,我收到错误
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
我能够通过 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;
回答by Friedrich Gro?e
I encountered this error in liquibaseand this page was one of the first search results so I guess I share my solution at this page:
我在liquibase 中遇到了这个错误,这个页面是第一个搜索结果之一,所以我想我在这个页面分享我的解决方案:
You can put your whole sql in a separate file and include this in the changeset.
Its important to set the splitStatements
option to false
.
您可以将整个 sql 放在一个单独的文件中,并将其包含在变更集中。将splitStatements
选项设置为false
.
The whole changeset would then look like
整个变更集看起来像
<changeSet author="fgrosse" id="530b61fec3ac9">
<sqlFile path="your_sql_file_here.sql" splitStatements="false"/>
</changeSet>
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.
我总是喜欢将那些大的 SQL 部分(如函数更新等)放在单独的文件中。通过这种方式,您可以在打开 sql 文件时获得正确的语法突出显示,而不必在一个文件中混合 XML 和 SQL。
Edit: as mentioned in the comments its worth noting that the sql
changesupports the splitStatements
option as well (thx to AndreyT for pointing that out).
编辑:正如评论中提到的,值得注意的是,该sql
更改也支持该splitStatements
选项(感谢 AndreyT 指出这一点)。
回答by manRo
I had the same problem with the JDBC driver used by Liquibase.
Liquibase 使用的 JDBC 驱动程序也有同样的问题。
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:
似乎驱动程序会分解以分号结尾的每一行,并将其作为单独的 SQL 命令运行。这就是为什么下面的代码将由 JDBC 驱动程序按以下顺序执行:
CREATE OR REPLACE FUNCTION test(text) RETURNS VOID AS ' DECLARE tmp text
BEGIN tmp := "test"
END;
' LANGUAGE plpgsql
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:
当然,这是无效的 SQL 并导致以下错误:
unterminated dollar-quoted string at or near ' DECLARE tmp text
To correct this, you need to use backslashes after each line ended with semicolon:
要更正此问题,您需要在每行以分号结尾后使用反斜杠:
CREATE OR REPLACE FUNCTION test(text)
RETURNS void AS ' DECLARE tmp text; \
BEGIN
tmp := "test"; \
END;' LANGUAGE plpgsql;
Alternatively, you can place the whole definition in one line.
或者,您可以将整个定义放在一行中。
回答by user3328634
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.
我正在使用 HeidiSQL 客户端,这是通过在 CREATE OR REPLACE 语句之前放置 DELIMITER // 来解决的。HeidiSQL 中还有一个“一次性发送批处理”选项,基本上可以实现相同的功能。
回答by lisben
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.
我知道很久以前就有人问过这个问题,但是我在使用 Ant 的 SQL 任务的 Postgresql 脚本(从 Jenkins 运行)上遇到了同样的问题。
I tried to run this SQL (saved in a file named audit.sql):
我尝试运行此 SQL(保存在名为 audit.sql 的文件中):
DROP SCHEMA IF EXISTS audit CASCADE
;
CREATE SCHEMA IF NOT EXISTS audit AUTHORIZATION faktum
;
CREATE FUNCTION audit.extract_interval_trigger ()
RETURNS trigger AS $extractintervaltrigger$
BEGIN
NEW."last_change_ts" := current_timestamp;
NEW."last_change_by" := current_user;
RETURN NEW;
END;
$extractintervaltrigger$ LANGUAGE plpgsql
;
but got the error "unterminated dollar-quoted string". No problem running it from pgAdmin.
但得到错误“未终止的美元引用的字符串”。从 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-quotingI found the answer:
在http://grokbase.com/t/postgresql/pgsql-jdbc/06cjx3s3y0/ant-sql-tag-for-dollar-quoting我找到了答案:
Ant eats double-$$ as part of its variable processing. You have to use $BODY$ (or similar) in the stored procs, and put the delimiter on its own line (with delimitertype="row"). Ant will cooperate then.
Ant 将 double-$$ 作为其变量处理的一部分。您必须在存储的过程中使用 $BODY$(或类似的),并将分隔符放在自己的行上(使用 delimitertype="row")。蚂蚁会配合。
My Ant SQL script looks like this and it works:
我的 Ant SQL 脚本看起来像这样,它可以工作:
<sql
driver="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/jenkins"
userid="user" password="*****"
keepformat="true"
autocommit="true"
delimitertype="row"
encoding="utf-8"
src="audit.sql"
/>
回答by David
This example worked for me with PostgreSQL 14.1 and HeidiSQL 9.4.0.5125
这个例子对我有用 PostgreSQL 14.1 和 HeidiSQL 9.4.0.5125
DROP TABLE IF EXISTS emp;
CREATE TABLE emp (
empname text NOT NULL,
salary integer
);
DROP TABLE IF EXISTS EMP_AUDIT;
CREATE TABLE emp_audit(
operation char(1) NOT NULL,
stamp timestamp NOT NULL,
userid text NOT NULL,
empname text NOT NULL,
salary integer
);
DELIMITER //
CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $$
BEGIN
--
-- Create a row in emp_audit to reflect the operation performed on emp,
-- make use of the special variable TG_OP to work out the operation.
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
RETURN NEW;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS emp_audit ON emp;
CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();
回答by huphos
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
以下代码将在 Netbeans 7、Squirrel、DbSchema、PgAdmin3 中顺利运行
CREATE OR REPLACE FUNCTION author.revision_number()
RETURNS trigger AS
$BODY$
begin
new.rev := new.rev + 1;
new.revised := current_timestamp;
return new;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
Please note that the 'begin' statement comes immediately after the '$' quoted string.
请注意,'begin' 语句紧跟在'$' 带引号的字符串之后。
The next code will halt all the above clients except PgAdmin3.
下一个代码将停止除 PgAdmin3 之外的所有上述客户端。
CREATE OR REPLACE FUNCTION author.word_count()
RETURNS trigger AS
$BODY$
declare
wordcount integer := 0; -- counter for words
indexer integer := 1; -- position in the whole string
charac char(1); -- the first character of the word
prevcharac char(1);
begin
while indexer <= length(new.blab) loop
charac := substring(new.blab,indexer,1); -- first character of string
if indexer = 1 then
prevcharac := ' '; -- absolute start of counting
else
prevcharac := substring(new.blab, indexer - 1, 1); -- indexer has increased
end if;
if prevcharac = ' ' and charac != ' ' then
wordcount := wordcount + 1;
end if;
indexer := indexer + 1;
end loop;
new.words := wordcount;
return new;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
The crucial difference in the second example is the 'declare' section. The ploy of using back-slashes raises an error with PgAdmin3.
第二个示例中的关键区别是“声明”部分。使用反斜杠的策略会引发 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.
总之,我建议尝试不同的工具。一些工具即使应该编写文本文件,也会将不可见的内容放入文本中。众所周知,Unicode BOM 会发生这种情况,它会停止任何尝试实现会话或命名空间的 php 文件。虽然这不是解决方案,但我希望它有所帮助。
回答by fvel
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.
我对 zeos 和 c++ builder 有同样的问题。在我的情况下的解决方案:
在我使用的组件(类)中将属性分隔符(通常是“;”)更改为另一个。
dm->ZSQLProcessor1->DelimiterType=sdGo;
Perhaps Ant have something similar.
也许蚂蚁也有类似的东西。
回答by masoodg
I was receiving the same error because I had my semicolon in a new line like this:
我收到了同样的错误,因为我的分号在这样的新行中:
WHERE colA is NULL
;
Make sure they are in a single line as
确保它们在一行中
WHERE colA is NULL;