Howto use the Postgres unaccent function in a query expression

[cm253]

Hi!

I would like to add the custom Postgres function unaccent (from the Postgres unaccent extension). Basically it should possible to do a ilike search using the unaccent characters from a text column, for example:

SELECT * from table1 WHERE unaccent(column1) ILIKE unaccent('%search%');

For this I implemented a custom function and also monkey-patched the Field object:

def unaccent(db: DAL, val: str):
    def op(val, query_env):
        return 'unaccent(%s)' % db._adapter.expand(val, field_type='string', query_env=query_env)

    return Expression(db=db, op=op, first=val, type='string')


Field.unaccent = lambda field: Expression(db=field.db, op='unaccent(%s)' % field._rname, type='string')

It basically works for the following query:

search = search.replace('%', '!%')
search = f'%{search}%'
expression = unaccent(db, search)
db(db.table1.column1.unaccent.ilike(expression, escape='!')).select()

Now I have a few questions to the devs:

• Is there a better way how to extend the existing Postgres adapter for this new unaccent function? I saw in the pyDAL sources that there are a few decorators available like the @register_expression but I was not able to use them. Should it be possible to inherit the Postgres adapter and add the new functionality?
• Is the unaccent function safe in terms of SQL injection? I tried to find out how pyDAL uses parameterized queries, but for me it seams the query parameters are added directly to the SQL statement. But I guess pyDAL does some kind of escaping ...

I guess this issue is interesting for other devs as well how to extend an adapter.

Thanks for your help.

BR Chris