Dynamic trigger function to delete oldest rows of a table

Question

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);

Answer 1

The way you wrote that, new is interpreted as part of the SQL statement, but the SQL engine has no idea about the variables in your PL/pgSQL code. You will have to use a parameter:

EXECUTE format('... where usr_id = 1ドル.usr_id') USING NEW;

• Dusty Pomerleau: Thank you for your time! Now that it works, it looks like there are some other flaws in my code, but this definitely answers the question.