`pq: syntax error at or near "."` error received when calling functions with schema in front.

[hfhchan-plb]

Description

pq: syntax error at or near "." appearing on a migration script where schema is specified before a function.

Same migration works when deployed via 2.28.0.

• Version: 2.29.0
• Database: postgres
• Operating System: linux (Github CI)

Steps To Reproduce

All 3 versions of this failed to migrate on v2.29.0 invoked via the command line:

-- migrate:up
SELECT audit.attach_trigger('public.table_name', 'security_id');

-- migrate:down
DROP TRIGGER table_name_audit_insert ON public.table_name;
DROP TRIGGER table_name_audit_update ON public.table_name;
DROP TRIGGER table_name_audit_delete ON public.table_name;
DROP TRIGGER table_name_audit_truncate ON public.table_name;

-- migrate:up
SELECT "audit"."attach_trigger"('public.table_name', 'security_id');

-- migrate:down
DROP TRIGGER table_name_audit_insert ON public.table_name;
DROP TRIGGER table_name_audit_update ON public.table_name;
DROP TRIGGER table_name_audit_delete ON public.table_name;
DROP TRIGGER table_name_audit_truncate ON public.table_name;

-- migrate:up
SELECT * FROM "audit"."attach_trigger"('public.table_name', 'security_id');

-- migrate:down
DROP TRIGGER table_name_audit_insert ON public.table_name;
DROP TRIGGER table_name_audit_update ON public.table_name;
DROP TRIGGER table_name_audit_delete ON public.table_name;
DROP TRIGGER table_name_audit_truncate ON public.table_name;

First version succeeded when running with dbmate v2.28.0 (executed via yarn). Other versions weren't tested.

All 3 versions suceeded when running directly on DBeaver connected to the database.

Expected Behavior
There should not be a warning of syntax error. The syntax is valid and accepted by PG, seemingly only being rejected by lib/pq.

[dossy]

@hfhchan-plb I don't know if it's something specific to what your audit.attach_trigger() function is trying to do, or your Postgres configuration, but I tried to reproduce this and couldn't.

# dist/dbmate --version
dbmate version 2.29.0

# mkdir -p issue_722

# cat <<-'EOT' >issue_722/001_pg_schema_in_function_call.sql 
-- migrate:up
create schema dbmate_test_functions;

create table test_table (x int);

create function dbmate_test_functions.test_function()
returns void
language sql
as $$ insert into test_table (x) values (123) $$;

select dbmate_test_functions.test_function();

-- migrate:down
drop schema dbmate_test_functions cascade;
EOT

# dist/dbmate -u $POSTGRES_TEST_URL -d issue_722
Creating: dbmate_test
Applying: 001_pg_schema_in_function_call.sql
Applied: 001_pg_schema_in_function_call.sql in 3.744256ms
Writing: ./db/schema.sql

# PGPASSWORD=postgres psql -hpostgres -Upostgres dbmate_test
psql (17.5 (Debian 17.5-1), server 10.21 (Debian 10.21-1.pgdg90+1))
Type "help" for help.

dbmate_test=# select * from test_table;
  x  
-----
 123
(1 row)

dbmate_test=# \q

# dist/dbmate -u $POSTGRES_TEST_URL -d issue_722 down
Rolling back: 001_pg_schema_in_function_call.sql
Rolled back: 001_pg_schema_in_function_call.sql in 4.853519ms
Writing: ./db/schema.sql

Some questions for you:

• Can you try and create a minimal reproducible example of the issue?
• What OS/platform is your Postgres server running?
• What version of Postgres?
• What OS/platform are you running dbmate from?

Once I can reproduce the problem, I can take a deeper look into what might be causing it. Thanks!

[hfhchan-plb]

here's my attach trigger function:

-- DROP FUNCTION audit.attach_trigger(regclass, text);

CREATE OR REPLACE FUNCTION audit.attach_trigger(_tbl regclass, pk_col text DEFAULT 'id'::text)
 RETURNS void
 LANGUAGE plpgsql
 STRICT
