Rails ActiveRecord use SQL function as value for an attribute on create

Question

I am on rails 6,

I want to use an sql function as value for certain attribute upon creation of records.

That sql function is going to be dynamic, if it was static using it as default value would make sense.

What I can do is write a before_create callback, something like:

def populate_value_from_sql
  self.attr_name = self.connection.select_value('SELECT dynamic_function()')
end

But this will cause an extra sql query, i want to avoid that extra query and just compile inset's sql to use sql function instead of hard coded value. AND i want to stay with rails way so my validations and callbacks etc still work properly, i don't want to take complete raw sql route.

Thanks,

On postgres you can use triggers to do this on the database level. postgresql.org/docs/9.1/sql-createtrigger.html — max
– max, Commented Dec 3, 2019 at 13:24
I have to use dynamic names of the functions i want to call, for clarity, those will be sequence names. So i can't use triggers, those trigger names will be coming from outside of DB — Zia Ul Rehman
– Zia Ul Rehman, Commented Dec 3, 2019 at 14:16
Couldn't you pass the string value of the function to the trigger function whihc would allow you to interpolate the function inside the trigger function. And then in the actual trigger, you reference the trigger function? — Int'l Man Of Coding Mystery
– Int'l Man Of Coding Mystery, Commented Dec 5, 2019 at 8:39
I am not sure if i understood you correctly @MikeHeft , but i think what you are saying is to pass a value to trigger dynamically and than based off of that decide which function to call? If so, it would be great if you can demonstrate your solution as an answer, if it fits well with my use case, i don't have anything against triggers. Although i would prefer something without those. — Zia Ul Rehman
– Zia Ul Rehman, Commented Dec 5, 2019 at 8:58

Int'l Man Of Coding Mystery · Accepted Answer · 2019-12-06 10:36:30Z

0

I had never done it with a trigger yet, but I found this answer that I'm also going to try. But I don't see why it wouldn't work. I changed the answer to try and use the %1$s syntax for interpolating, so you don't have to list the same variable over and over.

CREATE OR REPLACE FUNCTION trigen(tbl text) RETURNS void AS $T1$
BEGIN
    EXECUTE format(
    'CREATE FUNCTION %1$s_function_name() RETURNS TRIGGER AS $T2$
    BEGIN
        UPDATE insertions SET n = n + 1 WHERE tablename = %1$s;
        RETURN NEW;
    END
    $T2$ LANGUAGE plpgsql', $1, quote_nullable($1));

    EXECUTE format('CREATE TRIGGER %1$s_inCnt BEFORE INSERT ON %s
    FOR EACH ROW EXECUTE PROCEDURE %1$s_insertCnt();', $1);
    END
$T1$ LANGUAGE plpgsql;

I don't know how you would pass the dynamic name through to the method, but a sample method in Rails could be:

def generate_trigger(table)
  ActiveRecord::Base.connection.execute("SELECT trigen(#{table});")
end

And to create the trigger function in your db you can create a migration

def change
  reversible do |dir|
    dir.up
      execute <<-SQL
        #trigen function snippet
      SQL
    end

    dir.down do
     execute <<-SQL
       DROP FUNCTION IF EXISTS trigen() CASCADE
      SQL
    end
  end
end

In order to drop the triggers and trigger functions themselves, you could then make another method in you Rails code,

def drop_trigger(trigger)
  ActiveRecord::Base.connection.base.execute("DROP TRIGGER IF EXISTS #{trigger} CASCADE;")
end

def drop_function(function)
  ActiveRecord::Base.connection.base.execute("DROP FUNCTION IF EXISTS #{function}() CASCADE;")
end

edited Dec 6, 2019 at 10:36

answered Dec 5, 2019 at 11:02

Int'l Man Of Coding Mystery

1,7122 gold badges16 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zia Ul Rehman Over a year ago

Thankyou for the trigger, an example WRT to rails and how would it work together with rails and its callback would be helpful.

Zia Ul Rehman Over a year ago

Thankyou for the updates. It seems like we will be triggering this DB function after the creation of the model, which means 2 DB calls, in which case it is no different than what i already have and doesn't solve my problem of 2 DB calls. Unless I am mistaken about your understanding your solution

Collectives™ on Stack Overflow

Rails ActiveRecord use SQL function as value for an attribute on create

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related