Search Path Example (postgresql)

pg_search_path_example.rb

require 'pg'
$db = PG.connect # to default db

# override the `exec` method so that query results get displayed
def $db.exec(*, **)
  super.map { |r| r.transform_keys &:intern }
end

# A schema with a table in it
def make_schema
  $db.exec <<~SQL
  create schema omghi;
  create table omghi.customers(id serial primary key, name text);
  insert into omghi.customers (name) values ('josh'), ('makis'), ('marissa'), ('mohamed');
  SQL
end

# A view that does not specify the schema
def make_view
  $db.exec <<~SQL
  create view nonjoshes as
    select * from customers  -- schema not specified here
    where name <> 'josh';
  SQL
end

# The view cannot find the table, so it errors
$db.exec 'begin'
$db.exec 'show search_path' # => [{:search_path=>"\"$user\", public"}]
make_schema
make_view rescue $!.message # => "ERROR:  relation \"customers\" does not exist\nLINE 2:   select * from customers  -- schema not specified here\n                        ^\n"


# Add the schema to the search path and it will be able to find the table, so it will work
$db.exec 'rollback; begin'
$db.exec 'set search_path = "$user", public, omghi'
make_schema 
make_view
$db.exec 'select * from nonjoshes'
# => [{:id=>"2", :name=>"makis"},
#     {:id=>"3", :name=>"marissa"},
#     {:id=>"4", :name=>"mohamed"}]