.Pirátská strana » Technické oddělení » Údržba a provoz

create view octopus_users_view as select 
 * from dblink(
   'dbname=octopus', 
   'SELECT 
     id, 
     username_clean, 
     official_last_name, 
     official_first_name, 
     ''+420'' || REGEXP_REPLACE( REGEXP_REPLACE(REGEXP_REPLACE(phone, ''^(00420|[^0-9]*420)'', ''''), ''[^0-9]'', '''', ''g''),''^00'', '''') from people_person'
   )  
   AS t1(id integer, username text, firstname text, lastname text, phone text);

create view "failed_messages_view" as select 
 number,
 octopus_users_view.username, 
 octopus_users_view.firstname, 
 octopus_users_view.lastname, 
 count(*) from history 
 left join octopus_users_view on 
 (octopus_users_view.phone=number) 
 where status = 'failed' and ts>'2024-01-01' 
 group by (
   number,
   octopus_users_view.username,
   octopus_users_view.firstname, 
   octopus_users_view.lastname 
 ) 
 order by count(*) 
desc;

grant select on failed_messages_view to "nocodb-view";