错误：达到或接近未结束引用的字符串 [英] ERROR: unterminated quoted string at or near

问题描述

在使用ANT我收到错误执行如下触发code

  org.postgresql.util.PSQLException：ERROR：达到或接近'声明超时整数未结束引用的字符串
您的位置：57
 

我能够通过成功地pgAdmin的执行低于code（Postgres所提供）和命令行实用程序PSQL和触发功能被加入，但同时通过ANT执行失败每次

  BEGIN TRANSACTION;CREATE OR REPLACE FUNCTION清扫（）返回触发为
    宣布
    超时整数;
    开始
    超时= 30 * 24 * 60 * 60;
        DELETE FROM diagnosticdata WHERE CURRENT_TIMESTAMP  -  teststarttime＆GT; （超时*''1秒''::区间）;
        返回新;
    结束;
语言PLPGSQL'; - 触发：扫上diagnosticdataCREATE TRIGGER扫
  AFTER INSERT
  ON diagnosticdata
  FOR EACH ROW
  EXECUTE PROCEDURE清扫（）;结束;
 

解决方案

我不得不与Liquibase使用JDBC驱动程序同样的问题。

看来，司机爆炸由一个分号结束每行，并运行它作为一个单独的SQL命令。这就是为什么code下面将通过JDBC驱动程序按以下顺序执行：

1. CREATE OR REPLACE功能测试（文本）返回void AS'声明TMP文本


2. BEGIN TMP：=测试


3. END;


4. 'LANGUAGE PLPGSQL

当然，这是无效的SQL和导致以下错误：

 或接近'声明TMP文本未终止美元引号字符串
 

要解决这个问题，你需要使用反斜线每行后用分号结束：

  CREATE OR REPLACE功能测试（文本）
返回void AS'声明TMP文本; \\
开始
TMP：=测试; \\
结束;'语言PLPGSQL;
 

另外，您也可以将整个定义一行。

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;

解决方案

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:

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:

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.

这篇关于错误：达到或接近未结束引用的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！