create_item_children_count.rb

class CreateItemChildrenCountView < ActiveRecord::Migration
  def self.up
    execute <<-SQL
      CREATE VIEW item_children_count AS
      SELECT parent_id AS item_id, COUNT(*) as children_count
      FROM items GROUP BY parent_id;
    SQL
  end

  def self.down
    execute <<-SQL
      DROP VIEW item_children_count;
    SQL
  end
end

item.rb

class Item < ActiveRecord::Base
  class ChildrenCountView < ActiveRecord::Base
    set_table_name :item_children_count
    set_primary_key :item_id
    belongs_to :item
    def readonly?
      true
    end
  end
  has_one :children_count_view, :class_name => 'ChildrenCountView', :readonly => true
  scope :with_children, joins(:children_count_view).where('item_children_count.children_count > 0')

  def children_count
    children_count_view.andand.children_count || 0
  end
  def children?
    children_count > 0
  end
end

postgresql_views.rb

class ActiveRecord::ConnectionAdapters::PostgreSQLAdapter
  def tables(name = nil)
    query(<<-SQL, name).map { |row| row[0] } +
      SELECT tablename
      FROM pg_tables
      WHERE schemaname = ANY (current_schemas(false))
    SQL
    query(<<-SQL, name).map { |row| row[0] }
      SELECT viewname
      FROM pg_views
      WHERE schemaname = ANY (current_schemas(false))
    SQL
  end

  def table_exists?(name)
    name          = name.to_s
    schema, table = name.split('.', 2)

    unless table # A table was provided without a schema
      table  = schema
      schema = nil
    end

    if name =~ /^"/ # Handle quoted table names
      table  = name
      schema = nil
    end

    query(<<-SQL).first[0].to_i > 0 ||
      SELECT COUNT(*)
      FROM pg_tables
      WHERE tablename = '#{table.gsub(/(^"|"$)/,'')}'
      #{schema ? "AND schemaname = '#{schema}'" : ''}
    SQL
    query(<<-SQL).first[0].to_i > 0
      SELECT COUNT(*)
      FROM pg_views
      WHERE viewname = '#{table.gsub(/(^"|"$)/,'')}'
      #{schema ? "AND schemaname = '#{schema}'" : ''}
    SQL
  end
end