I am implementing a cache system that stores in Redis results from SQL queries. What I do is
- I collect all the parameters of the query and create a hash with them (the hash includes all parameters that can affect the result of the query)
- I check whether in Redis there is already a key for that hash
- If Redis doesn't have that key, I call PostgreSQL, get the results, and store them in Redis under the hash
The steps above are quite regular, nothing special; the problem is that there are some cases in which the parameters of the query are stored in a table in the same database, and they can be quite a lot (in the range of tens of thousands).
In these cases, the process looks like this
- Call PostgreSQL preliminarly, to fetch all the parameters upon which the query depends
- Get the response and build a hash from it
- Go on with the steps above, call Redis, call PostgreSQL if needed, etc
The question is: is there a way to use an aggregate function in PostgreSQL to return an already-built hash? This way, I don't have to fetch tens of thousands of records and build a hash with them once they are outside PostgreSQL: the hash would come up from postgreSQL already build and ready to use.
In my mind, this would be a sort of aggregation function, like
SELECT MD5_HASH(attr1, attr2) FROM table_with_parameters WHERE etc...
this would be equivalent to calling
SELECT attr1, attr2 FROM table_with_parameters WHERE etc...
then storing all the results outside PostgreSQL, pass them into the MD5 algorithm and build a hash