Listing views

[Olivier Guilyardi]

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

[Kaloyan Iliev Iliev]

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[ra]

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Doug McNaught]

Olivier Guilyardi <[e-mail]> 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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Olivier Guilyardi]

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Olivier Guilyardi]

[e-mail] 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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Doug McNaught]

Olivier Guilyardi <[e-mail]> 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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Olivier Guilyardi]

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Olivier Guilyardi]

Doug McNaught wrote:

Olivier Guilyardi <[e-mail]> 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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Doug McNaught]

Olivier Guilyardi <[e-mail]> 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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Olivier Guilyardi <[e-mail]> 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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Olivier Guilyardi]

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend