Ruby Grape POSTGRESQL stored procedure

Question

Trying to execute a POSTGRESQL stored procedure/function in Ruby Grape API Package. I have the function getactivesites() in my server which returns the site name.

server code:

BEGIN

    RETURN QUERY SELECT "SITE_ID",
            "SITE_NAME"
        FROM public.sites WHERE "ACTIVE_FLAG" = true
        ORDER BY "SITE_NAME" ASC ;
    END;

ruby code:

resource :getsites do

desc “Get Active Sites“
get do
  results =  ActiveRecord::Base.connection.execute("execute getactivesites")
  return results
end

The error I get when I run it is “PG::InvalidSqlStatementName: ERROR: prepared statement "getactivesites" does not exist : execute getactivesites”

Answer 1

Try select instead of execute:

ActiveRecord::Base.connection.execute("select getactivesites()")

Here is from my real proj:

Db.execute( 'select my_sp_name( ? )', my_sp_param )

where Db is sugar like:

# -*- frozen-string-literal: true -*-
# Syntax sugar module
module Db
  extend self

  delegate :transaction, to: ApplicationRecord

  def sanitize( *args )
    ApplicationRecord.send( :sanitize_sql, args )
  end

  def execute( *args )
    ApplicationRecord.connection.execute( sanitize( *args ) )
  end

end