Re: small psql patch - show Schema name for \dt \dv \dS

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> But so far I haven't seen any convincing argument why any client query
> would really want to look at current_schemas(), either. What you
> actually want to know about is visibility, and computing that from
> current_schemas() is quite an expensive proposition. I think what
> we need to expose is RelationIsVisible and friends, not BackendId.

I need something other than RelationIsVisible for psql for the case
when someone says "\d foo" - I need to be able to decide which
"foo" table I should display: pg_temp_1.foo, public.foo, greg.foo,
etc... which schemas to search, and the order to do the searching,
is why some sort of access to current_schemas() is needed.

Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200208021010

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.7 (GNU/Linux)

iD8DBQE9SpfEvJuQZxSWSsgRAmw/AJ0RZ2vbx2zDQlFrLwRyVkZ1kvQB0ACgwyqT
9xugxhqatGJrrDWzzfez114=
=GZtD
-----END PGP SIGNATURE-----

greg(at)turnstep(dot)com writes:
> I need something other than RelationIsVisible for psql for the case
> when someone says "\d foo" - I need to be able to decide which
> "foo" table I should display: pg_temp_1.foo, public.foo, greg.foo,

Au contraire, RelationIsVisible is *exactly* what you need. I'm
envisioning that where we currently have, say,

select ... from pg_class p, ...
where relname like 'foo%' and ...

we'd write something like

select ... from pg_class p, ...
where relname like 'foo%' and pg_relation_is_visible(p.oid) and ...

An alternative approach is

select ... from pg_class p, ...
where p.oid = 'foo'::regclass

which works today and is probably more efficient, but (a) it does not
lend itself to wildcard searches, and (b) you have to be prepared to
deal with an ERROR instead of zero rows out if foo doesn't exist.

What remains to be thought about is how schemas ought to interact
with wildcard patterns --- in particular, what should you say in
psql backslash commands when you want to access something that is
*not* in your current search path? Can you wildcard the schema part?
And so forth. But visibility stops being an issue as soon as the
user tells you which schema to look in.

> etc... which schemas to search, and the order to do the searching,
> is why some sort of access to current_schemas() is needed.

current_schemas is provided for those who insist on reimplementing the
backend RelationIsVisible functionality for themselves. (pgAdmin falls
in that category, IIRC.) I don't think we want to go that route in
psql, however. It's not simple to do at the SQL level.

regards, tom lane