PostgreSQL : Write a function to iterate over the rows of a table and update the data

An example of PostgreSQL function

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:

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;

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)
your_database_name> 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;