Class ActiveRecord::ConnectionAdapters::PostgreSQLAdapter
In: lib/rails_pg_procs.rb
Parent: AbstractAdapter

TODO — Add Aggregates ability

Methods

Included Modules

SchemaProcs

Public Instance methods

Add a trigger to a table

[Source]

     # File lib/rails_pg_procs.rb, line 314
314:       def add_trigger(table, events, options={})
315:         events += [:row]    if options.delete(:row)
316:         events += [:before] if options.delete(:before)
317:         trigger = TriggerDefinition.new(0, table, options[:name], events, options[:function])
318:         execute trigger.to_sql_create
319: #        execute get_trigger_query(table, events, options)
320:       end

Create a stored procedure

[Source]

     # File lib/rails_pg_procs.rb, line 329
329:       def create_proc(name, columns=[], options={}, &block)
330:         if select_value("SELECT count(oid) FROM pg_language WHERE lanname = 'plpgsql' ","count").to_i == 0
331:           execute("CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler")
332:         end
333: 
334:         if options[:force]
335:           drop_proc(name, columns) rescue nil
336:         end
337: 
338:         if block_given?
339:           execute get_proc_query(name, columns, options) { yield }
340:         elsif options[:resource]
341:           execute get_proc_query(name, columns, options)
342:         else
343:           raise StatementInvalid.new("Missing function source")
344:         end
345:       end

[Source]

     # File lib/rails_pg_procs.rb, line 304
304:       def create_type(name, *columns)
305:         drop_type(name) if types.find {|typ| typ.name == name.to_s }
306:         execute get_type_query(name, *columns)
307:       end
 DROP FUNCTION name ( [ type [, ...] ] ) [ CASCADE | RESTRICT ]

default RESTRICT

[Source]

     # File lib/rails_pg_procs.rb, line 349
349:       def drop_proc(name, columns=[], cascade=false)
350:         execute "DROP FUNCTION #{name.to_sql_name}(#{columns.collect {|column| column}.join(", ")}) #{cascade_or_restrict(cascade)};"
351:       end

[Source]

     # File lib/rails_pg_procs.rb, line 309
309:       def drop_type(name, cascade=false)
310:         execute "DROP TYPE #{name} #{cascade_or_restrict(cascade)}"
311:       end

[Source]

     # File lib/rails_pg_procs.rb, line 259
259:       def procedures(lang=nil)
260:         query "SELECT P.oid, proname, pronamespace, proowner, lanname, proisagg, prosecdef, proisstrict, proretset, provolatile, pronargs, prorettype, proargtypes, proargnames, prosrc, probin, proacl\nFROM pg_proc P\nJOIN pg_language L ON (P.prolang = L.oid)\nWHERE P.oid > \#{self.class.first_proc_oid}\nAND (proisagg = 'f')\n\#{'AND (lanname ' + lang + ')'unless lang.nil?}\n"
261:       end

DROP TRIGGER name ON table [ CASCADE | RESTRICT ]

[Source]

     # File lib/rails_pg_procs.rb, line 323
323:       def remove_trigger(table, name, options={})
324:         options[:name] = name
325:         execute "DROP TRIGGER #{trigger_name(table, [], options).to_sql_name} ON #{table} #{cascade_or_restrict(options[:deep])};"
326:       end

[Source]

     # File lib/rails_pg_procs.rb, line 271
271:       def triggers(table_name)
272:         query("SELECT T.oid, C.relname, T.tgname, T.tgtype, P.proname\nFROM pg_trigger T\nJOIN pg_class   C ON (T.tgrelid = C.OID AND C.relname = '\#{table_name}' AND T.tgisconstraint = 'f')\nJOIN pg_proc    P ON (T.tgfoid = P.OID)\n").collect {|row| TriggerDefinition.new(*row) }
273:       end

[Source]

     # File lib/rails_pg_procs.rb, line 281
281:       def types
282:         result = query("SELECT T.oid, T.typname, A.attname, format_type(A.atttypid, A.atttypmod) AS type\nFROM pg_type      T\nJOIN pg_class     C ON (T.typrelid = C.oid)\nJOIN pg_attribute A ON (A.attrelid = C.oid AND C.relkind = 'c')\n")
283: 
284:         type_id = nil
285:         types = []
286:         result.each { |row|
287:           if type_id != row[0]
288:             types << TypeDefinition.new(row[0], row[1], [])
289:             type_id = row[0]
290:           end
291: 
292:           types.last.columns << [row[2], row[3]]
293:         }
294: 
295:         types
296:       end

Private Instance methods

Helper function that builds the sql query used to create a stored procedure. Mostly this is here so we can unit test the generated sql. Either an option[:resource] or block must be defined for this method. Otherwise an ActiveRecord::StatementInvalid exception is raised. Defaults are:

   RETURNS (no default -- which is cheap since that means you have to call this method w/ the options Hash) TODO: fix this
   LANGUAGE = plpgsql (The plugin will add this if you don't have it added already)
   behavior = VOLATILE (Don't specify IMMUTABLE or STABLE and this will be added for you)
   strict = CALLED ON NULL INPUT (Otherwise STRICT, According to the 8.0 manual STRICT and RETURNS NULL ON NULL INPUT (RNONI)
             behave the same so I didn't make a case for RNONI)
   user = INVOKER

[Source]

     # File lib/rails_pg_procs.rb, line 378
378:         def delim(name, options)
379:           options[:delim] || "$#{Inflector.underscore(name)}_body$"
380:         end
      From PostgreSQL
      CREATE [ OR REPLACE ] FUNCTION
          name ( [ [ argmode ] [ argname ] argtype [, ...] ] )
          [ RETURNS rettype ]
        { LANGUAGE langname
          | IMMUTABLE | STABLE | VOLATILE
          | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
         | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
          | AS 'definition'
         | AS 'obj_file', 'link_symbol'
       } ...
         [ WITH ( isStrict &| isCacheable ) ]

TODO Implement [ [ argmode ] [ argname ] argtype ]

[Source]

     # File lib/rails_pg_procs.rb, line 395
395:         def get_proc_query(name, columns=[], options={}, &block)
396:           returns = ''
397:           if options.has_key?(:return)
398:             returns = "RETURNS#{' SETOF' if options[:set]} #{options[:return] || 'VOID'}"
399:           end
400:           lang = options[:lang] || "plpgsql"
401: 
402:           if block_given?
403:             body = "#{delim(name, options)}\n#{yield}\n#{delim(name, options)}"
404:           elsif options[:resource]
405:             options[:resource] += [name] if options[:resource].size == 1
406:             body = options[:resource].collect {|res| "'#{res}'" }.join(", ")
407:           else
408:             raise StatementInvalid.new("Missing function source")
409:           end
410:           
411:           result = "
412:                   CREATE OR REPLACE FUNCTION #{name.to_sql_name}(#{columns.collect{|column| column}.join(", ")}) #{returns} AS
413:                         #{body}
414:                         LANGUAGE #{lang}
415:                         #{ behavior(options[:behavior] || 'v').upcase }
416:                         #{ strict_or_null(options[:strict]) }
417:                         EXTERNAL SECURITY #{ definer_or_invoker(options[:definer]) }
418:                   "
419:         end

CREATE TRIGGER name { BEFORE | AFTER } { event [ OR … ] } ON table [ FOR [ EACH ] { ROW | STATEMENT } ] EXECUTE PROCEDURE funcname ( arguments )

[Source]

     # File lib/rails_pg_procs.rb, line 361
361:         def get_trigger_query(table, events, options={})
362:           event_str = events.collect {|event| event.to_s.upcase }.join(" OR ")
363:           func_name = options[:function] || trigger_name(table, events, options)
364:           result = "CREATE TRIGGER #{trigger_name(table, events, options).to_sql_name} #{(options[:before] ? "BEFORE" : "AFTER")} #{event_str} ON #{table} FOR EACH #{(options[:row] ? "ROW" : "STATEMENT")} EXECUTE PROCEDURE #{func_name.to_sql_name}();"
365:         end

[Source]

     # File lib/rails_pg_procs.rb, line 421
421:         def get_type_query(name, *columns)
422:           raise StatementInvalid.new if columns.empty?
423:           "CREATE TYPE #{quote_column_name(name)} AS (
424:             #{columns.collect{|column,type|
425:                           if column.is_a?(Hash)
426:                                 column.collect { |column, type| "#{quote_column_name(column)} #{type}" }
427:                           else
428:                           "#{quote_column_name(column)} #{type}"
429:                           end
430:             }.join(",\n")}
431:           )"
432:         end

[Source]

     # File lib/rails_pg_procs.rb, line 354
354:         def trigger_name(table, events=[], options={})
355:           options[:name] || Inflector.triggerize(table, events, options[:before])
356:         end

[Validate]