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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-10 22:38:43  来源:igfitidea点击:

ERROR: unterminated quoted string at or near

postgresqlanttriggersplpgsql

提问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 splitStatementsoption 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 sqlchangesupports the splitStatementsoption 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 驱动程序按以下顺序执行:

  1. CREATE OR REPLACE FUNCTION test(text) RETURNS VOID AS ' DECLARE tmp text
  2. BEGIN tmp := "test"
  3. END;
  4. ' LANGUAGE plpgsql
  1. CREATE OR REPLACE FUNCTION test(text) RETURNS VOID AS ' DECLARE tmp text
  2. BEGIN tmp := "test"
  3. END;
  4. ' 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;