java - 在 Spring Boot 中从文件加载 SQL 触发器

Question

我正在尝试从资源文件夹中的 sql 文件加载自定义数据库触发器。

在我的测试类中，我添加了这个注解 @Sql("classpath:custom_script.sql")

在这个文件中，我有 PostgreSQL 数据库的触发器。当我从 PgAdmin 的查询工具执行此脚本时，它们工作正常，但是当我从 Spring Boot 应用程序中的测试之前加载它时，出现以下错误:

org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of class path resource [custom_script.sql]: CREATE OR REPLACE FUNCTION MY_TRIGGER_PROC() RETURNS TRIGGER AS $CUSTOM_TRG$ DECLARE FOO_V INTEGER; nested exception is org.postgresql.util.PSQLException: Unterminated dollar quote started at position 64 in SQL CREATE OR REPLACE FUNCTION MY_TRIGGER_PROC() RETURNS TRIGGER AS $CUSTOM_TRG$ DECLARE FOO_V INTEGER. Expected terminating $$

在 custom_script.sql 中，我有三个相关的触发器，如下所示:

CREATE OR REPLACE FUNCTION MY_TRIGGER_PROC()
  RETURNS TRIGGER AS $CUSTOM_TRG$
DECLARE FOO_V INTEGER;
BEGIN
  SELECT COUNT(F.ID)
  INTO FOO_V
  FROM FOO_TABLE F;

  IF FOO_V > 0
  THEN
    RAISE EXCEPTION 'CUSTOM EXCEPTION ERROR MESSAGE FOR ID ', NEW.ID
    USING ERRCODE = 'restrict_violation';
  END IF;
  RETURN NEW;
END;
$CUSTOM_TRG$
LANGUAGE plpgsql;

CREATE TRIGGER CUSTOM_TRG
  BEFORE INSERT
  ON FOO_TABLE
  FOR EACH ROW
EXECUTE PROCEDURE MY_TRIGGER_PROC();

我预计错误是由于分隔符或多行问题导致的，因此我将这些属性添加到属性文件中:

spring.jpa.properties.hibernate.connection.charSet=UTF-8
spring.jpa.properties.hibernate.hbm2ddl.import_files_sql_extractor=org.hibernate.tool.hbm2ddl.MultipleLinesSqlCommandExtractor
spring.datasource.separator=^;

并且还可以编辑我的脚本，如下面的代码所示，但没有帮助。你能帮我吗？谢谢。

编辑脚本:

CREATE OR REPLACE FUNCTION MY_TRIGGER_PROC()
  RETURNS TRIGGER AS $CUSTOM_TRG$
DECLARE FOO_V INTEGER;
BEGIN
  SELECT COUNT(F.ID)
  INTO FOO_V
  FROM FOO_TABLE F;

  IF FOO_V > 0
  THEN
    RAISE EXCEPTION 'CUSTOM EXCEPTION ERROR MESSAGE FOR ID ', NEW.ID
    USING ERRCODE = 'restrict_violation';
  END IF;
  RETURN NEW;
END;
$CUSTOM_TRG$
LANGUAGE plpgsql^;

CREATE TRIGGER CUSTOM_TRG
  BEFORE INSERT
  ON FOO_TABLE
  FOR EACH ROW
EXECUTE PROCEDURE MY_TRIGGER_PROC()^;

Answer 1

一个解决方案是在函数定义周围使用单引号而不是此处使用的 $CUSTOM_TRG$；在类似的情况下，应该再次放弃 $$ 美元报价，转而使用简单的单引号。但是，整个定义的单引号需要在函数定义中转义引号，如 Postgresql 文档中所述:

It is often helpful to use dollar quoting (see Section 4.1.2.4) to write the function definition string, rather than the normal single quote syntax. Without dollar quoting, any single quotes or backslashes in the function definition must be escaped by doubling them.

因此，在您的示例中，您需要将函数定义中的单引号加倍，出现在 FOO_V > 0 的逻辑中。