Functions

1. Object-relational systems allow functions to be defined by users.
2. Functions can be defined in a data manipulation language such as extended SQL.

   Example. Given a document, return the count of the number of authors.

    
   		 create function author-count(one-doc Document)
   
   		 		 return integer as
   
   		 		 select count(author-list)
   
   		 		 from one-doc
   
   

   The function can be used in a query,

    
   		 select name
   
   		 from doc
   
   		 where author-count(doc) > 1
   
   

   Note that, although doc refers to a relation in the from-clause, it is treated as a tuple variable in the where-clause, and can therefore be used as an argument to the author-count function.

3. In general, a select statement can return a collection of values. If the return type of a function is a collection type, the result of the function is the entire collection. However, if the return type is not a collection type, the collection generated by SQL should contain only one tuple. Otherwise, a system may have two choices: flag an error or select an arbitrary one from the collection.
4. Functions can also be defined in a programming language such as C or C++. It can be more efficient and handle more complex computations than that defined using SQL.

   However, since the code needs to be loaded and executed with the database system code, it may carry the risk of

   1. integrity: a bug in the program can corrupt the database internal structures, and
   2. security: it can by-pass the access control functionality of the database system.
5. Embedded SQL is different from C++ code functions: In embedded SQL, the query is passed by the user program to the database system to be run. User-written code never needs to access to the database itself. The operating system thus can protect the database from access by any user process.