Thread: 7.4 Wishlist

SHOW
----

I think 7.4 could and really should implement SHOW command similar to
MySQL. Listing tables/foreign keys/views and so isn't just psql problem,
but common to many interface implementations and maintenance tools. I 
think it's wrong to rely on pg_* system tables on these. 

If you think of some probable new user (changing from MySQL) who asks how
to query list of tables in PostgreSQL and gets the answer (from psql -E)  
that is:

SELECT n.nspname as "Schema", c.relname as "Name", CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN
 
'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type", u.usename as "Owner"
FROM pg_catalog.pg_class c    LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner    LEFT JOIN
pg_catalog.pg_namespacen ON n.oid = c.relnamespace
 
WHERE c.relkind IN ('r','v','S','')     AND n.nspname NOT IN ('pg_catalog', 'pg_toast')     AND
pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

opposed to something like

SHOW TABLES_AND_VIEWS

as in MySQL, which DBMS would this user prefer?

I further suggest that these SHOW command parameters (like tables and
views) could be views in special system schema, so it could be easy to
update them just by changing templates. Maybe 7.5 (if it had introduced
new features) could provide downward compatibility to 7.4 ready clients by
allowing the use of SHOW-views from different schema (like
pg_show_743_compat)

:)


Stored procedures used in implementing new syntax
-------------------------------------------------

This is an implementation detail suggestion. Would it be possible that 
new syntax in SQL could be implemented in different languages than C.

We

Ups... i sent an early draft of my post by accident, sorry...

-- 
Antti Haapala
+358 50 369 3535
ICQ: #177673735

On Wed, 2003-01-22 at 04:55, Antti Haapala wrote:
> SHOW
> ----
>
> I think 7.4 could and really should implement SHOW command similar to
> MySQL. Listing tables/foreign keys/views and so isn't just psql problem,


Actually, in 7.4 I'd tell them to:

select * from information_schema.tables;


This is a far more portable method.

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

> > SHOW
> > ----
> > 
> > I think 7.4 could and really should implement SHOW command similar to
> > MySQL. Listing tables/foreign keys/views and so isn't just psql problem,
> 
> Actually, in 7.4 I'd tell them to:
> 
> select * from information_schema.tables;
> 
> This is a far more portable method.

Yep. You know it was just a draft which wasn't actually meant to be
sent...

I think the contents of that information schema could and should be user
modifiable views...

I needed to administer one of my dbs on 7.3 from another computer with
psql 7.2.3 -- for the computer had PG 7.2.3 running, and \d on for example
views failed gratuituosly with error "relation pg_relcheck not found". Of
course the dumps, sql commands and tsv data etc. I needed were only on
that 7.2.3 machine...

Many interfaces need to enumerate tables & databases etc. I'm not
interested in having different versions of for example DBD::Pg... one, the
most up-to-date, version should do the job. Against whatever version of
postmaster I want to use it.

So maybe backwards compatibility could be introduced also (older clients &
applications against newer DBMS) by having different information schemas
for different client versions... don't know, how it should be actually
done. Maybe there would be schemas "info_compat_74", "info_compat_75"  
and "information_schema_80" in PostgreSQL 8.0 :P and "information_schema"  
could be alias to most appropriate of these, depending on client version
:) (dunno).

-- 
Antti Haapala 
+358 50 369 3535 
ICQ: #177673735