错误:未终止的带引号的字符串位于或附近 [英] ERROR: unterminated quoted string at or near

问题描述

在使用 ANT 执行下面显示的触发代码时，我收到错误

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

我能够通过 PGADmin(由 postgres 提供)和命令行实用程序psql"成功执行以下代码，并添加了触发器功能，但在通过 ANT 执行时每次都失败

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;

推荐答案

我在 liquibase 中遇到了这个错误，这个页面是第一个搜索结果，所以我想我在这个页面分享我的解决方案:

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:

您可以将整个 sql 放在一个单独的文件中，并将其包含在变更集中.将 splitStatements 选项设置为 false 很重要.

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.

整个变更集看起来像

<changeSet author="fgrosse" id="530b61fec3ac9">
    <sqlFile path="your_sql_file_here.sql" splitStatements="false"/>
</changeSet>

我总是喜欢将那些大的 SQL 部分(如函数更新等)放在单独的文件中.这样一来，您在打开 sql 文件时就可以获得正确的语法高亮显示，而不必将 XML 和 SQL 混合在一个文件中.

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 change 也支持 splitStatements 选项(感谢 AndreyT 指出这一点).

Edit: as mentioned in the comments its worth noting that the sql change supports the splitStatements option as well (thx to AndreyT for pointing that out).

这篇关于错误:未终止的带引号的字符串位于或附近的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！