CODEWITHSUNDEEP
pythondjangopostgresqlsearch
moddayjob
moddayjob

Reputation: 708

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

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.

Upvotes: 1

Views: 3106

Answers (1)

schillingt
schillingt

Reputation: 13731

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)]

Upvotes: 2

Related Questions