Dynamic trigger function to delete oldest rows of a table
1
vote
1
answer
779
views
I am trying to generalize a trigger that deletes the oldest rows in a table as new rows are added. Since the number of rows to retain depends on the table, I am passing that number to the trigger function as an argument. When I use
format()
to interpolate the table name, it appears I am losing access to new
, as described in [Trigger function taking column names as parameters to modify the row](https://dba.stackexchange.com/questions/127787/trigger-function-taking-column-names-as-parameters-to-modify-the-row) .
Do I require hstore
to accomplish this, or is there a simpler approach?
Current code:
create or replace function drop_over_n()
returns trigger
language plpgsql as $$
begin
execute format('
with chrono_by_usr as (
select id, row_number()
over (partition by usr_id order by created_at) as row_number
from %I
where usr_id = new.usr_id and id != new.id
)
delete from %I
where id in (
select id
from chrono_by_usr
where row_number > %s
);
', tg_table_name, tg_table_name, tg_argv);
return null;
end;
$$;
create or replace trigger limit_recent_finding
after insert on recent_finding
for each row execute function drop_over_n(50);
Asked by Dusty Pomerleau
(13 rep)
Dec 13, 2021, 04:44 AM
Last activity: Dec 13, 2021, 09:26 AM
Last activity: Dec 13, 2021, 09:26 AM