SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
create table chat ( id int not null primary key generated by default as identity, ads_id int, from_id int, to_id int, d timestamptz ); insert into chat (ads_id, from_id, to_id, d) values (1, 10, 100, current_timestamp), (1, 10, 100, current_timestamp + interval '1 sec'), (1, 100, 10, current_timestamp + interval '2 sec'), (1, 100, 10, current_timestamp + interval '3 sec'), (1, 10, 100, current_timestamp + interval '4 sec'), (1, 100, 10, current_timestamp + interval '5 sec'), (2, 10, 101, current_timestamp + interval '6 sec'), (2, 10, 102, current_timestamp + interval '7 sec'), (2, 102, 10, current_timestamp + interval '10 sec'), (2, 11, 100, current_timestamp + interval '1 sec'), (2, 100, 11, current_timestamp + interval '5 sec'), (2, 100, 11, current_timestamp + interval '7 sec'), (2, 12, 100, current_timestamp + interval '10 sec'), (2, 12, 100, current_timestamp + interval '11 sec') ; create function get_responses() returns table (user_id int, ads_id int, customer_id int, d timestamptz, response interval) stable language plpgsql as $$ #variable_conflict use_column declare first_message record; begin for first_message in select distinct on (ads_id, from_id) ads_id, from_id, to_id, id, d from chat order by ads_id, from_id, d loop perform from chat where from_id = first_message.to_id and to_id = first_message.from_id and id < first_message.id; continue when found; $1 := first_message.to_id; $2 := first_message.ads_id; $3 := first_message.from_id; $4 := first_message.d; select d - first_message.d into response from chat where ads_id = first_message.ads_id and from_id = first_message.to_id and to_id = first_message.from_id and id > first_message.id; return next; end loop; end $$; select * from get_responses();
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear