django.db.utils.ProgrammingError: syntax error at or near "FUNCTION"

Question

I am following Optimizing Full Text Search in Django article and at some point the article says:

In order to add a trigger we need to craft a manual Django migration. This will add the trigger function and update all of our Pages rows to ensure the trigger is fired and content_search is populated at migration time for our existing records.

from django.db import migrations
class Migration(migrations.Migration):
    dependencies = [
        ('web', '0002_auto_20200115_1401')
    ]
    migration = '''
        CREATE TRIGGER content_search_update BEFORE INSERT OR UPDATE
        ON web_page FOR EACH ROW EXECUTE FUNCTION
        tsvector_update_trigger(content_search, 'pg_catalog.english', content);

        -- Force triggers to run and populate the text_search column.
        UPDATE web_page set ID = ID;
    '''
    reverse_migration = '''
        DROP TRIGGER content_search ON web_page;
    '''
    operations = [migrations.RunSQL(migration, reverse_migration)]

when I run the migration I get this error:

django.db.utils.ProgrammingError: syntax error at or near "FUNCTION"

One difference of my settings.py from the tutorial article is that it uses django.db.backends.postgresql as database engine and I use django.db.backends.postgresql_psycopg2 What can be the problem here and more importantly how can I solve this? Thanks in advance.

schillingt · Accepted Answer · 2020-01-15 15:13:05Z

2

I believe you need to use PROCEDURE instead of FUNCTION.

from django.db import migrations
class Migration(migrations.Migration):
    dependencies = [
        ('web', '0002_auto_20200115_1401')
    ]
    migration = '''
        CREATE TRIGGER content_search_update BEFORE INSERT OR UPDATE
        ON web_page FOR EACH ROW EXECUTE PROCEDURE
        tsvector_update_trigger(content_search, 'pg_catalog.english', content);

        -- Force triggers to run and populate the text_search column.
        UPDATE web_page set ID = ID;
    '''
    reverse_migration = '''
        DROP TRIGGER content_search ON web_page;
    '''
    operations = [migrations.RunSQL(migration, reverse_migration)]

answered Jan 15, 2020 at 15:13

schillingt

13.8k3 gold badges34 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

moddayjob Over a year ago

That worked. Thank you very much. Is this because of the database engine difference? Specific to psycopg2-binary? I'll accept this answer in 10 minutes.

schillingt Over a year ago

Nope. I did a search for how to create a trigger in postgres and you use CREATE FUNCTION to create the function, but EXECUTE PROCEDURE to call that function as a part of a trigger. This is purely a postgres syntax issue.

Collectives™ on Stack Overflow

django.db.utils.ProgrammingError: syntax error at or near "FUNCTION"

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related