in plpgsql, how would i create table rows from a loop-CodePudding

I'm trying to get something like this:

| month_yr|
 --------- 
| 11_2021 |
| 12_2021 |
| 01_2022 |
| 02_2022 |
| 03_2022 |

where the number of rows depends on the argument i enter into a function like this:

CREATE OR REPLACE FUNCTION
    months_to_avg(qty_months INT)
        RETURNS TABLE(month_yrs TEXT)
        AS $$
            DECLARE
                qty_months INT := 3;
                current_month INT := ( SELECT EXTRACT(MONTH FROM DATE(NOW())) );
                month_col INT;
                current_year INT := ( SELECT EXTRACT(YEAR FROM DATE(NOW())) );
            BEGIN
                FOR month_col IN 1..qty_months LOOP
                    IF current_month < (month_col 1) THEN
                        RETURN CONCAT( ((12 current_month) - month_col), (current_year - 1) ) as month_yr;
                    ELSE
                        RETURN CONCAT( (current_month - month_col), current_year ) as month_yr;
                    END IF;
                END LOOP;
            END
        $$ LANGUAGE plpgsql

but i'm having a hard time understanding how to return those rows using a loop.

thanks!

CodePudding user response：

CREATE OR REPLACE FUNCTION months_to_avg ()
    RETURNS TABLE (
        month_yrs text
    )
    AS $$
DECLARE
    qty_months int := 7;
    current_month int := (
        SELECT
            EXTRACT(MONTH FROM DATE(NOW())));
    month_col int;
    current_year int := (
        SELECT
            EXTRACT(YEAR FROM DATE(NOW())));
BEGIN
    FOR month_col IN 1..qty_months LOOP
        IF month_col > current_month THEN
            RAISE NOTICE 'loop times %', month_col;
            RAISE NOTICE 'current_month - month_col: %, current_year: %', (current_month - month_col   13), current_year - 1;
            RETURN query
            SELECT
                current_month - month_col   13 || '_' || current_year - 1;
        ELSE
            RAISE NOTICE 'loop times %', month_col;
            RAISE NOTICE 'current_month - month_col: %, current_year: %', (current_month - month_col), current_year;
            RETURN query
            SELECT
                current_month - month_col   1 || '_' || current_year;
        END IF;
    END LOOP;
END
$$
LANGUAGE plpgsql;

use loop, to make it right, use raise notice/raise info will make debug more easier.