Hi!

I just came across a posting in this list, and a question arose from that 
which I'm carrying for some time. PG has *some* views in the system catalog, 
which make life easier, but some essential(?) things like 'list all tables in 
DB' has to be done in a multi-table join with special attributes. What is the 
rationale of that? Wouldn't it be easier (and more portable, see 7.3/7.2 
system catalogs vs. psql) to have views for that? Do I miss a point here?

I'd be even willing to do some work here, if considered worthwhile...

Greetings,
	Joerg
-- 
Leading SW developer  - S.E.A GmbH
Mail: joerg.hessdoerfer@sea-gmbh.com
WWW:  http://www.sea-gmbh.com

Mon, 13 Jan 2003 16:56:01 +0100
Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
\d type queries - why not views in system catalog?!?
Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com> writes:
> PG has *some* views in the system catalog, which make life easier, but
> some essential(?) things like 'list all tables in DB' has to be done
> in a multi-table join with special attributes. What is the rationale
> of that? Wouldn't it be easier (and more portable, see 7.3/7.2 system
> catalogs vs. psql) to have views for that?

Only to the extent that the views match what a particular front-end
actually wants to see.

Peter Eisentraut is currently working on adding the SQL-spec-mandated
"INFORMATION_SCHEMA" views; so as long as all you want to know is what's
in the spec, those should be your answer.  But I do not foresee psql or
pg_dump ever switching over to INFORMATION_SCHEMA, because they want to
know about some things that are Postgres-specific.

			regards, tom lane

Mon, 13 Jan 2003 11:19:46 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: \d type queries - why not views in system catalog?!? 

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
NotDashEscaped: You need GnuPG to verify this message


> Wouldn't it be easier (and more portable, see 7.3/7.2 system catalogs vs. 
> psql) to have views for that? Do I miss a point here?

Putting the \d commands into views has been on the TODO list for a long time: 
I think it is actually the only psql-related item left, until we change 
the backend protocol to indicate transaction state. I don't think a view 
would have helped with the psql 7.2/7.3 change: a lot more changed than 
simply the underlying SQL.

Some of the the backslash commands are not amenable to putting inside a 
view, as they actually compromise multiple SQL calls and some logic in 
the C code, but a few could probably be made into views. Could whomever 
added that particular TODO item expand on this?

--
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200301131137

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+IuuovJuQZxSWSsgRAgQXAKCdu0+CelZ1V2bwI/HoJHIz+a3DPACgix7u
pOcRXwHb+4NJLMeSpNaqzRM=
=0yFo
-----END PGP SIGNATURE-----



Mon, 13 Jan 2003 16:28:06 -0000
greg@turnstep.com
Re: \d type queries - why not views in system catalog?!?
On Mon, 2003-01-13 at 11:28, greg@turnstep.com wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> NotDashEscaped: You need GnuPG to verify this message
> 
> 
> > Wouldn't it be easier (and more portable, see 7.3/7.2 system catalogs vs. 
> > psql) to have views for that? Do I miss a point here?
> 
> Putting the \d commands into views has been on the TODO list for a long time: 
> I think it is actually the only psql-related item left, until we change 
> the backend protocol to indicate transaction state. I don't think a view 
> would have helped with the psql 7.2/7.3 change: a lot more changed than 
> simply the underlying SQL.

It would be a wise idea to use the INFORMATION_SCHEMA where possible for
these, as that is pretty much guaranteed to be static in format.

-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
13 Jan 2003 11:44:41 -0500
Rod Taylor <rbt@rbt.ca>
Re: \d type queries - why not views in system catalog?!?
On Mon, 2003-01-13 at 11:28, greg@turnstep.com wrote:
> 
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> NotDashEscaped: You need GnuPG to verify this message
> 
> 
> > Wouldn't it be easier (and more portable, see 7.3/7.2 system catalogs vs. 
> > psql) to have views for that? Do I miss a point here?
> 
> Putting the \d commands into views has been on the TODO list for a long time: 
> I think it is actually the only psql-related item left, until we change 
> the backend protocol to indicate transaction state. I don't think a view 
> would have helped with the psql 7.2/7.3 change: a lot more changed than 
> simply the underlying SQL.
> 
> Some of the the backslash commands are not amenable to putting inside a 
> view, as they actually compromise multiple SQL calls and some logic in 
> the C code, but a few could probably be made into views. Could whomever 
> added that particular TODO item expand on this?
> 

One idea I've always thought would be nice would be to make full fledged
C functions out of the \ commands and ship them with the database. This
way the \ commands could just be alias to "select myfunc()".  This would
help out all of us who write GUI interfaces since we would have standard
functions we could call upon, and would also help with backward
compatibility since \dv could always call select list_views(), which
would already be included with each server. One of the reasons that this
was not feasible in the past was that we needed functions that could
return multiple rows and columns easily. Now that we have that in 7.3,
it might be worth revisiting. 

Robert Treat 



13 Jan 2003 15:50:32 -0500
Robert Treat <xzilla@users.sourceforge.net>
Re: \d type queries - why not views in system catalog?!?
Oh!

That's an excellent idea.  Seemingly addresses the issue and has
value-add.  I'm not aware of any gotchas here.  Is there something that
is being overlooked?


Greg


