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

Despina Papatheodorou
3 min readMay 19, 2022

--

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;

--

--