Re: Listing views

Hi,

No, I need an SQL query, this about the following (PHP) PEAR bug:
http://pear.php.net/bugs/bug.php?id=2085

No psql, just SQL.

In Postgresql 7.2, psql/describe.c/listTables() contains :

if (showSystem)
strcat(buf, " AND c.relname ~ '^pg_'\n");
else
strcat(buf, " AND c.relname !~ '^pg_'\n");

While, in Postgresql 7.4 I see :

if (showSystem)
appendPQExpBuffer(&buf, " AND n.nspname = 'pg_catalog'\n");
else
appendPQExpBuffer(&buf, " AND n.nspname NOT IN ('pg_catalog', 'pg_toast')\n");

"n" is here an alias for the pg_namespace table, which does not exist
in 7.2, since schemas where introduced with 7.3...

Am I wrong or is this a backward compatibility issue that forbids
listing views/tables/whatever with an identical SQL query on 7.2 and 7.4 ?

Regards

--
og

Kaloyan Iliev Iliev wrote:
> Hi,
> What about
> mydb=# \dv
> ??
> Doesn't it works fine. I don't see any pg views.
> Regards,
> Kaloyan
>
> Olivier Guilyardi wrote:
>
>> Hi,
>>
>> I'm trying to list views, eliminating internal ones from the output.
>> Using 7.2, I found this simple statement :
>> SELECT viewname FROM pg_views WHERE viewname !~ '^pg_';
>>
>> It works fine, ignoring 23 pg_* tables. And I get my actual views
>> returned.
>>
>> But, with 7.4, I get many (about 30) more system views, as
>> table_constraints,
>> table_privileges, tables, etc... And these do not have any 'pg' prefix.
>>
>> Do you know of some query that would properly list views, wether it's
>> running
>> on Postgresql 7.4 or 7.2 and lower ?
>> --
>> og
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 9: the planner will ignore your desire to choose an index scan if
>> your
>> joining column's datatypes do not match
>>
>>
>