Обсуждение: Wanted: RelationIsVisible interface
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 (Moved from Re: [PATCHES] small psql patch - show Schema name for \dt \dv \dS) >> 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 ... > I see what you are saying, and this is fine for those cases in which the user has supplied the schema, but I don't see how it solves the ordering problem. If I have a table public.foo and greg.foo, and both are "visible", how will psql know which one to show? It should be showing the first in the schema search path, in other words, the one that will get effected by a SQL statement such as "SELECT * FROM foo;" This is of course the original temp table problem with psql. At any rate, I will play with the RelationIsVisible stuff when somebody writes a SQL interface to it (or I write it myself, but that will take quite a long time :) Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200208091839 -----BEGIN PGP SIGNATURE----- Comment: http://www.turnstep.com/pgp.html iD8DBQE9VESovJuQZxSWSsgRAlHXAJkBqylRDegbwhNxsYr/CPIFYvnRQQCgxC0G VUDWfnprg1+pcKNZvE1gEzY= =w7hd -----END PGP SIGNATURE-----
Greg Sabino Mullane wrote: > At any rate, I will play with the RelationIsVisible stuff when somebody > writes a SQL interface to it (or I write it myself, but that will take > quite a long time :) > Looks like Tom just committed this; see: http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/catalog/namespace.c.diff?r1=1.29&r2=1.30 Datum pg_table_is_visible(PG_FUNCTION_ARGS); Datum pg_type_is_visible(PG_FUNCTION_ARGS); Datum pg_function_is_visible(PG_FUNCTION_ARGS); Datum pg_operator_is_visible(PG_FUNCTION_ARGS); Datum pg_opclass_is_visible(PG_FUNCTION_ARGS); They all accept an oid and return bool. Joe
OK, let's get started updating all our db access routines. We only have 22 days left until beta. --------------------------------------------------------------------------- Joe Conway wrote: > Greg Sabino Mullane wrote: > > At any rate, I will play with the RelationIsVisible stuff when somebody > > writes a SQL interface to it (or I write it myself, but that will take > > quite a long time :) > > > > Looks like Tom just committed this; see: > > http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/catalog/namespace.c.diff?r1=1.29&r2=1.30 > > Datum pg_table_is_visible(PG_FUNCTION_ARGS); > Datum pg_type_is_visible(PG_FUNCTION_ARGS); > Datum pg_function_is_visible(PG_FUNCTION_ARGS); > Datum pg_operator_is_visible(PG_FUNCTION_ARGS); > Datum pg_opclass_is_visible(PG_FUNCTION_ARGS); > > They all accept an oid and return bool. > > Joe > > > ---------------------------(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, Pennsylvania19073
"Greg Sabino Mullane" <greg@turnstep.com> writes:
>> we'd write something like
>> select ... from pg_class p, ...
>> where relname like 'foo%' and pg_relation_is_visible(p.oid) and ...
> I see what you are saying, and this is fine for those cases in which the user
> has supplied the schema, but I don't see how it solves the ordering problem.
> If I have a table public.foo and greg.foo, and both are "visible", how will
> psql know which one to show?
By definition, only one can be visible: a table named "foo" hides any
other "foo" that's later in the search path. This is exactly the point
that the is_visible tests cover, while there isn't any easy way to
handle it in SQL by comparison of a relnamespace value to a
current_schemas list --- that comparison doesn't tell you anything
about possible conflicting names elsewhere in the search path.
regards, tom lane