CREATE TABLE cache (
id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
The idea is to cache values generated from a bunch of other tables for
easy access, since it takes quite a long time to select and merge all
the data to generate the values, and I access it far more frequently
than I update it. When the other tables are updated, the trigger inserts
a name/value pair into the cache table. I then have another trigger
which fires when I insert into the cache table which deletes any earlier
entries for the same name.
This seems a bit over-complicated, but I can't see any other way to do
it in the absence of an UPDATE ... INSERT ON DUPLICATE KEY statement
like MySQL. Can anyone think of better solution than this?