[Solved] I have a table with a status however I want it to change status every 10 minutes. I’m using Postgresql

by


I can see what you’re after although there is a big omission: What happens when the status has reached ‘Z’ and it is time to update? More on that later.

Your request has 2 components, actually getting a process to run and a rolling update procedure (script) for the status. Well, Postgres has no native functionally for initiating a script. You’ll have to setup a cron job, or an entry is what ever job scheduler you have. The update process is not all that difficult, except for the undefined ‘Z’ status problem. (So when that happens I’ll just repeat the A-Z extending the code length (sort of like excel names columns).

The basic update needed is to simply add 1 to the current value. But of course the statement ” ‘A’+1 ” doesn’t work, but the result can be obtained with the CHR and ASCII functions. Chr(ascii(‘A’)+1)) effectively accomplishes that so your update could be accomplished as:

Update table_name set status = chr(ascii(status)+1);

However, that fails once the status reaches ‘Z’. Well it doesn’t fail in the since of generating an error, but it produces ‘[‘. The following script produces ‘AA’ in the above case, and each time status reaches ‘…Z’ the next status becomes ‘…AA’.

--- setup 
drop table if exists current_stat;
create table current_stat(id serial,status text,constraint status_alpha_ck check( status ~ '^[A-Z]+'));
insert into current_stat(status) values (null), ('A'), ('B'), ('Y'), ('Z'), ('AA'), ('ABZ')

--- Update SQL
with curr_stat as
   ( select id, status
          , chr(ascii(substring(status,char_length(status),1))+1) nstat
          , char_length(status) lstat from current_stat)  
update current_stat cs
  set status = ( select case when status is null or lstat < 1     then 'A'
                             when substring(status,lstat,1) = 'Z' then overlay( status placing 'AA' from lstat for 2)
                             else overlay( status placing nstat from lstat for 1)
                        end
                   from curr_stat
               where cs.id = curr_stat.id
             );

select * from current_stat;

solved I have a table with a status however I want it to change status every 10 minutes. I’m using Postgresql