Return-Path: pg_adm@postgres.berkeley.edu
Received: by postgres.Berkeley.EDU (5.61/1.29)
	id AA15219; Mon, 25 Jan 93 08:20:17 -0800
Date: Mon, 25 Jan 93 08:20:17 -0800
Message-Id: <9301251620.AA15219@postgres.Berkeley.EDU>
From: klm@nist.gov (Ken Manheimer)
Subject: handling of complex return types in functions
To: postgres@postgres.berkeley.edu
Sender: pg_adm@postgres.berkeley.edu

I am just becoming familiar with postgres, and have come up against
some questions which i don't know how to resolve myself.  I'm new not
only to postgres, but to databases in general, so there's some danger
that my questions are naieve or misguided.  I'm sorry if that turns
out to be the case, but the answers i'm seeking would be helpful to me
one way or the other, so i'm hoping someone can put me on course.

I'm using postgres version 4.0.1, on a Sun Sparcserver.  The db system
is conventionally setup, except that it's under UID 8 rather than 6.

My first question, with the greatest potential ramifications, has to
do with sets of complex return types in functions.  I defined a
function to reveal all the classes in the system owned by a specific
user:

* define function OwnedClasses (language="postquel",
                                returntype = setof char16)
          arg is (oid)
        as "retrieve (c.relname) from c in pg_class
        where c.relowner= $1 "

So,

* retrieve (x = relname (OwnedClasses(8::oid)))

produces a table of all the (publicly visible) classes owned by the
postgres system.  (The uid would be 6 for postgres installed in the
standard way - it's whatever the UID for the postgres account is on
your system.)

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:

  Query sent to backend is "retrieve (x = OwnedClasses(8::oid)) "
  WARN:Jan 25 10:40:00:Cannot assign complex type to variable x in target list

This happens no matter what variable i use, of course.  I've tried all
sorts of ways to express this query to get the interpreter to accept
it, to no avail.  I'm able to do this, however, with entire complex
classes themselves:

* retrieve (pg_class.all)\g

and with specific fields of a function return (as above, and in the
ref manual section on functions).

I'm beginning to think that complex returns from a function are not
possible.  Is that so, or is there a way to do what i'm trying to do??

My second question is probably as important as the first.  It has to
do with the null value - how do i specify null fields in the
conditions of my queries?  I mean, how do i specifically find records
which have nulls in particular fields?

Another, less vital question has to do with eliciting the class of
functions and where the function definitions "are".  I haven't yet
located where in the system catalogs this information is kept, though
i finished my search yet.  Is function definition text resolvable from
function objects, and if so, how?

My final question concerns the differences between 4.0 and 4.0.1.  One
that is evident and is not consistent with the 4.0 manual is the
ability to specify alternate UIDs for the postgres account.  (The fact
that the installation manual specifically refers to non-existent
instructions for dealing with this problem - the note on pg 4, section
3.3 - led me to look in the code, where it appears that that the
problem no longer exists.)  Is that in fact correct, and are there any
other features available that are not indicated in the manual?  (The
'empty' predicate of class expression would resolve my question about
recognizing nulls, which is part of the reason i ask.)

I hope these are valid questions.  It's been incredibly instructive to
become acquainted with this system, and i'm hoping to be able to use
it to solve some real data handling issues.  Resolution of these
questions will help me decide where to head in my designs.

Thanks for your time and attention!

Ken Manheimer
klm@nist.gov, 301 975-3539
Network File Service, Computer Systems and Communications Division
National Institute of Standards and Technology.