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.
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[0]);
return null;
end;
$$;
create or replace trigger limit_recent_finding
after insert on recent_finding
for each row execute function drop_over_n(50);
