For loop postgres over multiple tables

Question

Hi I have two tables in postgres that share similar structure as shown below

TABLE A

╔═══════════════════════════════════════════════════╗
║ id | timestamp                |  type | category  ║
╠═══════════════════════════════════════════════════╣
║ 1  | 2014-02-11 01:10:34.1234 | A  | 1            ║
║ 2  | 2014-02-11 01:10:36.7533 | A  | 2            ║
║ 3  | 2014-02-11 01:10:39.914  | E  | 1            ║
║ 4  | 2014-02-11 01:10:42.6712 | E  | 3            ║
║ 5  | 2014-02-11 01:10:34.9123 | E  | 1            ║
║ 6  | 2014-02-11 01:10:36.0313 | C  | 2            ║
║ 7  | 2014-02-11 01:10:39.1234 | X  | 1            ║
║ 7  | 2014-02-11 01:10:40.6743 | X  | 1            ║
║ 8  | 2014-02-11 01:10:42.9092 | B  | 3            ║
║ 9  | 2014-02-11 01:10:43.8234 | T  | 1            ║
║ 10 | 2014-02-11 01:10:45.1566 | T  | 1            ║
║ 11 | 2014-02-11 01:10:58.7344 | T  | 1            ║
║ 12 | 2014-02-11 01:10:59.9232 | T  | 1            ║
║ 13 | 2014-02-11 01:10:59.9232 | T  | 3            ║
║ 14 | 2014-02-11 01:10:59.9232 | T  | 2            ║
║ 15 | 2014-02-11 01:10:59.9232 | T  | 2            ║
╚═══════════════════════════════════════════════════╝

TABLE B

╔═══════════════════════════════════════════════════╗
║ id | timestamp                |  type | category  ║
╠═══════════════════════════════════════════════════╣
║ 1  | 2014-02-11 01:10:34.123  | A  | 1            ║
║ 2  | 2014-02-11 01:10:35.9092 | A  | 2            ║
║ 3  | 2014-02-11 01:10:36.1234 | E  | 1            ║
║ 4  | 2014-02-11 01:10:40.0100 | E  | 3            ║
║ 5  | 2014-02-11 01:10:51.1234 | E  | 2            ║
║ 7  | 2014-02-11 01:10:54.5347 | X  | 1            ║
║ 8  | 2014-02-11 01:11:02.7914 | B  | 3            ║
║ 9  | 2014-02-11 01:11:03.9000 | T  | 1            ║
║ 10 | 2014-02-11 01:11:05.7829 | T  | 1            ║
║ 11 | 2014-02-11 01:11:06.125  | T  | 1            ║
║ 12 | 2014-02-11 01:11:10.0000 | T  | 1            ║
║ 13 | 2014-02-11 01:10:59.9232 | T  | 3            ║
║ 14 | 2014-02-11 01:10:59.9232 | T  | 2            ║
╚═══════════════════════════════════════════════════╝

What i would like to compare in a stored procedure is the last 5 records for a given type if the last 5 records match on the category field than its a success if even one doesn't much then its all a failure.

It is tempting to do a join on the type and timestamp however the timestamps on the tables are never exact as the second tables data is coming from an external system. (NTP is not possible in this scenario)

e.g.

1. In the above table type A and B is a success because they have the exact same last 5 records that being the same category in the same order even though non of them had a total of 5.

2. E failed because they both had three records but the second table last category was a 2 instead of a 1.

3. C fails because it doesn't exist in table B

4. T fails because the last 5 don't match.

I was hoping to return the two data sets as independent arrays than running through a for loop and an index to compare the respective fields however haven't seen arays used in this manner in postgres please point me in the right direction.

All postgres examples seem to loop through the records of a single table.

example 1

example 2

postgres doc

Answer 1

Here is an SQL query that give you the result without a stored procedure :

select ta.type, case when ta.str_agg = tb.str_agg then 'success' else 'failed' end
from
(select type, array_to_string(array_agg(category order by timestamp desc), ',') as str_agg
from (select * from table_a as t where t.id in (select id from table_a WHERE type = t.type order by timestamp desc  LIMIT 5)) as t1
group by type) as ta
LEFT JOIN
(select type, array_to_string(array_agg(category order by timestamp desc), ',') as str_agg
from (select * from table_b as t where t.id in (select id from table_b WHERE type = t.type order by timestamp desc  LIMIT 5)) as t1
group by type) as tb
ON (ta.type = tb.type)

test here