ruby-on-rails - INSERT RETURNING 和分区 POSTGRESQL

Question

最近我在 portgres 上使用分区表。我有一些关于 INSERT RETURNING id 的问题。这是我的触发器函数和触发器:

CREATE OR REPLACE FUNCTION f_port_vessels_insert_trigger_auto()
  RETURNS TRIGGER AS $$
DECLARE
  insert_sql TEXT;
  week_num INTEGER;
  year_num INTEGER;
  partition_table VARCHAR;
  prefix_partition_table VARCHAR :='port_vessels_p_';
BEGIN
  SELECT CAST (EXTRACT(WEEK FROM NEW.created_at) AS INTEGER) INTO week_num;
  SELECT CAST (EXTRACT(YEAR FROM NEW.created_at) AS INTEGER) INTO year_num;
  partition_table :=  prefix_partition_table || year_num || '_' || week_num;
  insert_sql := 'INSERT INTO ' || partition_table || ' VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)';
  EXECUTE insert_sql
  USING NEW.id, NEW.port_id, NEW.vessel_id,
    NEW.last_position_updated_at, NEW.nav_status_code,
    NEW.created_at, NEW.updated_at, NEW.berth_id,
    NEW.berth_shift_id;
  RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER f_port_vessels_insert_trigger
BEFORE INSERT ON port_vessels
FOR EACH ROW EXECUTE PROCEDURE f_port_vessels_insert_trigger_auto();

我用谷歌搜索了所有这些，并看到一个解决方案创建了一个 View ，创建了触发器insert instead on the view instead of table。我认为如果我们只在数据库上工作很好，但我在 Rails 中使用 Active Record，我不想更多地改变我的模型，所以有人遇到过这种情况吗？或者有其他方法解决这个问题。

Answer 1

与其重新发明轮子，您应该(在 PostgreSQL v10 或更高版本上)使用 declarative partitioning .