Listing views

Hi,
What about
mydb=# \dv
??
Doesn't it works fine. I don't see any pg views.
Regards,
Kaloyan

Olivier Guilyardi 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');

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:

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

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

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

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

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