使用Spring JDBC用脚本初始化数据库失败

Question

我正在尝试用一个视图和一个规则来初始化我的数据库，遵循75.3使用Spring JDBC初始化数据库。

75.3使用Spring JDBC初始化数据库

Spring JDBC有一个数据源初始化器特性。Spring Boot默认情况下启用它，并从标准位置schema.SQL和data.SQL（在类路径的根目录中）加载SQL。此外，Spring Boot将加载模式-${platform}.sql和数据-${platform}.sql文件（如果存在），其中platform是Spring.datasource.platform的值，例如，您可以选择将其设置为数据库的供应商名称(hsqldb、h2、oracle、mysql、postgresql等）。Spring Boot默认情况下启用Spring JDBC初始化器的fail-fast特性，因此如果脚本导致异常，应用程序将无法启动。可以通过设置spring.datasource.schema和spring.datasource.data来更改脚本位置，如果spring.datasource.initialize=false，则这两个位置都不会被处理。

本节指出，如果我放置了schema-postgresql.sql，它应该使用文件包含的脚本初始化数据库。

不幸的是，脚本以以下错误结束

原因：org.postgresql.util.psqlexception：输入SQL状态结尾的语法错误位置:org.postgresql.core.v3.QueryExecutorImpl.ReceiveErrorResponse(QueryExecutorImpl.java:2310)~[Postgresql.core.v3.QueryExecutorImpl.jre7.jar:9.4.1209.jre7]在mes省略

但是，如果我从PGAdminisii运行此脚本，则不会出现任何错误，并且创建具有相应规则的视图也不会出现任何问题。

我在这里做错了什么？

这是我的Spring Boot示例的结构来再现它。

package com.example.model;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;

@Entity
public class Person implements Serializable {

    private static final long serialVersionUID = 1334414548362400146L;

    @Id
    private long id;

    @Column(nullable = false, length = 100)
    private String name = "";

    @Column(nullable = false, length = 100)
    private String surname = "";

}

package com.example.model;

import java.io.Serializable;

import javax.persistence.EmbeddedId;
import javax.persistence.Entity;

@Entity
public class PersonRole implements Serializable {
    private static final long serialVersionUID = -3953147119216643027L;

    @EmbeddedId
     private PersonRoleKey primaryKey;
}

package com.example.model;

import java.io.Serializable;

import javax.persistence.Embeddable;
import javax.persistence.EnumType;
import javax.persistence.Enumerated;
import javax.persistence.ForeignKey;
import javax.persistence.ManyToOne;
import javax.persistence.PrimaryKeyJoinColumn;

@Embeddable
public class PersonRoleKey implements Serializable {

    private static final long serialVersionUID = 2105526364632711640L;

    @ManyToOne(optional = false)
    @PrimaryKeyJoinColumn(foreignKey = @ForeignKey(name = "person_fk"))
    private Person person;

    @Enumerated(EnumType.STRING)
    private Role role;

}

package com.example.model;

public enum Role {
    ADMIN, USER;
}

package com.example;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class DemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }
}

#Database configuration
spring.datasource.url: jdbc:postgresql://localhost:5432/postgres
spring.datasource.driverClassName: org.postgresql.Driver
spring.datasource.username: postgres
spring.datasource.password: postgres
spring.datasource.platform: postgresql
spring.datasource.continue-on-error: false

spring.jpa.properties.hibernate.dialect: org.hibernate.dialect.PostgreSQLDialect
spring.jpa.properties.hibernate.format_sql: true
spring.jpa.generate-ddl: true
spring.jpa.hibernate.ddl-auto: update
#default means org.hibernate.boot.model.naming.ImplicitNamingStrategyJpaCompliantImpl
spring.jpa.properties.hibernate.implicit_naming_strategy: default
spring.jpa.hibernate.naming.physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
#spring.jpa.properties.hibernate.implicit_naming_strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyHbmImpl
spring.jpa.properties.hibernate.auto_quote_keyword: true
spring.jpa.show-sql: false

CREATE OR REPLACE VIEW v_peoples_roles AS
 SELECT p.id,
    p.name,
    p.surname,
    pr.role
   FROM (person p
     JOIN personrole pr ON ((p.id = pr.person_id)));

CREATE OR REPLACE RULE insert_v_peoples_roles AS
    ON INSERT TO v_peoples_roles DO INSTEAD ( INSERT INTO person (id, name, surname)
  VALUES (new.id, new.name, new.surname);
 INSERT INTO personrole (person_id, role)
  VALUES (new.id, new.role);
);

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.example</groupId>
    <artifactId>demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>demo</name>
    <description>Demo project for Spring Boot</description>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.4.0.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <properties>
        <hibernate.version>5.2.2.Final</hibernate.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>


</project>

整个代码可以从这里下载。

根据POZ的注释，我将schema-postgresql.sql更改为

CREATE OR REPLACE VIEW v_peoples_roles AS
 SELECT p.id,
    p.name,
    p.surname,
    pr.role
   FROM (person p
     JOIN personrole pr ON ((p.id = pr.person_id)));

CREATE OR REPLACE FUNCTION insert_into_v_people_roles() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO person (id, name, surname) VALUES (new.id, new.name, new.surname);
  INSERT INTO personrole (person_id, role) VALUES (new.id, new.role);
  RETURN new;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS insert_v_peoples_roles ON v_peoples_roles;
CREATE TRIGGER insert_v_peoples_roles INSTEAD OF INSERT ON v_peoples_roles FOR EACH ROW EXECUTE PROCEDURE insert_into_v_people_roles();

但它产生了另一个错误，与预测的完全一样。

Answer 1

因为波兹没有贴出他自己的答案，一段时间过去了，我自己做。

将创建规则更改为而不是触发，并将$$-引用更改为'-引用解决了问题。唯一的问题是我必须转义函数定义中的所有撇号。但并不是那么痛苦。