I have a Rails 7 model that uses Postgres' virtual column feature:
create_table :time_entries do |t|
# ...
t.virtual :duration, type: :interval, as: %(("to" - "from")::interval), stored: true, null: false
# ...
end
The problem is, that after I create a record via Rails create(...)
these virtual column is nil
:
[16] pry(main)> TimeEntry.create(from: Time.zone.now, to: 1.day.from_now)
TRANSACTION (0.3ms) BEGIN
TimeEntry Create (0.5ms) INSERT INTO "time_entries" ("from", "to", "created_at", "updated_at") VALUES ($1, $2, $3, $4, $5, $6, $7, $8) RETURNING "id"
[["from", "2022-11-18 06:45:11.419000"], ["to", "2022-11-19 06:45:11.420000"], ["created_at", "2022-11-18 06:45:11.420862"], ["updated_at", "2022-11-18 06:45:11.420862"]]
TRANSACTION (0.9ms) COMMIT
=> #<TimeEntry:0x0000ffff86f1ae10
id: 13,
from: Fri, 18 Nov 2022 06:45:11.419000000 UTC +00:00,
to: Sat, 19 Nov 2022 06:45:11.420000000 UTC +00:00,
duration: nil,
created_at: Fri, 18 Nov 2022 06:45:11.420862000 UTC +00:00,
updated_at: Fri, 18 Nov 2022 06:45:11.420862000 UTC +00:00>
When you reload the model, duration
is set.
I found out, that this is due to Rails only returning the id
column using RETURNING "id"
at the end of the INSERT INTO
statement. When you execute the query in Postgres directly you can return the generated duration
column directly after the insert:
app_development=# INSERT INTO "time_entries" ("from", "to", "created_at", "updated_at") VALUES ( '2022-11-18 06:34:46.889000', '2022-11-18 06:34:56.889000', '2022-11-18 06:34:46.889000', '2022-11-18 06:34:46.889000') RETURNING "duration", "id";
duration | id
----------+----
00:00:10 | 11
(1 row)
Is it possible to customize the RETURNING "id"
part of the SQL query in my model, so that the instance of TimeEntry
already have the duration
set after I create it?
EDIT:
I found the code segment inside the Postgres Adapter and tried to monkey patch it like this:
require "active_record/connection_adapters/postgresql/database_statements"
module PostgresReturningPatch
def sql_for_insert(...)
sql, *args = super
if sql.include?(TimeEntry.table_name) && sql.ends_with?(%(RETURNING "id"))
returning_virtual_columns = TimeEntry::columns.select(&:virtual?).map do |column|
quote_column_name(column.name)
end.join(", ")
sql += ", #{returning_virtual_columns}"
end
binding.pry
[sql, *args]
end
end
ActiveRecord::ConnectionAdapters::PostgreSQL::DatabaseStatements.module_eval do
prepend PostgresReturningPatch
end
Unfortunately, it's still nil
when Rails returns the instance of my model, despite that the SQL ends now with RETURNING "id", "date", "duration"
.