Calling PL/pgSQL Stored Procedures from Ruby on Rails

Question

I am working on a project where I am the database designer/admin in a PostgreSQL environment. The leader has decided to use Rails for the application logic and recruited a Rails programmer.

The Rails programmer said that he usually programs all application code and doesn't like the lack of control presented by having someone pass him a stored procedure, and that he has never done it in rails.

The database utilizes a lot of inheritence/EERM, so stored procedures and triggers will make his job a lot easier, in addition to the performance benefits from using procs.

I have four questions:

1) How to call a pl/pgSQL stored procedure from Rails with no return value

2) How to call a pl/pgSQL stored procedure from Rails with a single return value (1 row/1 column)

3) How to call a pl/pgSQL stored procedure from Rails with a 1 row many column return value.

4) How to call a pl/pgSQL stored procedure from Rails using an OUT parameter ?

Thanks!

Answer 1

Hello I'm using this code to work with PgSQL stored procedures This covers all except your last question

   class SpActiveRecord < ActiveRecord::Base

      self.abstract_class = true
      #without return value
      def self.execute_sp(sql, *bindings)
        perform_sp(:execute, sql, *bindings)
      end
      #select many return values
      def self.fetch_sp(sql, *bindings)
        perform_sp(:select_all, sql, *bindings)
      end
      #select single return value
      def self.fetch_sp_val(sql, *bindings)
        perform_sp(:select_value, sql, *bindings)
      end

      protected
      def self.perform_sp(method, sql, *bindings)
        if bindings.any?
          sql = self.send(:sanitize_sql_array, bindings.unshift(sql))
        end
        self.connection.send(method, sql)
      end

    end

Example

class Invoice < SpActiveRecord

 def create_or_update
       raise ReadOnlyRecord if readonly? or !new_record?

        self.id = fetch_sp_val("SELECT * FROM billing.invoice_generate(?,?,?)", 
                               self.account_id,
                               self.start_date,
                               self.end_date)

       @new_record = false
       true
   end
end