包含$$字符SQLEXEC执行文件 [英] Execute file with SQLExec containing $$ characters

问题描述

我创建使用pg_dump的一个SQL转储文件。此导出文件包含包含$$字符功能。没有问题，用psql -f＆LT导入的文件;文件名>。

如果要导入的文件与蚂蚁使用SQLEXEC任务，我得到这样一个例外：

  org.postgresql.util.PSQLException：错误：语法错误或接近$
 

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

在Postgres的日志似乎SQLEXEC任务转换$$为$其中的原因错误。

错误：语法错误或接近$字符在87
声明：CREATE FUNCTION process_create_servicenumber（）返回触发语言PLPGSQL AS $ BEGIN IF（TG_OP =删除），然后返回OLD

下面我的方法

 保护无效importNewDbFromDumpFile（）{    最后一类SqlExecuter扩展SQLEXEC {
        公共SqlExecuter（）{
            项目项目=新的项目（）;
            project.init（）;
            setProject（项目）;
            setTaskType（SQL）;
            setTaskName（SQL）;
        }
    }
    尝试{
        SqlExecuter执行器=新SqlExecuter（）;
        executer.setSrc（新文件（dbDumpFileLocation））;
        executer.setClasspath（createClasspath（））;
        executer.setEscapeProcessing（真）;
        executer.setDriver（org.postgresql.Driver）;
        executer.setUrl（JDBC：在PostgreSQL：//本地主机/测试）;
        executer.setPassword（测试）;
        executer.setUserid（经理）;
        executer.execute（）;
    }赶上（例外五）{
        log.info（异常导入数据库......，E）;
    }
}
 

解决方案

$ 只是最低限度的<一个href=\"http://www.postgresql.org/docs/current/interactive/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING\"相对=nofollow> 美元报价 。让它（多！）不太可能通过将美元之间的字符串，在封闭的文字字符串相冲突：

 
CREATE OR REPLACE FUNCTION TIME_TO_SEC（时间点时间戳和时区）
  退货BIGINT语言PLPGSQL AS
 $ BODY $ 
宣布
 秒BIGINT;
 secondsFromEpoch BIGINT;
 secondsFromMidnight BIGINT;
开始
 secondsFromEpoch = EXTRACT（EPOCH从时间点）:: BIGINT;
 secondsFromMidnight = EXTRACT（EPOCH FROM CURRENT_TIMESTAMP ::日期）:: BIGINT;
 秒= secondsFromEpoch  -  secondsFromMidnight;
 返回秒;
结束;
 $ BODY $; 
 

更多建议

• 在PLPGSQL赋值运算符是：= 。 = 是无证和可能消失在未来的版本。更多此相关问题下。




• 使用<一个href=\"http://www.postgresql.org/docs/current/interactive/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT\"相对=nofollow> CURRENT_DATE 而不是 CURRENT_TIMESTAMP ::日期。




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




• 更​​重要的是，简化

    CREATE OR REPLACE FUNCTION time_to_sec2（时间点时间戳和时区）
    RETURNS BIGINT语言PLPGSQL稳定A​​S
  $ BODY $
  开始
      RETURN EXTRACT（EPOCH从时间点 -  CURRENT_DATE）:: BIGINT;
  结束;
  $ BODY $;
   

  甚至

    CREATE OR REPLACE FUNCTION time_to_sec3（时间点时间戳和时区）
    退货BIGINT语言SQL AS
  $ BODY $
      SELECT EXTRACT（EPOCH从时间点 -  CURRENT_DATE）:: BIGINT;
  $ BODY $;
   




• 可以声明 STABLE ！

  

另外请注意函数的current_timestamp家族限制为
  稳定的，因为它们的值不会在事务中改变。

• 还有一个密切相关的函数 年龄（） PostgreSQL中做的差不多了，但不完全，一样：它返回一个象征性标准，年和月的结果。因此，前pression年龄（）可以产生不同的结果的时间较长。

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

 以x（t）的AS（VALUES（'2012-07-20 03：51：26 + 02':: timestamptz））
SELECT TIME_TO_SEC（T）为T1
      ，time_to_sec2（T）为T2
      ，time_to_sec3（T）为T3
      ，EXTRACT（EPOCH FROM笔 -  CURRENT_DATE）:: BIGINT AS T4
      ，EXTRACT（EPOCH FROM年龄（T，CURRENT_DATE））:: BIGINT AS T5  - 偏离
      ，EXTRACT（EPOCH FROM年龄（T））:: BIGINT * -1 AS T6  - 偏离
从X;
 

---

至于原来的问题：这个PostgreSQL的错误消息并不一定意味着问题是美元符号：

  

错误：语法错误或接近$

大多数是缺少的时间; 在该行之前。或者，也许一个未逃过XML特殊字符，如＆LT; ＆GT; ＆安培; ？美元符号 $ 应该罚款。但我跟蚂蚁没有专家。应该有PostgreSQL的日志的更多内容。

I've created a sql dump file using pg_dump. This export file contains functions which contain $$ characters. No problem to import the file with psql -f < filename>.

If want to import the file with ant using the SQLExec task, I get an exception like:

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

Is there a way to import a file containing $$?

In the postgres Log it seems that the SQLExec tasks converts $$ to $ which the causes the error.

ERROR: syntax error at or near "$" at character 87 STATEMENT: CREATE FUNCTION process_create_servicenumber() RETURNS trigger LANGUAGE plpgsql AS $ BEGIN IF (TG_OP = 'DELETE') THEN RETURN OLD

Here my method

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);
    }
}

解决方案

$$ is just the bare minimum for dollar-quoting. Make it (much!) less likely to conflict with strings in the enclosed literal by putting a string between the dollars:

CREATE OR REPLACE FUNCTION time_to_sec(timepoint timestamp with time zone)
  RETURNS bigint LANGUAGE plpgsql AS
$BODY$
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;
$BODY$;

More advice

• The assignment operator in plpgsql is :=. = is undocumented and may go away in future releases. More under this related question.

• Use CURRENT_DATE instead of CURRENT_TIMESTAMP::date.

• It is allowed, but I would advise not to use mixed case parameter names in plpgsql. They are case insensitive.

• Most importantly, simplify:

  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$;
  

  Or even:

  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$;
  

• Can be declared STABLE!

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

• There is also the closely related function age() in PostgreSQL doing almost, but not quite, the same: it returns a "symbolic" result with standard-years and months. Therefore, expression with age() can yield different results for longer periods of time.

These are all equivalent - except for the last two deviating with longer periods of time:

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;

---

As to the original question: this PostgreSQL error message does not necessarily mean the problem is with the dollar sign:

ERROR: syntax error at or near "$"

Most of the time it's a missing ; before that line. Or maybe an un-escaped special characters in XML, like < > & ? The dollar sign $ should be fine. But I am no expert with ant. There should be more context in the PostgreSQL log.

这篇关于包含$$字符SQLEXEC执行文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！