The syntax for SUBSTRING in PostgreSQL is SUBSTRING(<text_expr> FROM <i> FOR <j>). Any idea how to make SQLAlchemy core generate that? I'm trying sqlalchemy.sql.expression.func, but that expects typically comma-separated notation. I don't see a built-in Function that addresses this. I'm not quite sure if literal or text would work. Any thoughts?
Add a comment
|
1 Answer
Looking through the SqlAlchemy tests, I found that sqlalchemy.sql.expression.func.substring compiles to SUBSTRING for PSQL:
def test_substring(self):
self.assert_compile(
func.substring("abc", 1, 2),
"SUBSTRING(%(substring_1)s FROM %(substring_2)s "
"FOR %(substring_3)s)",
)
self.assert_compile(
func.substring("abc", 1),
"SUBSTRING(%(substring_1)s FROM %(substring_2)s)",
)
func.substring(str, from, [for]) is indeed what you want. It is "comma-delineated" because that's how Python methods
If you want to generate the SQL yourself, you could do something like text("SUBSTRING('foo' FROM 1 FOR 2)"), but I don't see why you would.