Return-Path: pg_adm@postgres.berkeley.edu
Received: by postgres.Berkeley.EDU (5.61/1.29)
	id AA23443; Tue, 26 Jan 93 11:52:23 -0800
Date: Tue, 26 Jan 93 11:52:23 -0800
Message-Id: <9301261952.AA23443@postgres.Berkeley.EDU>
From: klm@nist.gov (Ken Manheimer)
Subject: Re: handling of complex return types in functions
To: postgres@postgres.berkeley.edu
Sender: pg_adm@postgres.berkeley.edu

I was informed of or found the answer to several of the questions i
raised yesterday.

For the first question:

mao@postgres.Berkeley.EDU Mon Jan 25 14:37:03 1993 wrote

> > * define function OwnedClasses (language="postquel",
> >                                 returntype = setof char16)
> >           arg is (oid)
> >         as "retrieve (c.relname) from c in pg_class
> >         where c.relowner= $1 "
> > 
> > The problem comes up when i try to get *all* the fields in a query, eg:
> > 
> > * retrieve (x = OwnedClasses(8::oid)) \g
> > 
> > The interpreter complains about assigning a complex type to a
> > variable:
> 
> this is a known bug in 4.0.1; unfortunately, you can only examine a single
> column of tuples returned by postquel functions.  we hope to remedy this
> in a future release of the software.
> 					mike

Thanks for filling me in, mike.  (In case you're interested, i would
find it useful to have this implemented.)

Second question - looking through the procedures catalog (pg_proc), i
discovered two functions, NullValue and NonNullValue, which you can
use to condition queries on null field values.  Eg:

  retrieve (group.groupname) where NullValue(group.password)

will show all entries in a 'group' class with null password fields.

Third question - obtaining function definitions - the functions are
all registered in the pg_proc catalogue, with their definitions
("source") in the pg_proc.prosrc field.  The query:

  retrieve (pg_proc.proname, pg_proc.prosrc)
    where pg_proc.proowner = "8"::oid\g

retrieves the name and statements for all procedures declared by the
user with uid 8.  (System functions usually have stubs in their
definition fields, so don't expect to garner extra information about
them with this query.)

The fourth question is more open ended, concerning undocumented
features and differences of the 4.0.1 release.  I don't have anything
specific to report here.

In case anyone is interested, in my wanderings i developed some spiffy
queries.  One, which has been helpful for assessing the postgres user
environment, obtains the class attributes and attribute types for a
class, given the class name:

  retrieve (a.attname, t.typname)
    from a in pg_attribute, t in pg_type, c in pg_class
             where c.relname = <class name>
               and a.attrelid = c.oid
               and t.oid = a.atttypid

Unfortunately, this generally takes a while to run.  (I'd like to use
secondary indices to speed it up, but i probably misunderstand them,
and the system doesn't seem to like what i tried to do - i had to
eradicate and recreate the database i was working in to get back to
operation...)

(In case it's not obvious, i really like the ability in this system to
have access to classes, procedures, types, etc, as db objects.  I
gather from the documentation that that is not common in conventional
relational databases...)

That's about all the poking around i'll be doing, i just thought
someone else might be interested in some of the things i found.

Ken
klm@nist.gov, 301 975-3539