Thread: More schema support for psql
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Please ignore the previous psql schema patch. It was merely a warmup. This version covers most everything, and includes support for some wildcard type matching of schemas and tables, while trying to remain true to the pre-schema ways of doing things. As before, schema names are always shown along with the table name, to clear up any confusion. All references to pg_ tables are now fully qualified, since nothing is stopping anyone from now running the command CREATE TABLE public.pg_class (foobar date); When a schema name is not given for things such as "\d foo", psql will do its best to find the correct one. Unlike the previous patch, this is more centralized so future changes will be easier. Currently, it does temporary relations first (in other words, temporary schemas), then non-public schemas (in order of last creation date), then public schemas, and finally the system schemas. So \d pg_class will still work, and "\d mytable" will finally show you your *temporary* "mytable". :) You can see your permanent one by specifying the schema name, while "\dt mytable" will show you all tables of that name in all schemas. Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200206241229 -----BEGIN PGP SIGNATURE----- Comment: http://www.turnstep.com/pgp.html iD8DBQE9F0mYvJuQZxSWSsgRAkSYAJ9Mwh+IAV9rI6ZWKZNUq2szt1R9SwCdHBDp I/8sd/2H7fmvalz1YWchLRA= =xtFC -----END PGP SIGNATURE-----
Attachment
"Greg Sabino Mullane" <greg@turnstep.com> writes:
> This version covers most everything, and includes support for some
> wildcard type matching of schemas and tables,
Like what? I don't much want to reverse-engineer your code to discover
what you think should happen ... and you don't seem to have included any
documentation patches.
In general I'd recommend posting a straw-man proposal for the behavior
before you go to the trouble of writing code. (I had solicited ideas
awhile back, but don't recall that we arrived at any consensus.)
> All references to pg_ tables are now fully qualified
Yeah, I've been wondering whether we shouldn't do that. It will break
psql's \d commands for use with pre-7.3 backends, but of course the
joins against pg_namespace will pretty much do that anyway. Does anyone
want to bite the bullet and make psql backend-version-aware, like
pg_dump is? It would be a lot more work and code bulk, so I'm not sure
it's worth the trouble, but ...
> Currently, it does
> temporary relations first (in other words, temporary schemas), then
> non-public schemas (in order of last creation date), then public schemas,
> and finally the system schemas.
Surely it should pay attention to the search path. I don't think that
\d should show relations that are outside the current path (unless a
qualified name pattern is given).
I wonder in fact whether we could avoid having so much of this logic on
the psql side, and rely more on the backend to do the searching. I'm
not saying that the available name lookup functions are adequate for
psql yet --- but it's not too late to improve them if needed.
regards, tom lane
Tom Lane has applied this patch. Thanks. --------------------------------------------------------------------------- Greg Sabino Mullane wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > > Please ignore the previous psql schema patch. It was merely a warmup. > This version covers most everything, and includes support for some > wildcard type matching of schemas and tables, while trying to remain > true to the pre-schema ways of doing things. As before, schema names > are always shown along with the table name, to clear up any confusion. > All references to pg_ tables are now fully qualified, since nothing > is stopping anyone from now running the command > > CREATE TABLE public.pg_class (foobar date); > > When a schema name is not given for things such as "\d foo", psql will do > its best to find the correct one. Unlike the previous patch, this is more > centralized so future changes will be easier. Currently, it does > temporary relations first (in other words, temporary schemas), then > non-public schemas (in order of last creation date), then public schemas, > and finally the system schemas. So \d pg_class will still work, and > "\d mytable" will finally show you your *temporary* "mytable". :) > You can see your permanent one by specifying the schema name, while > "\dt mytable" will show you all tables of that name in all schemas. > > Greg Sabino Mullane greg@turnstep.com > PGP Key: 0x14964AC8 200206241229 > > -----BEGIN PGP SIGNATURE----- > Comment: http://www.turnstep.com/pgp.html > > iD8DBQE9F0mYvJuQZxSWSsgRAkSYAJ9Mwh+IAV9rI6ZWKZNUq2szt1R9SwCdHBDp > I/8sd/2H7fmvalz1YWchLRA= > =xtFC > -----END PGP SIGNATURE----- > [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- 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