Sample Header Ad - 728x90

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