Class: CreateProcedures

Inherits:
ActiveRecord::Migration
  • Object
show all
Defined in:
lib/generators/post_json/install/templates/create_procedures.rb

Overview

pgxn.org/dist/plv8/doc/plv8.html plv8-pgopen.herokuapp.com/ www.craigkerstiens.com/2013/06/25/javascript-functions-for-postgres/ www.postgresonline.com/journal/archives/272-Using-PLV8-to-build-JSON-selectors.html

Instance Method Summary collapse

Instance Method Details

#change ⇒ Object 



7
8
9
10
11
12
13
14
15
16
 
# File 'lib/generators/post_json/install/templates/create_procedures.rb', line 7

def change
  ActiveRecord::Base.connection.execute(json_numeric_procedure)
  ActiveRecord::Base.connection.execute(json_text_procedure)
  ActiveRecord::Base.connection.execute(js_filter_procedure)
  ActiveRecord::Base.connection.execute(json_selector_procedure)
  ActiveRecord::Base.connection.execute(json_selectors_procedure)
  ActiveRecord::Base.connection.execute(show_all_indexes_procedure)
  # ActiveRecord::Base.connection.execute(show_indexes_procedure)
  # ActiveRecord::Base.connection.execute(ensure_dynamic_index_procedure)
end

#js_filter_procedure ⇒ Object 



36
37
38
39
40
41
42
43
44
45
46
47
 
# File 'lib/generators/post_json/install/templates/create_procedures.rb', line 36

def js_filter_procedure
"create or replace function js_filter(js_function text, json_arguments text, data json) returns numeric as $$
if (data == null) {
  return null;
}
eval('var func = ' + js_function);
eval('var args = ' + (json_arguments == '' ? 'null' : json_arguments));
var final_args = [data].concat(args);
var result = func.apply(null, final_args);
return result == true || 0 < parseInt(result) ? 1 : 0;
$$ LANGUAGE plv8 IMMUTABLE STRICT;"
end

#json_numeric_procedure ⇒ Object 



18
19
20
21
22
23
24
25
 
# File 'lib/generators/post_json/install/templates/create_procedures.rb', line 18

def json_numeric_procedure
"CREATE OR REPLACE FUNCTION json_numeric(key text, data json) RETURNS numeric AS $$
if (data == null) {
  return null;
}
return data[key];
$$ LANGUAGE plv8 IMMUTABLE STRICT;"
end

#json_selector_procedure ⇒ Object 



49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
 
# File 'lib/generators/post_json/install/templates/create_procedures.rb', line 49

def json_selector_procedure
"CREATE OR REPLACE FUNCTION json_selector(selector text, data json) RETURNS text AS $$
if (data == null || selector == null || selector == '') {
  return null;
}
var names = selector.split('.');
var result = names.reduce(function(previousValue, currentValue, index, array) {
  if (previousValue == null) {
    return null;
  } else {
    return previousValue[currentValue];
  }
}, data);
return result;
$$ LANGUAGE plv8 IMMUTABLE STRICT;"    
end

#json_selectors_procedure ⇒ Object 



66
67
68
69
70
71
72
73
 
# File 'lib/generators/post_json/install/templates/create_procedures.rb', line 66

def json_selectors_procedure
"CREATE OR REPLACE FUNCTION json_selectors(selectors text, data json) RETURNS json AS $$
var json_selector = plv8.find_function('json_selector');
var selectorArray = selectors.replace(/\s+/g, '').split(',');
var result = selectorArray.map(function(selector) { return json_selector(selector, data); });
return result;
$$ LANGUAGE plv8 IMMUTABLE STRICT;"
end

#json_text_procedure ⇒ Object 



27
28
29
30
31
32
33
34
 
# File 'lib/generators/post_json/install/templates/create_procedures.rb', line 27

def json_text_procedure
"CREATE OR REPLACE FUNCTION json_text(key text, data json) RETURNS text AS $$
if (data == null) {
  return null;
}
return data[key];
$$ LANGUAGE plv8 IMMUTABLE STRICT;"    
end

#show_all_indexes_procedure ⇒ Object 



75
76
77
78
79
80
 
# File 'lib/generators/post_json/install/templates/create_procedures.rb', line 75

def show_all_indexes_procedure
  "CREATE OR REPLACE FUNCTION show_all_indexes() RETURNS json AS $$
    var sql = \"SELECT c3.relname AS table, c2.relname AS index FROM pg_class c2 LEFT JOIN pg_index i ON c2.oid = i.indexrelid LEFT JOIN pg_class c1 ON c1.oid = i.indrelid RIGHT OUTER JOIN pg_class c3 ON c3.oid = c1.oid LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c3.relnamespace WHERE c3.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND pg_catalog.pg_table_is_visible(c3.oid) ORDER BY c3.relpages DESC;\"
    return plv8.execute( sql );
  $$ LANGUAGE plv8 IMMUTABLE STRICT;"
end