How can I have a column autocalculate itself? Rails

Question

I have daily stock prices in a table and I've added a column which I'd like to auto calculate. I don't want it to be a manual calculation because the stock prices are something I import into the model with a finance api.

Therefore I just to use ActiveRecord-import and then have that column autopopulate.

The columns in question are Percent Change and Average past 5 days. I feel like this is something that I could set up perhaps on the DB side so that those columns auto populate. Almost like Excel is able to just write =a1/b1 and then the entire table populates.

Is this possible in either a rails model or postgresql? (using rails 3.2 and postgresql 9.2)

score 1 · Accepted Answer · edited May 23 '17 at 12:16

1

There is another option that is even closer to what you have in mind: a "computed field" or "generated column". Effectively a function taking the row type as parameter. I quote the manual here:

The equivalence between functional notation and attribute notation makes it possible to use functions on composite types to emulate "computed fields"

Might look like this for your example:

CREATE TABLE tbl (a_id int, a int, b int);
INSERT INTO tbl VALUES (1,3,7), (2,4,8), (3,9,2);

Function that emulates a computed field calc:

CREATE OR REPLACE FUNCTION calc(tbl)
  RETURNS numeric LANGUAGE sql IMMUTABLE  AS
$func$
    SELECT round($1.a::numeric/$1.b, 2)
$func$;

Call:

SELECT *, t.calc FROM tbl t;  -- note the table-qualified call!

Returns:

 a_id | a | b | calc
------+---+---+------
    1 | 3 | 7 | 0.43
    2 | 4 | 8 | 0.50
    3 | 9 | 2 | 4.50

Related answers with a lot more details and explanation:
Computed / calculated columns in PostgreSQL
How do you do date math that ignores the year?
Store common query as column?

edited May 23 '17 at 12:16

Community

1
1

answered Mar 28 '13 at 08:00

Erwin Brandstetter

605,456
145
1,078
1,228

I notice that you write a function with respect to a table. Is that function executed whenever I use `select t.calc`? Or has the function already executed and is now storing a value? I'm asking because of performance reasons. If it will run the function each time I use `select *,t.calc` it may be better for me to use a trigger and insert the value into the column the first time and save me the time it takes to process the calculation each time subsequently. Do you know if it stores the value or calculates on `select`? – Terence Chow Mar 29 '13 at 23:33
@ErwinBrandstter I also have another question...Your formula is able to calculate across each row, how would I go about calculating across multiple rows? For example, in your table, I would need something to show the increase/drop in column b. The 'calc' column would be 7/8-1 = -.125 or 8/2-1 = 3. Those are the values I need in the `calc` column, with the third column showing up as nil. – Terence Chow Mar 30 '13 at 01:50
@Chowza: I would solve that with the window function `lag()`, just like demonstrated today [in this related answer](http://stackoverflow.com/questions/15714342/iterating-through-postgresql-records-how-to-reference-data-from-next-row/15716499#15716499): paragraph "Proper solution", at the bottom. – Erwin Brandstetter Mar 30 '13 at 20:40

Akash · Answer 2 · 2013-03-28T03:57:48.783

I am aware of 2 options on the DB side, you can either try using a view, which contains the logic to get the dynamic fields, or you can use a trigger to auto populate them on any table changes

Update: *Triggers* are nothing but a function(s) that are fired automatically when you perform any updates (insert/update/delete) on a table, here's more info on it: http://www.postgresql.org/docs/9.1/static/sql-createtrigger.html

Views are pesudo name given a sql query, you can write your logic as

create view test as select 1+1 as dynamic_col

and then use it as

select * from test;

The output shall be 2 in the above case, but the view can be customized as per your requirement

here's more info in it: http://www.postgresql.org/docs/9.2/static/sql-createview.html

I'm still new to using databases. Is the trigger something that I set up in a rails migration? I have pgadmin 3 and I can look into the documentation to figure out how to write that statement, but will my friend on his computer be able to see my postgresql changes when i push the app to our development base? Where is the code for the db being recorded in rails? Or is this something I write in an ACtiveRecord::Base.connection — Terence Chow, Mar 28 '13 at 03:41

How can I have a column autocalculate itself? Rails

2 Answers2