AS $function$
DECLARE
  trig text;
BEGIN
  trig := FORMAT('audit.audit_trigger(%L)', pk_col);

  EXECUTE FORMAT($$
CREATE TRIGGER %s_audit_insert
    AFTER INSERT
    ON %I
    REFERENCING NEW TABLE AS new_table
    FOR EACH STATEMENT
EXECUTE FUNCTION %s;
$$, _tbl, _tbl, trig);

  EXECUTE FORMAT($$
CREATE TRIGGER %s_audit_update
    AFTER UPDATE
    ON %I
    REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
    FOR EACH STATEMENT
EXECUTE FUNCTION %s;
$$, _tbl, _tbl, trig);

  EXECUTE FORMAT($$
CREATE TRIGGER %s_audit_delete
    AFTER DELETE
    ON %I
    REFERENCING OLD TABLE AS old_table
    FOR EACH STATEMENT
EXECUTE FUNCTION %s;
$$, _tbl, _tbl, trig);

  EXECUTE FORMAT($$
CREATE TRIGGER %s_audit_truncate
    AFTER TRUNCATE
    ON %I
    FOR EACH STATEMENT
EXECUTE FUNCTION %s;
        $$, _tbl, _tbl, trig);
END;
$function$
;

[hfhchan-plb]

I downgraded to v2.28.0 on the project and it still errors.

Meanwhile the exact same migration script seems to run fine on another project when invoked via yarn dbmate migrate instead.

[hfhchan-plb]

OK potential cause of difference - the project where this fails has configured default search_path

[hfhchan-plb]

I managed to get it work by adding a DO$$ BEGIN END $$...

-- migrate:up

DO $$
BEGIN
  PERFORM audit.attach_trigger('public.table_name', 'security_id');
END
$$;

-- migrate:down
DROP TRIGGER table_name_audit_insert ON public.table_name;
DROP TRIGGER table_name_audit_update ON public.table_name;
DROP TRIGGER table_name_audit_delete ON public.table_name;
DROP TRIGGER table_name_audit_truncate ON public.table_name;

[dossy]

Here's my attempt to reproduce the issue:

# mkdir issue_722_20260130

# cat >issue_722_20260130/1_create_schema__audit.sql <<-'EOT'
-- migrate:up
create schema audit;

-- migrate:down
drop schema audit;
EOT

# cat >issue_722_20260130/2_create_table__table_name.sql <<-'EOT'
-- migrate:up
create table table_name (x int);

-- migrate:down
drop table table_name;
EOT

# cat >issue_722_20260130/3_create_function__audit__attach_trigger.sql <<-'EOT'
-- migrate:up

CREATE OR REPLACE FUNCTION audit.attach_trigger(_tbl regclass, pk_col text DEFAULT 'id'::text)
 RETURNS void
 LANGUAGE plpgsql
 STRICT
AS $function$
DECLARE
  trig text;
BEGIN
  trig := FORMAT('audit.audit_trigger(%L)', pk_col);

  EXECUTE FORMAT($$
CREATE TRIGGER %s_audit_insert
    AFTER INSERT
    ON %I
    REFERENCING NEW TABLE AS new_table
    FOR EACH STATEMENT
EXECUTE FUNCTION %s;
$$, _tbl, _tbl, trig);

  EXECUTE FORMAT($$
CREATE TRIGGER %s_audit_update
    AFTER UPDATE
    ON %I
    REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
    FOR EACH STATEMENT
EXECUTE FUNCTION %s;
$$, _tbl, _tbl, trig);

  EXECUTE FORMAT($$
CREATE TRIGGER %s_audit_delete
    AFTER DELETE
    ON %I
    REFERENCING OLD TABLE AS old_table
    FOR EACH STATEMENT
EXECUTE FUNCTION %s;
$$, _tbl, _tbl, trig);

  EXECUTE FORMAT($$
CREATE TRIGGER %s_audit_truncate
    AFTER TRUNCATE
    ON %I
    FOR EACH STATEMENT
EXECUTE FUNCTION %s;
        $$, _tbl, _tbl, trig);
