Listing views

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

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
>
>

On Tuesday 10 August 2004 03:14, Olivier Guilyardi wrote:
> SELECT viewname FROM pg_views WHERE viewname !~ '^pg_';
with 7.4 :

SELECT viewname FROM pg_views WHERE schemaname NOT IN
('pg_catalog','information_schema');

Albert

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
>>
>>
>

ra(at)konvergencia(dot)hu wrote:
> On Tuesday 10 August 2004 03:14, Olivier Guilyardi wrote:
>
>>SELECT viewname FROM pg_views WHERE viewname !~ '^pg_';
>
> with 7.4 :
>
> SELECT viewname FROM pg_views WHERE schemaname NOT IN
> ('pg_catalog','information_schema');

For me, the best is an identical query for both 7.2 and 7.4. Since
the pg_views schemaname field does not exist in 7.2, this last query
is going to raise an error.

Let's take, say :
SELECT viewname FROM pg_views
WHERE schemaname NOT IN ('pg_catalog','information_schema')
AND viewname !~ '^pg_';

Is there an SQL trick so that this does not raise an error in 7.2 ?
Something like IF FIELD EXISTS (this is pseudo-code) ?
--
og

Olivier Guilyardi <ml(at)xung(dot)org> writes:

> "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 ?

System catalog layouts have never been guaranteed for backward
compatibility. Going forward, the stable way to find out about your
tables is the SQL_standard "information_schema" which first appeared in
7.4 IIRC. This doesn't help you with 7.2 though--you'll have to
handle it specially. :(

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863

Doug McNaught wrote:

> Olivier Guilyardi <ml(at)xung(dot)org> writes:
>>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 ?
>
> System catalog layouts have never been guaranteed for backward
> compatibility. Going forward, the stable way to find out about your
> tables is the SQL_standard "information_schema" which first appeared in
> 7.4 IIRC. This doesn't help you with 7.2 though--you'll have to
> handle it specially. :(

Okay, that's what I thought : an initial little query to identify the server
version, so that subsequent queries can be adapted accordingly...

But, since this "What Postgresql version is this ?" query is silently performed
by the library I'm interested in, what the host application is not supposed to
know, it must be ensured that it's not going to break : is version() the way to
go ? Could there be some permission issues with very restrictive user accounts ?
Some better _only-one-query_ way to identify the server version/features ?

--
og

Olivier Guilyardi <ml(at)xung(dot)org> writes:

> Okay, that's what I thought : an initial little query to identify the server
> version, so that subsequent queries can be adapted accordingly...
>
> But, since this "What Postgresql version is this ?" query is
> silently performed by the library I'm interested in, what the host
> application is not supposed to know, it must be ensured that it's
> not going to break : is version() the way to go ? Could there be
> some permission issues with very restrictive user accounts ? Some
> better _only-one-query_ way to identify the server version/features
> ?

'SELECT version();' should work. There will be no permission issues
with calling that function unless you set the database up that way--it
has PUBLIC execute permission by default.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863

Doug McNaught wrote:
>
> 'SELECT version();' should work. There will be no permission issues
> with calling that function unless you set the database up that way--it
> has PUBLIC execute permission by default.

Good

--
og

Olivier Guilyardi <ml(at)xung(dot)org> writes:
> Doug McNaught wrote:
>> 'SELECT version();' should work. There will be no permission issues
>> with calling that function unless you set the database up that way--it
>> has PUBLIC execute permission by default.

> Good

In theory it's possible for that to fail in 7.3: if someone makes their
own function named version() and then alters the default search path so
that their function is found before the one in pg_catalog, then the
wrong function would be invoked.

In practice I'd say this falls in the category of "don't do that then".

regards, tom lane