Skip to content

Instantly share code, notes, and snippets.

@Envek
Last active June 23, 2022 13:43
Show Gist options
  • Save Envek/780b917e72a86c123776ee763b8dd986 to your computer and use it in GitHub Desktop.
Save Envek/780b917e72a86c123776ee763b8dd986 to your computer and use it in GitHub Desktop.
Example of “true money” PostgreSQL composite type including both amoiunt and currency and how to work with it in Ruby on Rails
# Example of custom PostgreSQL composite type support
# Works with Rails 6.0, also should work with Rails 5.2
# Place this file to config/initializers/
gem "money"
require "money"
# Subclass Money class just to get pretty output in rails console
class TrueMoney < Money
def inspect
"#{to_f} #{currency}"
end
end
# Define custom ActiveRecord type with logic for retrieving and saving values into database
# activerecord/lib/active_record/connection_adapters/postgresql/oid/interval.rb
module ActiveRecord
module ConnectionAdapters
module PostgreSQL
module OID # :nodoc:
class TrueMoney < Type::Value # :nodoc:
def type
:true_money
end
# Prepare value to be saved into database
def serialize(value)
return nil if value.nil? # ActiveRecord will handle NULL for us
amount_t = ::ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::OID::Decimal.new
currency_t = ::ActiveModel::Type::String.new
"(#{currency_t.serialize(value.currency.iso_code).inspect},#{amount_t.serialize(value.amount)})"
end
# Parse data either from user or database
def deserialize(value)
return nil if value.nil?
currency, amount = value.match(/\A\("?(\w+)"?,(\d+(?:\.\d+)?)\)\z/).captures
::TrueMoney.from_amount(BigDecimal(amount), currency)
end
# Parse data from user input
def cast(value)
return nil if value.nil?
case value
when ::Money then value
when String then deserialize(value)
else
raise NotImplementedError, "Don't know how to cast #{value.class} #{value.inspect} into Money"
end
end
# # Parse data either from database or user input.
# # Convenience method that replaces both `deserialize` and `cast`. Also handles nils for us.
# def cast_value(value)
# case value
# when ::Money then value
# when String
# currency, amount = value.match(/\A\("?(\w+)"?,(\d+(?:\.\d+)?)\)\z/).captures
# ::Money.from_amount(BigDecimal(amount), currency)
# else
# raise NotImplementedError, "Don't know how to cast #{value.class} #{value.inspect} into Money"
# end
# end
# Support for output default values to schema.rb
def type_cast_for_schema(value)
serialize(value).inspect
end
end
end
end
end
end
# Register our type in ActiveRecord
# activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
require 'active_record/connection_adapters/postgresql_adapter'
PostgreSQLAdapterWithTrueMoney = Module.new do
def initialize_type_map(m = type_map)
m.register_type "true_money" do |*_args, _sql_type|
::ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::OID::TrueMoney.new
end
m.alias_type "_true_money", "true_money"
# Call Rails logic after ours or it will complain that OID isn't supported
super
end
end
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.prepend(PostgreSQLAdapterWithTrueMoney)
ActiveRecord::Type.register(:true_money, ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::OID::TrueMoney, adapter: :postgresql)
# Add methods for migrations DSL
module ActiveRecord
module ConnectionAdapters
module PostgreSQL
module ColumnMethods
def true_money(name, options = {})
column(name, :true_money, options)
end
end
end
end
end
# activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb
require 'active_record/connection_adapters/postgresql/schema_statements'
module SchemaStatementsWithTrueMoney
def type_to_sql(type, limit: nil, precision: nil, scale: nil, array: nil, **)
case type.to_s
when 'true_money' then "true_money"
else super
end
end
end
ActiveRecord::ConnectionAdapters::PostgreSQL::SchemaStatements.prepend(SchemaStatementsWithTrueMoney)
CREATE TYPE _true_money AS (
currency varchar,
amount numeric
);
CREATE DOMAIN true_money AS _true_money CHECK (
value IS NULL AND value IS DISTINCT FROM (null, null)::_true_money -- allow NULL but disallow (NULL,NULL)
OR
(value).currency IS NOT NULL AND (value).amount IS NOT NULL -- all fields should be present
);
CREATE FUNCTION true_money_add(a true_money, b true_money) RETURNS true_money AS $$
BEGIN
IF (a).currency != (b).currency THEN
RAISE EXCEPTION '% can not be added to % - currencies does not match', b, a;
END IF;
RETURN ((a).currency, (a).amount + (b).amount);
END;
$$ IMMUTABLE RETURNS NULL ON NULL INPUT LANGUAGE plpgsql;
CREATE OPERATOR +(leftarg=true_money, rightarg=true_money, procedure=true_money_add);
CREATE FUNCTION true_money_sum(state true_money, value true_money) RETURNS true_money AS $$
BEGIN
IF value IS NULL AND state IS NULL THEN
RETURN NULL;
END IF;
IF state IS NULL THEN
RETURN value;
END IF;
RETURN state + value;
END;
$$ IMMUTABLE LANGUAGE plpgsql;
CREATE AGGREGATE sum (true_money) (sfunc = true_money_sum, stype = true_money);
CREATE FUNCTION true_money_multiply(a true_money, b numeric) RETURNS true_money AS $$
BEGIN
RETURN ((a).currency, (a).amount * b);
END;
$$ IMMUTABLE RETURNS NULL ON NULL INPUT LANGUAGE plpgsql;
CREATE FUNCTION true_money_multiply(a numeric, b true_money) RETURNS true_money AS $$
BEGIN
RETURN ((b).currency, (b).amount * a);
END;
$$ IMMUTABLE RETURNS NULL ON NULL INPUT LANGUAGE plpgsql;
CREATE OPERATOR *(leftarg=true_money, rightarg=numeric, procedure=true_money_multiply, commutator= *);
CREATE OPERATOR *(leftarg=numeric, rightarg=true_money, procedure=true_money_multiply, commutator= *);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment