Fork me on GitHub
#sql
<
2019-06-21
>
caleb.macdonaldblack03:06:32

I'm wondering if it's possible to do a stateful select or update in postgres. Similar to a stateful transducer, I want to map over each row keeping some state to derive a new value for each row. For context I'm simulating how much energy would be consumed if a battery and solar exists. So using data where there is no battery, at each point in time (for example 30 mins) I calculate energy consumption by adding excess/unused solar to the current battery state (Charging battery). Then when there isn't sufficient solar I pull the excess from the battery while it has charge. Then finally if there isn't sufficient solar or battery charge I pull energy from the grid. I need to calculate the energy flow in and out of battery, solar and grid for each point in time. To do this I believe I need to keep some state of the battery's charge level as I iterate through. I'm already calculating this in clojure fine but I have to read and write the entire timeseries data set. So if I can do this in sql I believe It might be faster.

gklijs04:06:34

Not an answer to your question directly. But what you want to do might be easier/faster using crux, https://github.com/juxt/crux.

caleb.macdonaldblack04:06:34

Interesting thanks!

noisesmith17:06:53

there are individual operations like sum