# Unable to create function in Aurora postgres

**URL:** <https://forum.liquibase.org/t/unable-to-create-function-in-aurora-postgres/7965>\
**Category:** Liquibase Development\
**Created:** [March 23, 2023, 12:35pm UTC](https://forum.liquibase.org/t/unable-to-create-function-in-aurora-postgres/7965 "2023-03-23T12:35:58Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![rkagr](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.liquibase.org/rkagr/32/941_2.png) [@rkagr](https://forum.liquibase.org/u/rkagr)\
**Post date:** [March 23, 2023, 12:35pm UTC](https://forum.liquibase.org/t/unable-to-create-function-in-aurora-postgres/7965/1 "2023-03-23T12:35:58Z")

</div>

I am working on a usecase in which I want to use DMS to migrate data from Aurora postgres. To be able to use any user other than the admin users, we need to create some resource in the Aurora postgres database for the DMS to function properly.  
You can refer this page: [Using a PostgreSQL database as an AWS DMS source - AWS Database Migration Service](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html)

I want to create the resources in the database using liquibase. I have created the following yaml file to create the resource:

```auto
databaseChangeLog:
  - preConditions:
        - runningAs: 
            username: inquiry-es-migration
  - changeSet:
      id: 66
      author: r.agrawal
      changes:
        - sql:
            dbms: postgres
            splitStatements: false
            endDelimiter: ';;'
            sql:
                CREATE OR REPLACE FUNCTION awsdms_intercept_ddl()
                  RETURNS event_trigger
                LANGUAGE plpgsql
                SECURITY DEFINER
                  AS $$
                  declare _qry text;
                BEGIN
                  if (tg_tag='CREATE TABLE' or tg_tag='ALTER TABLE' or tg_tag='DROP TABLE') then
                        SELECT current_query() into _qry;
                        insert into awsdms_ddl_audit
                        values
                        (
                        default,current_timestamp,current_user,cast(TXID_CURRENT()as varchar(16)),tg_tag,0,'',current_schema,_qry
                        );
                        delete from awsdms_ddl_audit;
                end if;
                END;
                $$;

```

I used the endDelimiter as ‘;;’ because without that Liquibase was considering the SQL statement to be till the first ‘;’ and it was failing.

When I try to validate the above code, it passes. when I execute it, it shows no problem and an entry is also made in the databasechangelog table in the database but the resource is not created.  
Please guide me, how can I fix it.

---

<div class="post-metadata">

**Author:** ![rkagr](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.liquibase.org/rkagr/32/941_2.png) [@rkagr](https://forum.liquibase.org/u/rkagr)\
**Post date:** [April 17, 2023, 7:40am UTC](https://forum.liquibase.org/t/unable-to-create-function-in-aurora-postgres/7965/2 "2023-04-17T07:40:29Z")

</div>

@daryldoak ?? could you please help me here?

---

<div class="post-metadata">

**Author:** ![daryldoak](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.liquibase.org/daryldoak/32/764_2.png) [@daryldoak](https://forum.liquibase.org/u/daryldoak)\
**Post date:** [April 17, 2023, 2:05pm UTC](https://forum.liquibase.org/t/unable-to-create-function-in-aurora-postgres/7965/3 "2023-04-17T14:05:36Z")

</div>

I’m not seeing a double semicolon in your changeset. I would expect to see something like this:

$$  
;;

Can you provide the Liquibase output showing the sql statement was executed?

---

<div class="post-metadata">

**Author:** ![rkagr](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.liquibase.org/rkagr/32/941_2.png) [@rkagr](https://forum.liquibase.org/u/rkagr)\
**Post date:** [April 21, 2023, 8:45pm UTC](https://forum.liquibase.org/t/unable-to-create-function-in-aurora-postgres/7965/4 "2023-04-21T20:45:03Z")

</div>

@daryldoak  
The script is

```auto
databaseChangeLog:
  - preConditions:
        - runningAs: 
            username: <responsible_user>
  - changeSet:
      id: 70
      author: user
      changes:
        - sql:
            dbms: postgres
            splitStatements: false
            endDelimiter: ';;'
            sql:
                CREATE OR REPLACE FUNCTION awsdms_intercept_ddl()
                  RETURNS event_trigger
                LANGUAGE plpgsql
                SECURITY DEFINER
                  AS $$
                  declare _qry text;
                BEGIN
                  if (tg_tag='CREATE TABLE' or tg_tag='ALTER TABLE' or tg_tag='DROP TABLE') then
                        SELECT current_query() into _qry;
                        insert into awsdms_ddl_audit
                        values
                        (
                        default,current_timestamp,current_user,cast(TXID_CURRENT()as varchar(16)),tg_tag,0,'',current_schema,_qry
                        );
                        delete from awsdms_ddl_audit;
                end if;
                END;
                $$;
                ;;

```

and the output is  
Starting Liquibase at 22:41:20 (version 4.18.0 #5864 built at 2022-12-02 18:02+0000)  
Liquibase Version: 4.18.0  
Liquibase Community 4.18.0 by Liquibase  
Running Changeset: changelogs/\<FILE\_NAME\>::70::user  
Liquibase command ‘update’ was executed successfully.

but no function is created in the database

---

<div class="post-metadata">

**Author:** ![daryldoak](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.liquibase.org/daryldoak/32/764_2.png) [@daryldoak](https://forum.liquibase.org/u/daryldoak)\
**Post date:** [April 21, 2023, 8:51pm UTC](https://forum.liquibase.org/t/unable-to-create-function-in-aurora-postgres/7965/5 "2023-04-21T20:51:44Z")

</div>

Please remove the changeset row from the databasechangelog table, add the following to your liquibase execution so the sql is echoed to the output, and then execute again:

–sql-log-level=INFO