Description: ------------ Hi, I'm using DB 1.6.5 Trying to list the views from my Postgresql (7.2) database, I got some irrelevant views returned (named "pg_*"). These are postgresql internal views. This actually comes from DB_pgsql::getSpecialQuery which doesn't ignore Postgresql's internal objects for views as it does for tables. -- Olivier Guilyardi Reproduce code: --------------- require_once 'PEAR.php'; require_once 'DB.php'; $db =& DB::connect("pgsql://..."); $db->query("CREATE TABLE a (i INTEGER, j INTEGER)"); $db->query("CREATE view v AS SELECT i FROM a"); $views = $db->getListOf("views"); print_r ($views); /* A small patch : 815c815 < return 'SELECT viewname FROM pg_views'; --- > return "SELECT viewname FROM pg_views WHERE viewname !~ '^pg_'"; */ Expected result: ---------------- Array ( [0] => v ) Actual result: -------------- Array ( [0] => pg_stat_user_indexes [1] => pg_statio_all_indexes [2] => pg_statio_sys_indexes [3] => pg_statio_user_indexes [4] => pg_statio_all_sequences [5] => pg_statio_sys_sequences [6] => pg_statio_user_sequences [7] => pg_stat_activity [8] => pg_stat_database [9] => v [10] => pg_user [11] => pg_rules [12] => pg_views [13] => pg_tables [14] => pg_indexes [15] => pg_stats [16] => pg_stat_all_tables [17] => pg_stat_sys_tables [18] => pg_stat_user_tables [19] => pg_statio_all_tables [20] => pg_statio_sys_tables [21] => pg_statio_user_tables [22] => pg_stat_all_indexes [23] => pg_stat_sys_indexes )

Looks like you're reporting something that has already been fixed in release 1.6.5. See http://pear.php.net/bugs/bug.php?id=1549 for more info.

Hi, No I'm not, please read my bug report properly and give a try to the reproduce code, using either DB 1.6.5 or the CVS. -- og

I'm sorry. I was on vacation and on a slow connection on someone else's computer, so acted hastily. Regarding the views, I see there are 59 internal views created automatically. For example, information_schema_catalog_name, applicable_roles and table_constraints. I'm not a PostgreSQL guru, so perhaps you can help me understand why only the system views starting with "pg_" be eliminated from the results?

In Postgresql 7.2, which I used for the repoduce code, all system views have the 'pg_' prefix. I've checked against Postgresql 7.4 and can see the additional (about 30 more) views you're mentioning. I'm no Postgresql expert either. The point is that whatever you want to list (tables, functions, views, etc...) the postgresql ml archives and other docs always end up refering to the psql command line tool : '\dt' for tables, '\dv' for views, etc... psql actually translates these short commands into sql statements. But, it looks like it's not very consistant accross versions : If you happen to read C, I recommend you take a look at these : Postgresql 7.2 : http://tinyurl.com/5vy2y Postgresql 7.4 : http://tinyurl.com/594tp Both of these point at describe.c, which contain the generic function listTables(), which psql calls for listing views among other objects. Inside of listTables(), there's the "if (showSystem)" statement, which means "do we want to list system tables/views/etc or not". Now, in 7.2 this is performed with : c.relname !~ '^pg_' while, in 7.4, it relies on a join against pg_catalog.pg_namespace (aliased "n" in describe.c). And... the pg_namespace table does not exist in Postgresql 7.2 As for bugs #682 and #1607 (and my bogus #2084), this is a Postgresql backward compatibility issue, raised with version 7.3 which introduced schemas (this is what pg_namespace is all about). I believe the _only_ solution is to execute an extra query in DB_pgsql constructor or connect(), to know the Postgresql version and adapt queries accordingly.

The best fix I've seen recommeded is: return "SELECT viewname FROM pg_views WHERE schemaname='public'"; This bug has knock on effects to other packages (see bug #2778)

Fixed in CVS.