PostgreSQL : Write a function to iterate over the rows of a table and Update the Data

Assume that you have a table where Subscriptions are stored, example given you have bought a new Spotify subscription, for 3, 6 or 12 months.

In this table , the data that are kept are :

the creation date (created_at)
last update timestamp (last_updated)
start_date of Subscription
start_date of Subscription
end_date of Subscription
duration_in_months (3,6 or 12)
customer_id , the customer that bought the subscription

/** Create the table*/
create table subscriptions
(
    id     serial PRIMARY KEY,
    created_at       timestamp,
    last_updated     timestamp,
    start_date       timestamp,
    end_date         timestamp,
    duration_in_months integer,
    customer_id     varchar(255)

);

Let’s feed the table with data

INSERT INTO subscriptions ( created_at, last_updated, start_date, end_date,duration_in_months, customer_id)
VALUES ( NOW(), NOW(), '2022-05-19' ,null,  3, 'customer-1');

INSERT INTO subscriptions ( created_at, last_updated, start_date, end_date, duration_in_months, customer_id)
VALUES ( NOW(), NOW(), '2022-05-19' ,null,  6, 'customer-2');

Problem to Solve:

We want to create a function to iterate over the rows of this table , check the subscription duration and update the end_date column

create or replace function update_dates() returns void
    language plpgsql
AS
$$
DECLARE
    t_curs cursor for
        SELECT *
        FROM subscriptions
        WHERE start_date IS NOT NULL
          AND duration_in_months IS NOT NULL;
    t_row     subscriptions%rowtype;
    _end_date timestamp;

BEGIN

    FOR t_row in t_curs
        LOOP

            RAISE NOTICE 'id: %', t_row.id;
            RAISE NOTICE 'duration_in_months: %', t_row.duration_in_months;
            RAISE NOTICE 'start_date %' , t_row.start_date;

            CASE t_row.duration_in_months
                WHEN 3 THEN _end_date = t_row.start_date + interval '3 months';
                WHEN 6 THEN _end_date = t_row.start_date + interval '3 months';
                WHEN 12 THEN _end_date = t_row.start_date + interval '6 months';
                ELSE _end_date = NULL;
                END CASE;

            RAISE NOTICE '_new_end_date %', _end_date;

            IF _end_date IS NOT NULL THEN
                update subscriptions
                set end_date     = _end_date,
                    last_updated = NOW()
                where current of t_curs;

                RAISE NOTICE 'update subscription with ID  % ', t_row.id;
            END IF;

        END LOOP;
END
$$;

Let’s start explain:

DECLARE
    t_curs cursor for
        SELECT *
        FROM subscriptions
        WHERE start_date IS NOT NULL
          AND duration_in_months IS NOT NULL;
    t_row     subscriptions%rowtype;

Creates a cursor over table subscriptions , for the rows that have start_date and duration_in_months columns not null

 FOR t_row in t_curs
        LOOP
.....
END LOOP;

Creates a loop and iterate over the selected rows of the table

CASE t_row.duration_in_months
                WHEN 3 THEN _end_date = t_row.start_date + interval '3 months';
                WHEN 6 THEN _end_date = t_row.start_date + interval '3 months';
                WHEN 12 THEN _end_date = t_row.start_date + interval '6 months';
                ELSE _end_date = NULL;
                END CASE;

Applies a case selection, based of the value of duration_in_months column for each row, it computes and set value to _end_date variable

 update subscriptions
                set end_date     = _end_date,
                    last_updated = NOW()
                where current of t_curs;

Update the row , with the commuted values

RAISE NOTICE 'update subscription with ID  % ', t_row.id;

RAISE NOTICE is used to print on console

Magic time… execute the function

select update_dates();

The console logs:

your_database_name> select update_dates()
[2022-05-17 23:01:14] [00000] id: 1
[2022-05-17 23:01:14] [00000] duration_in_months: 3
[2022-05-17 23:01:14] [00000] start_date 2022-05-19 00:00:00
[2022-05-17 23:01:14] [00000] _new_end_date 2022-08-19 00:00:00
[2022-05-17 23:01:14] [00000] update subscription with ID 1
[2022-05-17 23:01:14] [00000] id: 2
[2022-05-17 23:01:14] [00000] duration_in_months: 6
[2022-05-17 23:01:14] [00000] start_date 2022-05-19 00:00:00
[2022-05-17 23:01:14] [00000] _new_end_date 2022-08-19 00:00:00
[2022-05-17 23:01:14] [00000] update subscription with ID 2
[2022-05-17 23:01:14] 1 row retrieved starting from 1 in 33 ms (execution: 7 ms, fetching: 26 ms)
ciab.public> select * from subscriptions
[2022-05-17 23:01:21] 2 rows retrieved starting from 1 in 45 ms (execution: 2 ms, fetching: 43 ms)

And then , verify:

select * from subscriptions;