Return-Path: pg_adm@postgres.berkeley.edu
Received: by postgres.Berkeley.EDU (5.61/1.29)
	id AA00886; Mon, 3 Aug 92 09:01:15 -0700
Date: Mon, 3 Aug 92 09:01:15 -0700
Message-Id: <9208031601.AA00886@postgres.Berkeley.EDU>
From: mick <M.J.Ridley@comp.brad.ac.uk>
Subject: Postquel functions in Ver 4
To: postgres@postgres.berkeley.edu
Sender: pg_adm@postgres.berkeley.edu
Cc: N.Hulley@comp.brad.ac.uk

Ive come up with a problem with the modifications
to postquel functions in Version 4.
Now that there is a distinction between postquel functions
that return a setof instances and those that return a single instance
there could be problems with composite objects and unexpected
results when a user doesnt know the difference
between these functions (or forgets how they were defined).

For instance taking the manager function from the manual
and the hobbies and children functions from the reference pages.
I combined these (schema and data shown below) and you can get.


retrieve(EMP.name,hobby=EMP.hobbies.name,kid=EMP.children.name,boss=EMP.manager.name)\g
---------------------------------------------------------
| name        | hobby       | kid         | boss        |
---------------------------------------------------------
| joey        | jamming     | (null)      | (null)      |
---------------------------------------------------------
| jeff        | biking      | (null)      | (null)      |
---------------------------------------------------------
| wei         | basketball  | (null)      | wei         |
---------------------------------------------------------
| mike        | swimming    | matthew     | (null)      |
---------------------------------------------------------
| mike        | philately   | matthew     | (null)      |
---------------------------------------------------------
| mike        | swimming    | calvin      | (null)      |
---------------------------------------------------------
| mike        | philately   | calvin      | (null)      |
---------------------------------------------------------

Specify a manager name (single instance function) in a where clause
and you get the expected reult.

retrieve(EMP.name,hobby=EMP.hobbies.name,kid=EMP.children.name,boss=EMP.manager.name)
where EMP.manager.name="wei"\g
---------------------------------------------------------
| name        | hobby       | kid         | boss        |
---------------------------------------------------------
| wei         | basketball  | (null)      | wei         |
---------------------------------------------------------

However the apparently very similar query where you
specify hobbies name or children name (set of instance function)
in a where clause and you get no instances returned.

retrieve(EMP.name,hobby=EMP.hobbies.name,kid=EMP.children.name,boss=EMP.manager.name)
where EMP.children.name="calvin"\g
---------------------------------------------------------
| name        | hobby       | kid         | boss        |
---------------------------------------------------------

retrieve(EMP.name,hobby=EMP.hobbies.name,kid=EMP.children.name,boss=EMP.manager.name)
where EMP.hobbies.name="basketball"\g
---------------------------------------------------------
| name        | hobby       | kid         | boss        |
---------------------------------------------------------

Ive even got some bizarre results out.

 retrieve(EMP.name,hobby=EMP.hobbies.name) where EMP.hobbies.name = 
"jamming"\g
-----------------------------
| name        | hobby       |
-----------------------------
| jeff        | biking      |
-----------------------------

This distinction between the types of postquel functions may be what
was intended and I know the functions have plural names,
hobbies and children rather than hobby and child, but Im worried that
users could be confused by this situation.

Mick Ridley



**************************************************************




 create EMP (name = char16, salary = float8,
            dept = char16, age = int4)

 create KIDS (name = char16, dad = char16, mom = char16)

append EMP (name = "joey", salary = "100000.01"::float8,
            dept = "toy", age = 24) 
append EMP (name = "jeff", salary = "100000.01"::float8,
            dept = "shoe", age = 23)
append EMP (name = "wei", salary = "100000"::float8,
            dept = "tv", age = 30) 
append EMP (name = "mike", salary = "500000"::float8,
            dept = "appliances", age = 30)

append KIDS (name = "matthew", dad = "mike",
             mom = "teresa")
append KIDS (name = "calvin", dad = "mike",
             mom = "teresa")

create HOBBIES (name = char16, person = char16)

append HOBBIES (name = "biking", person = "jeff" ) 
append HOBBIES (name = "jamming", person = "joey" ) 
append HOBBIES (name = "basketball", person = "wei") 
append HOBBIES (name = "swimming", person = "mike") 
append HOBBIES (name = "philately", person = "mike")

 define function hobbies
    (language = "postquel", returntype = setof HOBBIES)
    arg is (EMP)
    as "retrieve (HOBBIES.all)
          where $1.name = HOBBIES.person"

define function children
    (language = "postquel", returntype = setof KIDS)
    arg is (EMP)
    as "retrieve (KIDS.all)
          where $1.name = KIDS.dad
             or $1.name = KIDS.mom"

 create DEPT (name=char16,manager=char16)

append DEPT(name="tv",manager="wei")

define function manager
      (language = "postquel", returntype = EMP)
      arg is (EMP)
      as "retrieve (E.all) from E in EMP
              where E.name = DEPT.manager
              and DEPT.name = $1.dept"