Looping SQL query - PostgreSQL

I'm trying to get a query to loop through a set of pre-defined integers:

I've made the query very simple for this question.. This is pseudo code as well obviously!

my_id = 0
WHILE my_id < 10
  SELECT * from table where id = :my_id`
  my_id += 1
END

I know that for this query I could just do something like where id < 10.. But the actual query I'm performing is about 60 lines long, with quite a few window statements all referring to the variable in question.

It works, and gets me the results I want when I have the variable set to a single figure.. I just need to be able to re-run the query 10 times with different variables hopefully ending up with one single set of results.

So far I have this:

CREATE OR REPLACE FUNCTION stay_prices ( a_product_id int ) RETURNS TABLE (
    pid int,
    pp_price int
) AS $$
DECLARE
  nights int;
  nights_arr INT[] := ARRAY[1,2,3,4];
  j int;
BEGIN
  j := 1;
  FOREACH nights IN ARRAY nights_arr LOOP

    -- query here..

  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;

But I'm getting this back:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.

So do I need to get my query to SELECT ... INTO the returning table somehow? Or is there something else I can do?

EDIT: this is an example of the actual query I'm running:

\x auto
\set nights 7

WITH x AS (
    SELECT
        product_id, night,
        LAG(night, (:nights - 1)) OVER (
            PARTITION BY product_id
            ORDER BY night
        ) AS night_start,
        SUM(price_pp_gbp) OVER (
            PARTITION BY product_id
            ORDER BY night
            ROWS BETWEEN (:nights - 1) PRECEDING
            AND CURRENT ROW
        ) AS pp_price,
        MIN(spaces_available) OVER (
            PARTITION BY product_id
            ORDER BY night
            ROWS BETWEEN (:nights - 1) PRECEDING
            AND CURRENT ROW
        ) AS min_spaces_available,
        MIN(period_date_from) OVER (
            PARTITION BY product_id
            ORDER BY night
            ROWS BETWEEN (:nights - 1) PRECEDING
            AND CURRENT ROW
        ) AS min_period_date_from,
        MAX(period_date_to) OVER (
            PARTITION BY product_id
            ORDER BY night
            ROWS BETWEEN (:nights - 1) PRECEDING
            AND CURRENT ROW
        ) AS max_period_date_to

    FROM products_nightlypriceperiod pnpp
    WHERE
        spaces_available >= 1
        AND min_group_size <= 1
        AND night >= '2016-01-01'::date
        AND night <= '2017-01-01'::date
)
SELECT
    product_id as pid,
    CASE WHEN x.pp_price > 0 THEN x.pp_price::int ELSE null END as pp_price,
    night_start as from_date,
    night as to_date,
    (night-night_start)+1 as duration,
    min_spaces_available as spaces
FROM x
WHERE
    night_start = night - (:nights - 1)
    AND min_period_date_from = night_start
    AND max_period_date_to = night;

That will get me all the nights night periods available for all my products in 2016 along with the price for the period and the max number of spaces I could fill in that period.

I'd like to be able to run this query to get all the periods available between 2 and 30 days for all my products.

This is likely to produce a table with millions of rows. The plan is to re-create this table periodically to enable a very quick look up of what's available for a particular date. The products_nightlypriceperiod represents a night of availability of a product - e.g. Product X has 3 spaces left for Jan 1st 2016, and costs £100 for the night.