That was a good bit more work to get ready than I expected. It's broken into two scripts, one to create the schema, the other to load data and containing a couple check queries to ensure things are working properly (checking the materialized tables against a regular view for accuracy).
The first test case is to give us a definitive result on what "agreed pricing" is in effect at a point in time based on a product hierarchy our customers setup, and allow pricing to be set on nodes in that hierarchy, as well as specific products (with an order of precedence).
The second test case maintains some aggregated amounts / counts / boolean logic at an "invoice" level for all the detail lines which make up that invoice.
Both of these are real-world use cases which were simplified a bit to make them easier to understand. We have other use cases as well, but with how much time this took to prepare i'll keep it at this for now.
If you need anything clarified or have any issues, just let me know.
Hi Adam,
On Thu, 22 Oct 2020 10:07:29 -0400
Adam Brusselback <adambrusselback@gmail.com> wrote:
> Hey there Yugo,
> I've asked a coworker to prepare a self contained example that encapsulates
> our multiple use cases.
Thank you very much!
> The immediate/eager approach is exactly what we need, as within the same
> transaction we have statements that can cause one of those "materialized
> tables" to be updated, and then sometimes have the need to query that
> "materialized table" in a subsequent statement and need to see the changes
> reflected.
The proposed patch provides the exact this feature and I think this will meet
your needs.
> As soon as my coworker gets that example built up I'll send a followup with
> it attached.
Great! We are looking forward to it.
Regards,
Yugo Nagata
--
Yugo NAGATA <nagata@sraoss.co.jp>