How to call plpgsql functions from Ruby on rails?

Question

How to call plpgsql functions from Ruby on Rails application?

This is the function definition:

CREATE OR REPLACE FUNCTION add_org_pincode(i_user_id integer, i_org_id integer, i_amount numeric, i_pincode_id integer)
  RETURNS integer AS
$BODY$
Declare
  v_org_balance    numeric;
Begin
  Select o.balance
    Into Strict v_org_balance
    From organizations o
      Left Join organizations d On o.parent_organization_id = d.id
    Where o.id = i_org_id
    For Update Of o;
  --
  Insert Into org_balance_transactions(organization_id, balance, amount_added, reason, action_user_id, transaction_id)
    Values (i_org_id, v_org_balance, i_amount, 10, i_user_id, i_pincode_id);
  --
  Update organizations
    Set balance = balance + i_amount
    Where id = i_org_id;
  -- Other error
  Return 0;
End;$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

So, how do I call add_org_pincode?

See here for some more discussion: stackoverflow.com/questions/10343988/… — Steve Rowley
– Steve Rowley, Commented May 8, 2012 at 13:44

Steve Rowley · Accepted Answer · 2012-05-08 15:11:34Z

2

Like the others have said, something akin to:

result = connection.execute ("SELECT add_org_pincode(#{user_id}, #{org_id}, #{amount}, #{pincode_id};")

answered May 8, 2012 at 15:11

Steve Rowley

1,5781 gold badge11 silver badges18 bronze badges

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

1 Comment

Nate Symer Over a year ago

Eww interpolating into an SQL query... not safe at all.

Jose Yovany Luis Garcia · Accepted Answer · 2013-03-15 18:39:14Z

This is an example that I have proven, it might help you...

CREATE LANGUAGE plpgsql;

/**PROMEDIOS**/
CREATE OR REPLACE FUNCTION average(valores NUMERIC[],OUT prom NUMERIC) AS $$
DECLARE
    element_count INT4;
    sum numeric := 0.0;
BEGIN

    element_count := array_upper(valores, 1) - array_lower(valores, 1) +1;
    FOR i IN array_lower(valores, 1) .. array_upper(valores, 1)
    LOOP
        sum := sum + valores[i];
    END LOOP;
    prom := trunc(sum/element_count,1);
END;
$$ LANGUAGE plpgsql;

Obviously you have to execute this SQL in your database, just add it in your SQL Editor (pointing your database) in PGAdmin, after executing that, this function must be available like this select average(ARRAY[4.5,3.2,7.0]);.

In Rails Console I tried the example like this:

_calificaciones = Calificacion.all(:conditions => ["promedio_parciales != average(array[parcial1,parcial2,parcial3])"])

and it worked fine for me... Good luck...

cfeduke · Accepted Answer · 2012-05-08 13:42:25Z

1

You can go straight to the ActiveRecord::Base.connection to execute SQL which is what I did for some prototype code...

result = ActiveRecord::Base.connection.execute("SELECT * FROM stock_hourly_performance(#{stock_id})")

I don't know if its the best solution to the problem but it got me up and running.

answered May 8, 2012 at 13:42

cfeduke

23.2k10 gold badges64 silver badges65 bronze badges

1 Comment

Lee Over a year ago

Beware of sql injection doing this. If the query cannot be parameterised then you need to escape all the input, validate its type and so in to be safe.

DanS · Accepted Answer · 2012-05-08 13:42:44Z

0

You can execute raw SQL with:

ActiveRecord::Base.connection.execute('SQL goes here')

Be sure that it is absolutely necessary to bypass ActiveRecord (Rail's ORM).

answered May 8, 2012 at 13:42

DanS

18.8k9 gold badges55 silver badges47 bronze badges

Collectives™ on Stack Overflow

How to call plpgsql functions from Ruby on rails?

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related