Hi guys,
I'm writing a simple Plpgsql function to delete some data from different
tables.
The function starts with a select, and then 2 deletes after that.
How can I return the number of rows that each delete performed?
CREATE or REPLACE FUNCTION delete_ids_clientid(account_id integer)
RETURNS integer AS $$
declare
row record;
account_id integer;
BEGIN
FOR row IN EXECUTE '
SELECT
t1.id
FROM
public.table2 t2
JOIN
public.table1 t1 ON t2.id = t1.id
WHERE
t2.account_id = ' || account_id || ''
LOOP
DELETE FROM public.table1 WHERE id IN
(
SELECT
id
FROM
public.table1 t1
WHERE
t1.id = row.id
);
DELETE FROM public.table2 WHERE billable_id IN
(
SELECT
billable_id
FROM
public.table2 t1
WHERE
t1.id = row.id
);
END LOOP;
END
$$ language 'plpgsql';
Cheers
