Resent-From: POSTGRES mailing list <postman@postgres.Berkeley.EDU>
Resent-Message-Id: <199407191735.KAA19144@nobozo.CS.Berkeley.EDU>
Sender: owner-postman@postgres.Berkeley.EDU
From: jimbo@crseo.ucsb.edu
Message-Id: <9407191728.AA27339@ crseo.ucsb.edu>
To: wolf@molout.tutkie.tut.ac.jp (Wolf-D. Ihlenfeldt)
Cc: postgres@postgres.Berkeley.EDU
Subject: Re: How to retrieve owner of class 
In-Reply-To: Your message of "Tue, 19 Jul 94 17:35:19 +0200."
             <9407190835.AA03166@molout.tutkie.tut.ac.jp> 
Date: Tue, 19 Jul 94 10:28:36 -0700
Resent-To: postgres-redist@postgres.Berkeley.EDU
Resent-Date: Tue, 19 Jul 94 10:35:53 -0700
Resent-XMts: smtp


>
>
>In Postgres4.2, the id of the owner of a class is stored as
>pg_class.relowner as type 'oid'.
>This entry refers to pg_user.usesysid, which is of type 'int2'.
>
>Question 1: Why are the types different ?
>
>I tried to retrieve class name and owner by
>retrieve (c.relname, u.usename) from c in pg_class, u in pg_user
>where u.usesysid = c.relowner
>
>and I get
>NOTICE:Jul 19 17:53:31:there is no operator = for types int2 and oid
>NOTICE:Jul 19 17:53:31:You will either have to retype this query using an
>NOTICE:Jul 19 17:53:31:explicit cast, or you will have to define the operator
>WARN:Jul 19 17:53:31:= for int2 and oid using DEFINE OPERATOR
>
>Casting does not work:
>
>retrieve (c.relname, u.usename) from c in pg_class, u in pg_user
>where u.usesysid = c.relowner::int2
>NOTICE:Jul 19 17:54:16:there is no operator = for types int2 and oid
>NOTICE:Jul 19 17:54:17:You will either have to retype this query using an
>NOTICE:Jul 19 17:54:17:explicit cast, or you will have to define the operator
>WARN:Jul 19 17:54:17:= for int2 and oid using DEFINE OPERATOR
>
>
>I solved the problem by writing a private oid/int2 comparison 
>function and introducing a '=' operator. However, I wonder if
>this is really necessary.
>Question 2: Is there another way to specify this query ?
>
>Question 3: Looking at the built-in functions, I found names like
>'oidint2' and functions for this type. However, these are NO 
>oid/int2 functions, but seem to operate on an exotic oidint2 type.
>What is this type being used for ?
>


This is just like the "database administrators and their databases" example
in chapter 15 of the user's manual.  For all classes, use:


retrieve(u.usename, c.relname)
from u in pg_user, c in pg_class
where u.usesysid = int2in(int4out(c.relowner))


For just user defined classes (no indices, no large objects, no system
classes), use:


retrieve(u.usename, c.relname)
from u in pg_user, c in pg_class
where u.usesysid = int2in(int4out(c.relowner))
and c.relkind = "r"
and c.relname !~ "^pg_"
and c.relname !~ "^Xinv"


-Jim

 __________________________________________________________
  Jim Davidson                     |  jimbo@crseo.ucsb.edu
  CSL / Center for Remote Sensing  |  jimbo@sbitp.bitnet
  University of California         |  Phone (805)893-8475
  Santa Barbara, CA 93106          |  Fax           -2578

==============================================================================
   To add/remove yourself to/from the POSTGRES mailing list: send mail with 
   the subject line ADD or DEL to "postgres-request@postgres.Berkeley.EDU"

   If this fails, send mail to "post_questions@postgres.Berkeley.EDU" and
   a human will deal with it.  DO NOT post to the "postgres" mailing list.
==============================================================================