Subject: How to retrieve owner of class

From: [email-masked] (Wolf-D. Ihlenfeldt)
Date: Tue, 19 Jul 94 17:35:19 jst

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 ?

WDI

==============================================================================
   To add/remove yourself to/from the POSTGRES mailing list: send mail with 
   the subject line ADD or DEL to "[email-masked]"

   If this fails, send mail to "[email-masked]" and
   a human will deal with it.  DO NOT post to the "postgres" mailing list.
==============================================================================

---

From: [email-masked]
Date: Tue, 19 Jul 94 10:28:36 -0700

>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                     |  [email-masked]
  CSL / Center for Remote Sensing  |  [email-masked]
  University of California         |  Phone (805)893-8475
  Santa Barbara, CA 93106          |  Fax           -2578