Is it possible to invoke an external function once for certain queries being fired from external client?
I have a table called test with 2 columns which are encrypted using AES_encrypt. But, the encryption key being used here is encrypted using an external service. So, the requirement is to invoke the external service to decrypt the key before calling the AES_Decrypt. Here, the query may select 100K rows and I dont want to call the external service 100K times and rather call once to decrypt the key and use the decrypted key to decrypt the columns from 100K rows.
We don’t have any way to do that completely transparently right now. External functions can’t maintain state between calls on the SingleStoreDB side. The only thing that comes to mind is to split it into two queries – one to call an external function to decrypt the key, and a second one that uses the decrypted key in the aes_ functions.
Also, the way our external functions work, they process data in batches, so it is not necessary to do a round-trip call across processes for every row. Maybe you could use the batching to reduce the overhead enough to suit you, so you could just use a regular function call in your SQL and not have to break things into two steps.