Module: RailsOnPg::Functions

Defined in:
lib/rails_on_pg/functions.rb

Instance Method Summary collapse

Instance Method Details

#create_function(name, options = {}, *params) ⇒ Object

Create new plpgsql function Example:

create_function 'format_name', {:returns=>'character varying'}, 'first_name character varying(125)', 'middle_name character varying(15)', "last_name character varying(20)" do
  "RETURN COALESCE(last_name, 'no last name') || ', ' || COALESCE(first_name, 'no first name') || ' ' || COALESCE(middle_name || '.','');"
end



9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
# File 'lib/rails_on_pg/functions.rb', line 9

def create_function name, options={}, *params
  options.reverse_merge!({:as=>'$$'})
  returns = options[:returns]
  declare = %{DECLARE 
              #{options[:declare].join(';')}} if options[:declare]
  drop_function name, params
  # execute
  set_lang
  execute %{CREATE FUNCTION #{name}(#{params.join(',')}) RETURNS #{returns} AS #{options[:as]}
  #{declare}
  BEGIN
    #{yield}
  END;
  #{options[:as]} LANGUAGE 'plpgsql';
  }
end

#create_trigger(name, type, table_name, *actions) ⇒ Object

Create trigger function for it name - trigger name type - :before or :after table_name - table name actions - “insert”,“update”,“delete” Example:

create_trigger "some_tr", :before, "users", "insert","update"



38
39
40
41
42
43
44
45
 
# File 'lib/rails_on_pg/functions.rb', line 38

def create_trigger name, type, table_name, *actions
  create_function "#{name}_f", :returns=>'trigger',:as=>'$BODY$' do
    yield
  end
  execute %{CREATE TRIGGER #{name} #{type.to_s.upcase} #{actions.map{|str|str.upcase}.join(' OR ')}
  ON "#{table_name}" FOR EACH ROW
  EXECUTE PROCEDURE #{name}_f();}
end

#drop_function(name, *params) ⇒ Object

drop function



27
28
29
 
# File 'lib/rails_on_pg/functions.rb', line 27

def drop_function name, *params
  execute "DROP FUNCTION IF EXISTS #{name}(#{params.join(',')}) CASCADE"    
end

#drop_trigger(name, table_name) ⇒ Object

Drop trigger



48
49
50
 
# File 'lib/rails_on_pg/functions.rb', line 48

def drop_trigger name, table_name
  execute "DROP TRIGGER #{name} on #{table_name} CASCADE"    
end