Though the document says that 'tables' method returns an Array of all tables and views, 'tables' method in Pg driver
only returns an Array of all tables.

A list of all tables and views can be taken by '\dtv' in psql shell, that is equivalent to the following SQL:

  SELECT n.nspname as "Schema",
    c.relname as "Name",
    CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's'
THEN 'special' END as "Type",
    u.usename as "Owner"
    FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relkind IN ('r','v','')
    AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
    AND pg_catalog.pg_table_is_visible(c.oid)
    ORDER BY 1,2;

So I wrote a patch to fix this problem.

Date: 2008-03-14 18:09
Sender: Erik Hollensbe

I'm going to enter this patch, but the context of what tables
should show is probably a hot topic of discussion amongst ruby/dbi
users, and the answer is probably in a future spec.

Counting this "closed", but moving it to "feature
requests" so I can track it for the upcoming feature
release.

Date: 2008-03-13 12:55
Sender: Erik Hollensbe

I see what you're saying.

Let me add a test that breaks this current relation (which it
seems is entirely possible) and I'll move forward.

Thanks for going through this.

-Erik

Date: 2008-03-13 05:31
Sender: Mike Pomraning

Please re-open -- the applied patch wrongly, I think, excludes
tables in pg_catalog and pg_toast schemas.

- This is a backwards compat break.
  Ouch, my code!

- Excluding pg_catalog is incorrect per se (IMHO)
  Tables are tables, even if they're information_schema or
  db-specific "system catalogs."  You're not alone
in doing
  this, Oracle.rb and FrontBase.rb do it, too -- but I
  can't say you're in good company.

- Possibly non-sensical behavior introduced.
  If I have a table "public"."pg_class",
it won't show up
  in tables() even if my schema search_path
  (pg_table_is_visible) would qualify an unadorned
  "pg_class" identifier to it in some ordinary SQL.

Recommend simply:

  SELECT c.relname
    FROM pg_catalog.pg_class c
  WHERE c.relkind IN ('r','v')
        AND
        pg_catalog.pg_table_is_visible(c.oid)

Date: 2008-02-24 20:20
Sender: Erik Hollensbe

This has been fixed and will is slated for the next release.

Date: 2008-02-23 20:58
Sender: Christopher Maujean

assigning to Erik, the patch is still broken, and it's way old.

Date: 2004-11-22 11:35
Sender: Kazuhiko 

Logged In: YES 
user_id=1446

Since I uploaded a broken patch, I upload a correct one.

Date: 2004-11-22 11:34
Sender: Kazuhiko 

Logged In: YES 
user_id=1446

Since I uploaded a broken patch, I upload a correct one.