I have to correlate two sets of data. One is a list of payments and
the other is a list of invoices, both ordered by date. A given
payment may be allocated to multiple invoices. Also, a given
invoice may consume multiple payments. For a variety of reasons,
payments were never associated with an invoice when they were first
entered, and it's too late now (IOW, I can't readily change the
structure of these tables and even if I could, I'd still have to do
this to fill in the missing data).

I want to scan the two sets, fetching an item from each and deciding
how they match up. As each is consumed, I need to fetch another one
of that kind, producing in the end a sum of payments within a given
period.

This is basically a merge problem, but I don't see a way to do it in
PSQL because it looks to me like you have to read all of one set
before you can do anything with the other one.

Does anyone have any suggestions as to how this could be done using
PSQL? Or SQL for that matter, say with an SP or a UDF in the join
condition?

Michael D. Spence
Mockingbird Data Systems, Inc.