agora inbox for postgres@postgres.berkeley.edu  

help / color / mirror / Atom feed

From: jimbo@crseo.ucsb.edu
To: Wolf-D. Ihlenfeldt <wolf@molout.tutkie.tut.ac.jp>
Cc: postgres@postgres.Berkeley.EDU
Subject: Re: How to retrieve owner of class
Date: Tue, 19 Jul 94 10:28:36 -0700
Message-ID: <9407191728.AA27339@ crseo.ucsb.edu> (raw)
In-Reply-To: <9407190835.AA03166@molout.tutkie.tut.ac.jp>


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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: postgres@postgres.berkeley.edu
  Cc: jimbo@crseo.ucsb.edu, wolf@molout.tutkie.tut.ac.jp
  Subject: Re: How to retrieve owner of class
  In-Reply-To: <9407191728.AA27339@ crseo.ucsb.edu>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox