I had, for a brief moment, a working SQL step in a script to handle a problem I was running into with searches, but I modified the script and now cannot get it back to functionality.
Either of these work fine in the sql console (I'm using a postgres database using the actualtech ESS adapter, and datagrip to manipulate the database directly):
select * from the_personnel_records where the_fullname ~* 'thesearchterm'
select * from "UserManagementTool" where the_fullname ~* thesearchterm
but pasting either of these into FMP's executeSQL step directly ends up not working because FMP butchers them and they don't work:
ERROR: syntax error at or near ""~*"" at character 56
STATEMENT: select * from "UserManagementTool" where "the_fullname" "~*" "thesearchterm"
ERROR: syntax error at or near ""~*"" at character 58
STATEMENT: select * from "the_personnel_records" where "the_fullname" "~*" "thesearchterm"
I cannot for the life of me figure out how I made this work before and why it won't work now, but I'm tearing my hair out at this point. Any help appreciated.
It might be noteworthy to mention that it was a one-line solution in the script step that I accidentally removed and it's possible I didn't do it with executeSQL, but if not, I don't know how the heck I did it.