java - 使用包含 $$ 字符的 SQLExec 执行文件

Question

我使用 pg_dump 创建了一个 sql 转储文件。此导出文件包含包含 $$ 字符的函数。使用 psql -f < filename> 导入文件没有问题。

如果想使用 SQLExec 任务通过 ant 导入文件，我会得到如下异常:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "$"

有没有办法导入包含$$的文件？

在 postgres 日志中，似乎 SQLExec 任务将 $$ 转换为 $，这导致了错误。

错误:第 87 个字符处的“$”处或附近存在语法错误语句:CREATE FUNCTION process_create_servicenumber() RETURNS 触发器 LANGUAGE plpgsql AS $BEGIN IF (TG_OP = 'DELETE') THEN RETURN OLD

这是我的方法

protected void importNewDbFromDumpFile() {

    final class SqlExecuter extends SQLExec {
        public SqlExecuter() {
            Project project = new Project();
            project.init();
            setProject(project);
            setTaskType("sql");
            setTaskName("sql");
        }
    }
    try {
        SqlExecuter executer = new SqlExecuter();
        executer.setSrc(new File(dbDumpFileLocation));
        executer.setClasspath(createClasspath());
        executer.setEscapeProcessing(true);
        executer.setDriver("org.postgresql.Driver");
        executer.setUrl("jdbc:postgresql://localhost/test");
        executer.setPassword("test");
        executer.setUserid("manager");
        executer.execute();
    } catch (Exception e) {
        log.info("Exception importing database ...", e);
    }
}

Answer 1

$$只是 dollar-quoting 的最低限度。通过在美元之间放置一个字符串，使其(大大!)不太可能与所包含文字中的字符串发生冲突:

CREATE OR REPLACE FUNCTION time_to_sec(timepoint timestamp with time zone)
  RETURNS bigint LANGUAGE plpgsql AS
<b>$BODY$</b>
DECLARE
 seconds bigint;
 secondsFromEpoch bigint;
 secondsFromMidnight bigint;
BEGIN
 secondsFromEpoch = EXTRACT(EPOCH FROM timepoint)::bigint;
 secondsFromMidnight = EXTRACT(EPOCH FROM CURRENT_TIMESTAMP::date)::bigint;
 seconds = secondsFromEpoch - secondsFromMidnight;
 return seconds;
END;
<b>$BODY$;</b>

更多建议

• plpgsql 中的赋值运算符是 := 。 =没有记录，可能会在未来的版本中消失。更多内容在此related question .

• 使用 CURRENT_DATE 而不是CURRENT_TIMESTAMP::date .

• 这是允许的，但我建议不要在 plpgsql 中使用混合大小写的参数名称。它们不区分大小写。

• 最重要的是，简化:

  CREATE OR REPLACE FUNCTION time_to_sec2(timepoint timestamp with time zone)
    RETURNS bigint LANGUAGE plpgsql STABLE AS
  $BODY$
  BEGIN
      RETURN EXTRACT(EPOCH FROM timepoint - current_date)::bigint;
  END;
  $BODY$;
  

  或者甚至:

  CREATE OR REPLACE FUNCTION time_to_sec3(timepoint timestamp with time zone)
    RETURNS bigint LANGUAGE sql AS
  $BODY$
      SELECT EXTRACT(EPOCH FROM timepoint - current_date)::bigint;
  $BODY$;
  

• 可以声明 STABLE !

Also note that the current_timestamp family of functions qualify as stable, since their values do not change within a transaction.

• 还有密切相关的函数 age() 在 PostgreSQL 中，做的几乎相同，但不完全相同:它返回带有标准年份和月份的“符号”结果。因此，表达式为age()可以在较长时间内产生不同的结果。

这些都是等效的 - 除了最后两个偏离较长时间的情况:

WITH x(t) AS (VALUES ('2012-07-20 03:51:26+02'::timestamptz))
SELECT time_to_sec(t)  AS t1
      ,time_to_sec2(t) AS t2
      ,time_to_sec3(t) AS t3
      ,EXTRACT(EPOCH FROM t - current_date)::bigint AS t4
      ,EXTRACT(EPOCH FROM age(t, current_date))::bigint AS t5 -- deviates
      ,EXTRACT(EPOCH FROM age(t))::bigint * -1  AS t6  -- deviates
FROM   x;

<小时/>

关于原来的问题:这个 PostgreSQL 错误消息并不一定意味着问题出在美元符号上:

ERROR: syntax error at or near "$"

大多数时候它是缺失的 ;在那条线之前。或者可能是 XML 中未转义的特殊字符，例如 < > & ？美元符号$应该没事。但我不是 Ant 专家。 PostgreSQL 日志中应该有更多上下文。