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.

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.
E failed because they both had three records but the second table last category was a 2 instead of a 1.
C fails because it doesn't exist in table B
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

I was going to have a try at this one, but your tables are a nightmare to convert into INSERTs. Any chance you can provide less "pretty", more functional data? Best is sqlfiddle.com or something compatible with its "text to ddl" tool. (Edit your question and comment here when done). Also, PostgreSQL version? — Craig Ringer
– Craig Ringer, Commented Feb 11, 2014 at 0:30

Rida BENHAMMANE · Accepted Answer · 2014-02-17 12:11:33Z

2

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

edited Feb 17, 2014 at 12:11

answered Feb 11, 2014 at 0:36

Rida BENHAMMANE

4,1291 gold badge16 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sqwale Over a year ago

Thanks however C doesn't fail when it should be a failure. Just changed it from a JOIN to a left Join and now it works just fine. Thanks a bunch

sqwale Over a year ago

Thanks tried editing your answer but i needed the change to be more than 6 characters.

Collectives™ on Stack Overflow

For loop postgres over multiple tables

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related