PostgreSQL INTERVAL period variable inside a loop-CodePudding

My goal is to calculate the latest seven days of the month within a loop in order to reference some foreign tables names but I'm getting syntax error on the bolded line (day_number_ts) and no idea how to handle it with a different approach.

FOR day_cnt IN 1..7 LOOP
    dayofmonth_ts := TIMESTAMP 'today'::TIMESTAMP;
    **day_number_ts  := dayofmonth_date - (TO_CHAR(day_cnt,99) || ' days')::INTERVAL; ** ----> Syntax error
    day_text := day_number::TEXT;
    IF day_number < 10 THEN 
        day_text := '0'||day_number::text;
    END IF;
    nameoftable := concat('log.pglog_',day_text);
    RAISE NOTICE 'foreign table name: %',nameoftable;
    EXECUTE format ('SELECT * FROM %s',nameoftable::text||' WHERE message LIKE ''AUDIT%''');
END LOOP ;

CodePudding user response：

Use generate_series:

SELECT
    dt
FROM
    generate_series(date_trunc('month', CURRENT_DATE), date_trunc('month', CURRENT_DATE::timestamptz)   '1 month'::interval - '1 day'::interval, '1 day') ast (dt)
ORDER BY
    dt DESC
LIMIT 7;


  dt            
-------------------------
 12/31/2022 00:00:00 PST
 12/30/2022 00:00:00 PST
 12/29/2022 00:00:00 PST
 12/28/2022 00:00:00 PST
 12/27/2022 00:00:00 PST
 12/26/2022 00:00:00 PST
 12/25/2022 00:00:00 PST