
-----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-----



Fri,  9 Aug 2002 22:40:34 -0000
"Greg Sabino Mullane" <greg@turnstep.com>
Wanted: RelationIsVisible interface
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


Fri, 09 Aug 2002 15:41:33 -0700
Joe Conway <mail@joeconway.com>
Re: Wanted: RelationIsVisible interface

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, Pennsylvania 19073

Fri, 9 Aug 2002 21:56:45 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Wanted: RelationIsVisible interface
"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

Sat, 10 Aug 2002 00:35:17 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Wanted: RelationIsVisible interface 