On Mon, 2003-01-13 at 14:50, Robert Treat wrote:
> On Mon, 2003-01-13 at 11:28, greg@turnstep.com wrote:
> > 
> > -----BEGIN PGP SIGNED MESSAGE-----
> > Hash: SHA1
> > NotDashEscaped: You need GnuPG to verify this message
> > 
> > 
> > > Wouldn't it be easier (and more portable, see 7.3/7.2 system catalogs vs. 
> > > psql) to have views for that? Do I miss a point here?
> > 
> > Putting the \d commands into views has been on the TODO list for a long time: 
> > I think it is actually the only psql-related item left, until we change 
> > the backend protocol to indicate transaction state. I don't think a view 
> > would have helped with the psql 7.2/7.3 change: a lot more changed than 
> > simply the underlying SQL.
> > 
> > Some of the the backslash commands are not amenable to putting inside a 
> > view, as they actually compromise multiple SQL calls and some logic in 
> > the C code, but a few could probably be made into views. Could whomever 
> > added that particular TODO item expand on this?
> > 
> 
> One idea I've always thought would be nice would be to make full fledged
> C functions out of the \ commands and ship them with the database. This
> way the \ commands could just be alias to "select myfunc()".  This would
> help out all of us who write GUI interfaces since we would have standard
> functions we could call upon, and would also help with backward
> compatibility since \dv could always call select list_views(), which
> would already be included with each server. One of the reasons that this
> was not feasible in the past was that we needed functions that could
> return multiple rows and columns easily. Now that we have that in 7.3,
> it might be worth revisiting. 
> 
> Robert Treat 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
-- 
Greg Copeland <greg@copelandconsulting.net>
Copeland Computer Consulting


13 Jan 2003 14:56:22 -0600
Greg Copeland <greg@CopelandConsulting.Net>
Re: \d type queries - why not views in system catalog?!?
Robert Treat kirjutas T, 14.01.2003 kell 01:50:
> One of the reasons that this
> was not feasible in the past was that we needed functions that could
> return multiple rows and columns easily. Now that we have that in 7.3,
> it might be worth revisiting. 

Also, we have schemas now, so it would be easier to avoid name clashes.


-- 
Hannu Krosing <hannu@tm.ee>

14 Jan 2003 03:06:55 +0500
Hannu Krosing <hannu@tm.ee>
Re: \d type queries - why not views in system catalog?!?
Robert Treat writes:

> One idea I've always thought would be nice would be to make full fledged
> C functions out of the \ commands and ship them with the database.

The psql meta-commands are not a nicely designed set of queries that one
would encapsulate into a public library interface.  They are created for
interactive use, representing precomposed views of the database that are
thought to be useful.  If the ideas of usefulness change, then the
commands might change.

If you want to create a set of views or functions for noninteractive use
by client applications, then you need to step back and create maximally
decomposed views of the database that can be combined in all possible
ways.  The SQL information schema is such a set, but the information is
perhaps too limited for some applications.  (But I urge you too look
first.)  But any other set would have to be designed on similar
principles.

-- 
Peter Eisentraut   peter_e@gmx.net


Mon, 13 Jan 2003 23:46:28 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: \d type queries - why not views in system catalog?!?
Peter Eisentraut <peter_e@gmx.net> writes:
> Robert Treat writes:
>> One idea I've always thought would be nice would be to make full fledged
>> C functions out of the \ commands and ship them with the database.

> The psql meta-commands are not a nicely designed set of queries that one
> would encapsulate into a public library interface.  They are created for
> interactive use, representing precomposed views of the database that are
> thought to be useful.  If the ideas of usefulness change, then the
> commands might change.

I think that the proposal is to take describe.c more or less
lock-stock-and-barrel out of psql and put it in the backend instead.
It doesn't matter whether the views are orthogonal or useful for
non-interactive purposes; they're defined to do whatever we think
psql should show.

The question is whether this gives us a useful amount of decoupling of
psql from the backend version.  Certainly the describe.c code is the
stuff most subject to breakage across versions, but there are a lot of
other aspects of psql that could still break.  One fairly obvious
example of backend-dependent psql code that won't be helped this way is
the tab completion code.

While I don't have any strong objection to moving the guts of these
queries to the backend, I can't get real excited about it either;
I suspect that psql will still be pretty version-dependent.

			regards, tom lane

Mon, 13 Jan 2003 17:57:31 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: \d type queries - why not views in system catalog?!? 

What should we do with the TODO item?  Add question mark? Remove?

---------------------------------------------------------------------------

Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > Robert Treat writes:
> >> One idea I've always thought would be nice would be to make full fledged
> >> C functions out of the \ commands and ship them with the database.
> 
> > The psql meta-commands are not a nicely designed set of queries that one
> > would encapsulate into a public library interface.  They are created for
> > interactive use, representing precomposed views of the database that are
> > thought to be useful.  If the ideas of usefulness change, then the
> > commands might change.
> 
> I think that the proposal is to take describe.c more or less
> lock-stock-and-barrel out of psql and put it in the backend instead.
> It doesn't matter whether the views are orthogonal or useful for
> non-interactive purposes; they're defined to do whatever we think
> psql should show.
> 
> The question is whether this gives us a useful amount of decoupling of
> psql from the backend version.  Certainly the describe.c code is the
> stuff most subject to breakage across versions, but there are a lot of
> other aspects of psql that could still break.  One fairly obvious
> example of backend-dependent psql code that won't be helped this way is
> the tab completion code.
> 
> While I don't have any strong objection to moving the guts of these
> queries to the backend, I can't get real excited about it either;
> I suspect that psql will still be pretty version-dependent.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Mon, 13 Jan 2003 19:15:58 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: \d type queries - why not views in system catalog?!?