How to return generated columns in Rails ActiveRecord?

Question

We'd like to introduce a trigger to set a value on a column, in a Rails 6 application. The table looks like this (PostgreSQL 9.6):

CREATE TABLE foo(
    id bigserial primary key
  , sku text not null unique
  -- ...
);

On create, our code sets a prefix on SKU:

class Foo < ApplicationRecord
  before_create :set_sku_prefix

  private

  def set_sku_prefix
    self[:sku] = "AAA-"
  end
end

When the trigger fires, it finishes setting the record up:

CREATE OR REPLACE FUNCTION generate_sku() RETURNS trigger AS $$
BEGIN
  -- Simplified, real trigger handles ID already being set,
  -- and other edge cases related to the length of the returned value.
  NEW.id = nextval('public.foos_id_seq');
  NEW.sku = NEW.sku || RIGHT('000000', NEW.id, 6);

  RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER generate_sku
BEFORE INSERT OR UPDATE
ON foos
FOR EACH ROW
WHEN (RIGHT(NEW.sku, 1) = '-')
EXECUTE PROCEDURE generate_sku();

When ActiveRecord generates the SQL statement to do the insert, it does this:

INSERT INTO foos(sku) VALUES ('AAA-') RETURNING id

I'd like to change that last statement so that it also returns the SKU, and have ActiveRecord update the in-memory attribute value to what the database generated:

INSERT INTO foos(sku) VALUES ('AAA-') RETURNING id, sku

In the end, I'd like the following to be true:

foo = Foo.create!
if foo.sku == ("AAA-%06d" % [foo.id])
  puts "All good!"
else
  puts "Oops, wrong"
end

I have read most of the ActiveRecord API docs, and the Ruby on Rails Guides, to no avail. Searching on the general web also failed me. I searched for "rails activerecord trigger returning", with nothing that was really related to what I need.

Is it possible to make ActiveRecord read more of the written row and reload specific attributes?

The reason we're doing this is to avoid a double-write on INSERT. The existing code saves, then uses the record's ID to set the SKU, in Ruby-land, then writes the column back to the DB. This means 2 DB roundtrips for the same operation. The SKU column is also NULL-allowed, which I don't really like. We'd like to save a roundtrip to the DB, and enforce NOT NULL on the SKU column. If we have to reload the record, this will defeat the purpose of using a database trigger.

How to return generated columns in Rails ActiveRecord?

0 Answers