By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,969 Members | 2,250 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,969 IT Pros & Developers. It's quick & easy.

Listing views

P: n/a
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

Nov 23 '05 #1
Share this Question
Share on Google+
11 Replies


P: n/a
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

Nov 23 '05 #2

P: n/a
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

Nov 23 '05 #3

P: n/a
Olivier Guilyardi <ml@xung.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

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

Nov 23 '05 #4

P: n/a
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

Nov 23 '05 #5

P: n/a
ra@konvergencia.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

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

Nov 23 '05 #6

P: n/a
Olivier Guilyardi <ml@xung.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

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

http://archives.postgresql.org

Nov 23 '05 #7

P: n/a

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

Nov 23 '05 #8

P: n/a
Doug McNaught wrote:
Olivier Guilyardi <ml@xung.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

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

Nov 23 '05 #9

P: n/a
Olivier Guilyardi <ml@xung.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

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

Nov 23 '05 #10

P: n/a
Olivier Guilyardi <ml@xung.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

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

Nov 23 '05 #11

P: n/a

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

Nov 23 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.