Module: PgSaurus::Tools

Extended by:
Tools
Included in:
Tools
Defined in:
lib/pg_saurus/tools.rb

Overview

Provides utility methods to work with PostgreSQL databases. Usage:

PgSaurus::Tools.create_schema "services"  # => create new PG schema "services"
PgSaurus::Tools.create_schema "nets"
PgSaurus::Tools.drop_schema "services"    # => remove the schema
PgSaurus::Tools.schemas                   # => ["public", "information_schema", "nets"]
PgSaurus::Tools.move_table_to_schema :computers, :nets
PgSaurus::Tools.create_view view_name, view_definition # => creates new DB view
PgSaurus::Tools.drop_view view_name       # => removes the view
PgSaurus::Tools.views                     # => ["x_view", "y_view", "z_view"]

Instance Method Summary collapse

Instance Method Details

#create_schema_if_not_exists(schema_name) ⇒ void

Note:

Supports PostgreSQL 9.3+

This method returns an undefined value.

Create a schema if it does not exist yet.



21
22
23
24
 
# File 'lib/pg_saurus/tools.rb', line 21

def create_schema_if_not_exists(schema_name)
  sql = %{CREATE SCHEMA IF NOT EXISTS "#{schema_name}"}
  connection.execute sql
end

#create_view(view_name, view_definition) ⇒ Object

Creates PostgreSQL view

Parameters:

  • view_name (String, Symbol)
  • view_definition (String) 


51
52
53
54
 
# File 'lib/pg_saurus/tools.rb', line 51

def create_view(view_name, view_definition)
  sql = "CREATE VIEW #{view_name} AS #{view_definition}"
  connection.execute sql
end

#drop_schema_if_exists(schema_name) ⇒ void

This method returns an undefined value.

Ensure schema does not exists.



29
30
31
 
# File 'lib/pg_saurus/tools.rb', line 29

def drop_schema_if_exists(schema_name)
  connection.drop_schema(schema_name, if_exists: true)
end

#drop_view(view_name) ⇒ Object

Drops PostgreSQL view

Parameters:

  • view_name (String, Symbol) 


58
59
60
61
 
# File 'lib/pg_saurus/tools.rb', line 58

def drop_view(view_name)
  sql = "DROP VIEW #{view_name}"
  connection.execute sql
end

#move_table_to_schema(table, new_schema) ⇒ Object

Move table to another schema without loosing data, indexes or constraints.

Parameters:

  • table (String) —

    table name (schema prefix is allowed)

  • new_schema (String) —

    schema where table should be moved to



42
43
44
45
46
 
# File 'lib/pg_saurus/tools.rb', line 42

def move_table_to_schema(table, new_schema)
  schema, table = to_schema_and_table(table)
  sql = %{ALTER TABLE "#{schema}"."#{table}" SET SCHEMA "#{new_schema}"}
  connection.execute sql
end

#schemas ⇒ Object

Returns an array of existing schemas.



34
35
36
37
 
# File 'lib/pg_saurus/tools.rb', line 34

def schemas
  sql = "SELECT nspname FROM pg_namespace WHERE nspname !~ '^pg_.*' order by nspname"
  connection.query(sql).flatten
end

#to_schema_and_table(table_name) ⇒ Array[String, String]

Extract schema name and table name from qualified table name

Parameters:

  • table_name (String, Symbol) —

    table name

Returns:

  • (Array[String, String]) —

    schema and table



82
83
84
85
86
 
# File 'lib/pg_saurus/tools.rb', line 82

def to_schema_and_table(table_name)
  table, schema = table_name.to_s.split(".", 2).reverse
  schema ||= "public"
  [schema, table]
end

#views ⇒ Object

Returns an array of existing, non system views.



64
65
66
67
68
69
70
71
 
# File 'lib/pg_saurus/tools.rb', line 64

def views
  sql = "  SELECT table_schema, table_name, view_definition\n  FROM INFORMATION_SCHEMA.views\n  WHERE table_schema NOT IN ('pg_catalog','information_schema')\n  SQL\n  connection.execute sql\nend\n"