In elasticsearch I often use painless script inside searches to calculate values dynamically ay query time, for example by manipulating a timestamp field or deriving custom numeric values. Now I am working on a project where part of the data is also stored in PostgreSQL, and I need similar behavior there just like stumble guys mod. Essentially, I want to apply calculations on fields within the query itself rather than precomputing and storing them.
In PostgreSQL I know it is possible to write expressions directly in SQL queries or even create stored functions in PL/pgSQL, but I am not sure if there is a recommended approach that is conceptually closest to how Elasticsearch uses Painless.
So the question is what is considered the nearest equivalent to using Painless scripts when working in PostgreSQL? Should I rely mostly on SQL expressions, custom functions, or procedural languages, or is there something else commonly used for this purpose?