END;
$function$
;

-- migrate:down
EOT

# cat >issue_722_20260130/4_call_function.sql <<-'EOT'
-- migrate:up
SELECT audit.attach_trigger('public.table_name', 'security_id');

-- migrate:down
DROP TRIGGER table_name_audit_insert ON public.table_name;
DROP TRIGGER table_name_audit_update ON public.table_name;
DROP TRIGGER table_name_audit_delete ON public.table_name;
DROP TRIGGER table_name_audit_truncate ON public.table_name;
EOT

# dist/dbmate -e POSTGRES_TEST_URL create
Creating: dbmate_test

# dist/dbmate -e POSTGRES_TEST_URL -d issue_722_20260130 up
# dist/dbmate -e POSTGRES_TEST_URL -d issue_722_20260130 up
Applying: 1_create_schema__audit.sql
Applied: 1_create_schema__audit.sql in 2.365633ms
Applying: 2_create_table__table_name.sql
Applied: 2_create_table__table_name.sql in 2.019041ms
Applying: 3_create_function__audit__attach_trigger.sql
Applied: 3_create_function__audit__attach_trigger.sql in 2.695183ms
Applying: 4_call_function.sql
Applied: 4_call_function.sql in 984.144µs
Error: pq: syntax error at or near "FUNCTION"

# dist/dbmate -e POSTGRES_TEST_URL -d issue_722_20260130 status
[X] 1_create_schema__audit.sql
[X] 2_create_table__table_name.sql
[X] 3_create_function__audit__attach_trigger.sql
[ ] 4_call_function.sql

Applied: 3
Pending: 1

Unfortunately, my expertise with PL/pgSQL is not very deep so I don't know if there's an issue with your trigger function or not. To rule that out, I tried a simplified function just to confirm that it's being invoked from the migration:

# cat >issue_722_20260130/3_create_function__audit__attach_trigger.sql <<-'EOT'
-- migrate:up
CREATE OR REPLACE FUNCTION audit.attach_trigger(_tbl regclass, pk_col text DEFAULT 'id'::text)
 RETURNS void
 LANGUAGE plpgsql
 STRICT
AS $function$
DECLARE
  trig text;
BEGIN
  RAISE NOTICE 'Function called.';
  RAISE LOG 'Function called.';
END;
$function$
;

-- migrate:down
EOT

# dist/dbmate -e POSTGRES_TEST_URL drop
Dropping: dbmate_test

# dist/dbmate -e POSTGRES_TEST_URL create
Creating: dbmate_test

# dist/dbmate -e POSTGRES_TEST_URL -d issue_722_20260130 up --verbose
Applying: 1_create_schema__audit.sql
Rows affected: 0
Applied: 1_create_schema__audit.sql in 2.635257ms
Applying: 2_create_table__table_name.sql
Rows affected: 0
Applied: 2_create_table__table_name.sql in 2.616438ms
Applying: 3_create_function__audit__attach_trigger.sql
Rows affected: 0
Applied: 3_create_function__audit__attach_trigger.sql in 3.948394ms
Applying: 4_call_function.sql
Rows affected: 1
Applied: 4_call_function.sql in 2.624471ms
Writing: ./db/schema.sql

I don't think I can get dbmate to print out the message from RAISE NOTICE but let me check the server logs:

$ docker compose logs postgres
...
postgres-1  | 2026-01-30 20:36:57.523 UTC [196] LOG:  Function called.
postgres-1  | 2026-01-30 20:36:57.523 UTC [196] CONTEXT:  PL/pgSQL function audit.attach_trigger(regclass,text) line 6 at RAISE
postgres-1  | 2026-01-30 20:36:57.523 UTC [196] STATEMENT:  -- migrate:up
postgres-1  | 	SELECT audit.attach_trigger('public.table_name', 'security_id');

Looks like the function was called successfully.

I don't know why you needed to wrap the statement in a DO $$ BEGIN ... END $$; but I'm glad it sounds like you found a solution.