时间戳

首页 > 解决方案 > Liquibase 通过 Springboot 执行 postgres 方法

java - Liquibase 通过 Springboot 执行 postgres 方法

问题描述

我正在尝试在springboot中使用liquibase执行下面的sql方法，除了可以执行任何sql查询的方法。

下面的查询在 psql 终端中执行时不会出错，可以正常工作。

--changeset aequalis:1565334092800-33
CREATE OR REPLACE FUNCTION process_document_history() RETURNS TRIGGER AS $document_history$
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO document_history SELECT 'D', now(), OLD.*;
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO document_history SELECT 'U', now(), OLD.*;
            RETURN OLD;
        END IF;
        RETURN NULL;
    END;
$document_history$ LANGUAGE plpgsql;

通过 liquibase 执行上述行时引发的错误。

        org.springframework.beans.factory.BeanCreationException: 
    Error creating bean with name 'liquibase' defined in class path resource [org/springframework/boot/autoconfigure/liquibase/LiquibaseAutoConfiguration$LiquibaseConfiguration.class]: 
    Invocation of init method failed; nested exception is liquibase.exception.MigrationFailedException: 
Migration failed for change set db/changelog/changes/version/db.changelog.source-v3.sql::1565334092800-33::aequalis:
             
    Reason: liquibase.exception.DatabaseException: 
    
    Unterminated dollar quote started at position 73 in SQL CREATE OR REPLACE FUNCTION process_document_history() RETURNS TRIGGER AS $document_history$
            BEGIN
                IF (TG_OP = 'DELETE') THEN
                    INSERT INTO document_history SELECT 'D', now(), OLD.*. Expected terminating $$ [Failed SQL: CREATE OR REPLACE FUNCTION process_document_history() RETURNS TRIGGER AS $document_history$
            BEGIN
                IF (TG_OP = 'DELETE') THEN
                    INSERT INTO document_history SELECT 'D', now(), OLD.*]

标签： javapostgresqlspring-bootdatabase-triggerliquibase-hibernate

解决方案

我不得不在单独的 liquibvase sql 文件中使用该查询，而无需

--liquibase formatted sql

liquibase 使用 databasechangelog 表中的原始 id 执行文件

推荐阅读