PostgreSQL aggregation function to calculate an MD5 hash of the response

60 Views Asked by At

I am implementing a cache system that stores in Redis results from SQL queries. What I do is

  1. 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)
  2. I check whether in Redis there is already a key for that hash
  3. 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

  1. Call PostgreSQL preliminarly, to fetch all the parameters upon which the query depends
  2. Get the response and build a hash from it
  3. 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

0

There are 0 best solutions below