How to get financial year wise periods for a given date range

You want to create multiple records from one record (your date range). To accomplish this, you will need some kind of helper table.

In this example I created that helper table using GENERATE_SERIES and use it to join it to your date range, with some logic to get the dates you want.

dbfiddle

--Generate a range of fiscal years
WITH FISCAL_YEARS AS (
   SELECT 
      CONCAT(SEQUENCE.YEAR, '-07-01')::DATE AS FISCAL_START, 
      CONCAT(SEQUENCE.YEAR + 1, '-06-30')::DATE AS FISCAL_END
   FROM GENERATE_SERIES(2000, 2030) AS SEQUENCE (YEAR)
), 
--Your date range
DATE_RANGE AS (
   SELECT 
      '2021-06-16'::DATE AS RANGE_START, 
      '2022-08-31'::DATE AS RANGE_END
)
SELECT 
  --Case statement in case the range_start is later 
  --than the start of the fiscal year
  CASE 
    WHEN RANGE_START > FISCAL_START 
    THEN RANGE_START 
    ELSE FISCAL_START 
  END AS START_DATE, 
  --Case statement in case the range_end is earlier
  --than the end of the fiscal year
  CASE 
    WHEN RANGE_END < FISCAL_END 
    THEN RANGE_END 
    ELSE FISCAL_END
  END AS END_DATE
FROM FISCAL_YEARS
JOIN DATE_RANGE
  --Join to get all relevant fiscal years 
  ON FISCAL_YEARS.FISCAL_START BETWEEN DATE_RANGE.RANGE_START AND DATE_RANGE.RANGE_END
  OR FISCAL_YEARS.FISCAL_END BETWEEN DATE_RANGE.RANGE_START AND DATE_RANGE.RANGE_END

This should work for the current century.

with t(fys, fye) as
(
 select (y + interval '6 months')::date,
        (y + interval '1 year 6 months - 1 day')::date
 from generate_series ('2000-01-01'::date, '2100-01-01', interval '1 year') y
),
periods (period_start, period_end) as
(
 select
  case when fys < '16-Jun-2021'::date then '16-Jun-2021'::date else fys end,
  case when fye > '31-Aug-2022'::date then '31-Aug-2022'::date else fye end
 from t
)
select * from periods where period_start < period_end;

Looks well as a parameterized query too with '16-Jun-2021' and '31-Aug-2022' replaced by parameter placeholders.