sql - stored procedure for postgres to get next immediate working date from holiday list

Question

I have written a stored procedure in postgres to update only about next working date ie., cnwd in column of table ts_courtslip_cnwd based on holiday table holiday_t using holidaydate. How could you make it more effective way to function out....

CREATE OR REPLACE FUNCTION cnwd_details()
  RETURNS void AS
$BODY$
DECLARE
     counter record;
     sdcounter record;    
     cnt int;
currentdate date;    
  nextdate date;  
  mnxtdate date;  
  flag boolean;
BEGIN
flag:=false;
currentdate:= (SELECT to_date(to_char(now()::date,'yyyy-mm-dd'),'yyyy-mm-dd'));

   FOR counter IN SELECT * FROM dblink('host=172.16.2.32 user=postgres password=postgres dbname=taphc port=5432','SELECT holidaydate FROM holiday_t where date_part(''year'', create_modify) = date_part(''year'', CURRENT_DATE) and display=''Y'' and holidaydate >= ''now()''::date ORDER BY holidaydate ASC ')AS subquery(holidaydate date)  LOOP --LIMIT 1
flag:=true;  

 if currentdate<=counter.holidaydate then
 RAISE NOTICE 'holiday_t table holidaydate column: %', currentdate<=counter.holidaydate;
     nextdate:= (SELECT to_date(to_char(counter.holidaydate::date + INTERVAL '1 DAY','yyyy-mm-dd'),'yyyy-mm-dd'));
    perform  dblink('host=172.16.2.32 user=postgres password=postgres dbname=periphery port=5432',format('update ts_courtslip_cnwd set cnwd ='''||nextdate||'''')) as subquery ;

    else 

    end if; 
    END LOOP;
    if flag=false then
     mnxtdate:= (SELECT to_date(to_char(now()::date + INTERVAL '1 DAY','yyyy-mm-dd'),'yyyy-mm-dd'));    
    perform  dblink('host=172.16.2.32 user=postgres password=postgres dbname=periphery port=5432',format('update ts_courtslip_cnwd set cnwd ='''||mnxtdate||'''')) as subquery ;
    else

    end if;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION cnwd_details()
  OWNER TO postgres;

question from:https://stackoverflow.com/questions/65517622/stored-procedure-for-postgres-to-get-next-immediate-working-date-from-holiday-li

Answer 1

I can address part of your question to simplify the overall code. You are doing way to much work processing dates. Look at just one of your date 'conversions':

mnxtdate:= (SELECT to_date(to_char(now()::date + INTERVAL '1 DAY','yyyy-mm-dd'),'yyyy-mm-dd'));   

First off a SELECT statement is not at all necessary, you can have direct assignment:

instead of 
SELECT to_date(to_char(now()::date + INTERVAL '1 DAY','yyyy-mm-dd'),'yyyy-mm-dd') into mnxtdate;
    
just do:
mnxtdate:= expression;  (more later)

Now a look at what the interpreter has to do resolving the statement.

1. call the function now() ==> get the internal representation of the current timestamp.
2. (...)::date ==> convert internal representation of timestamp from #1 to internal representation a date.
3. + interval '1 DAY' ==> convert the internal date from #2 to a internal timestamp and add 1 day.
4. to_char(..., 'yyyy-mm-dd') ==> convert the internal timestamp from #3 to string with with format 'yyyy-mm_dd'.
5. to_date(..., 'yyyy-mm-dd') ==> convert the string from #4 into an internal representation of a date.
6. Store result of #5 in variable local variable mnxtdate.

A much simpler way eliminates the select altogether, is easier to write, easier to understand and eliminates most a lot on internal processing:

mnxtdate = (now() + interval '1 day')::date;

1. call the function now() ==> get the internal representation of the current timestamp.
2. + interval '1 day' ==> add 1 day to result of #1
3. (...)::date ==> convert result of #2 in internal representation of date.
4. Store result of #3 in variable local variable mnxtdate.

Note: You could also cast the timestamp before converting to date then just add 1. See example here. You might want to spend some time reviewing Postgres Date Processing.

---

The following shows your reworking date assignments - nothing else.

create table or replace function cnwd_details()
  returns void as
$body$
declare
    counter record;
    sdcounter record;    
    cnt int;
    currentdate date;    
    nextdate date;  
    mnxtdate date;  
    flag boolean;
begin
    flag:=false;
    currentdate:= now()::date; 
        
    for counter in (dblink('host=172.16.2.32 user=postgres password=postgres dbname=taphc port=5432','select holidaydate from holiday_t where date_part(''year'', create_modify) = date_part(''year'', current_date) and display=''y'' and holidaydate >= ''now()''::date order by holidaydate asc ')as subquery(holidaydate date)  
    loop  
        flag:=true;  
        
        if currentdate<=counter.holidaydate then
           raise notice 'holiday_t table holidaydate column: %', currentdate<=counter.holidaydate;
           nextdate:= (counter.holidaydate+interval '1 day')::date;
           perform  dblink('host=172.16.2.32 user=postgres password=postgres dbname=periphery port=5432',format('update ts_courtslip_cnwd set cnwd ='''||nextdate||'''')) as subquery ;
        end if; 
        
    end loop;
            
    if flag=false then
        mnxtdate:= (now()+interval '1 day')::date ;    
        perform  dblink('host=172.16.2.32 user=postgres password=postgres dbname=periphery port=5432',format('update ts_courtslip_cnwd set cnwd ='''||mnxtdate||'''')) as subquery ;
    end if;
end;
$body$
  language plpgsql volatile
  cost 100;