Gets all 7 previous days of the week and all 7 of the most recent days of the week along with the SQL that can be run to get each value.

get_recent_days.sql

CREATE OR REPLACE FUNCTION get_recent_days() RETURNS TABLE ("date_type" VARCHAR, "date_value" DATE, "date_sql" VARCHAR) STABLE AS $$
/*******************************************************************************
 * Author: Chris West
 * Date: 2018-07-24
 * Source: http://cwestblog.com/2018/07/24/plpgsql-example-function-that-returns-a-table/
 * Description:
 *   Gets all 7 previous days of the week and all 7 of the most recent days of
 *   the week along with the SQL that can be run to get each value.
 * Example Run:
 *   SELECT * FROM get_recent_days();
 *******************************************************************************/
BEGIN
  RETURN QUERY
    SELECT
      -- day
      (CASE day_type WHEN 1 THEN 'Previous ' ELSE 'Most Recent ' END || "day")::VARCHAR,
      -- value
      (CASE day_type
        WHEN 1 THEN
          CURRENT_DATE + COALESCE(NULLIF(CAST("offset" - extract(dow FROM CURRENT_DATE) AS INT) % 7, 0), -7)
        ELSE
          CURRENT_DATE + CAST("offset" - extract(dow FROM CURRENT_DATE) AS INT) % 7
      END)::DATE,
      -- sql
      (CASE day_type
        WHEN 1 THEN
          'SELECT CURRENT_DATE + COALESCE(NULLIF(CAST(' || "offset" || ' - extract(dow FROM CURRENT_DATE) AS INT) % 7, 0), -7) AS previous_' || lower("day") || ';'
        ELSE
          'SELECT CURRENT_DATE + CAST(' || "offset" || ' - extract(dow FROM CURRENT_DATE) AS INT) % 7 AS most_recent_' || lower("day") || ';'
      END)::VARCHAR
    FROM (
      SELECT
        generate_series(0, 1) AS day_type,
        generate_series(-6, 0) AS "offset",
        unnest(string_to_array('Mon,Tues,Wednes,Thurs,Fri,Satur,Sun', ',')) || 'day' AS "day"
    ) t
    ORDER BY "offset", day_type DESC;
END
$$ LANGUAGE plpgsql