Custom Field methods or custom methods on SQL types?

[mccarthyryanc]

I'm trying to create some custom functions and can't quite figure out how to do it in PyPika.

For example, MS SQL has geometry/geography types with many methods. STArea is one:

SELECT id,geometry FROM tablename WHERE tablename.geometry.STArea() < 10;

In pypika I'm guessing this is the correct syntax:

tablename = Table('tablename')
q = Query.from_(tablename).select(
    tablename.id, tablename.geometry
).where(
    tablename.geometry.STArea() < 10
)

But that obviously gives an AttributeError:

AttributeError: 'Field' object has no attribute 'STArea'

So, should I create custom methods on Field or is there another way to go about using these methods?

[mccarthyryanc]

Following the pypika-gis package. Perhaps something like this is okay?

from pypika.terms import Function

def Area(term, *args):
    return Function(f"{term}.STArea", *args)

q = Query.from_(tablename).select(
    tablename.id, tablename.geometry
).where(
    Area(tablename.geometry) < 10
)

q.get_sql()

Correctly outputs:

'SELECT "id","geometry" FROM "tablename" WHERE "geometry".STArea()<10'

But is using term in an f-string like that safe?

[anhqle]

To be extra safe, could you use term.get_sql() instead of term in the f-string?

[mccarthyryanc]

@anhqle , looks like that works, and doesn't quote the column name passed in:

def Area(term, *args):
    return Function(f"{term.get_sql()}.STArea", *args)

Outputs:

'SELECT "id","geometry" FROM "tablename" WHERE geometry.STArea()<10'