head 1.15; access; symbols; locks; strict; comment @.\" @; 1.15 date 92.07.14.23.04.45; author mao; state Exp; branches; next 1.14; 1.14 date 92.07.14.22.55.42; author frew; state Exp; branches; next 1.13; 1.13 date 92.07.14.01.29.19; author frew; state Exp; branches; next 1.12; 1.12 date 92.07.13.22.59.52; author joey; state Exp; branches; next 1.11; 1.11 date 92.07.13.20.45.02; author frew; state Exp; branches; next 1.10; 1.10 date 92.07.07.17.44.44; author frew; state Exp; branches; next 1.9; 1.9 date 92.07.03.23.23.14; author mer; state Exp; branches; next 1.8; 1.8 date 92.06.16.01.04.31; author mer; state Exp; branches; next 1.7; 1.7 date 92.06.16.01.03.12; author mer; state Exp; branches; next 1.6; 1.6 date 92.06.12.00.05.50; author hong; state Exp; branches; next 1.5; 1.5 date 92.06.11.22.42.13; author mer; state Exp; branches; next 1.4; 1.4 date 92.02.28.05.38.55; author mao; state Exp; branches; next 1.3; 1.3 date 92.01.09.21.09.12; author mer; state Exp; branches; next 1.2; 1.2 date 91.12.02.05.16.06; author kemnitz; state Exp; branches; next 1.1; 1.1 date 91.12.02.04.07.14; author kemnitz; state Exp; branches; next ; desc @New Postgres user manual. @ 1.15 log @shovel some honesty into the discussion of arrays. @ text @.\".he '\*(dA'DRAFT'\*(tI' \" comment out in production version .\"======================================================== .\"------------------------------------ .\" /home1/frew/s2k/postgres/manual/RCS/manual.me,v 1.10 1992/07/07 17:44:44 frew Exp .\"------------------------------------ .\" XXX standard disclaimer belongs here.... .\"--------------------------------------------------------------------------- .de cW \" arg3arg1arg2, constant-width arg1 \&\\$3\\fC\\$1\\fP\\$2 .\"\&\\$3\\fC\\s-1\\$1\\s0\\fP\\$2 .. .\"------------------------------------ .de xP \" ip for references .ip \\$1 \\w'[STON90B]'u+2n .. .\"------------------------------------ .de (P \" prologue for constant-width block .ft C .ps -1 .vs -1 .. .\"----------------- .de )P \" epilogue for constant-width block .vs +1 .ps +1 .ft P .. .\"------------------------------------ .de (T \" prologue for constant-width table .(P .in +\\n(biu .. .\"----------------- .de )T \" epilogue for constant-width table .in -\\n(biu .)P .. .\"------------------------------------ .de (C \" begin constant-width list .(l .(P .. .\"----------------- .de )C \" end constant-width list .)P .)l .. .\"--------------------------------------------------------------------------- . ds II \s-1INGRES\s0 . ds PP \s-1POSTGRES\s0 . ds UU \s-1UNIX\s0 . ds PQ \s-1POSTQUEL\s0 . ds LP \s-1LIBPQ\s0 . ds PV \s-14.0\s0 . ds OF \s-1PICASSO\s0 .\"------------------------------------ .ps 11 .vs 13 .\"----------------- .nr pp \n(.s .nr sp \n(.s+1 \" +1-pt section headers .\"----------------- .nr bs 1v .nr ps 0.3v .nr ss 1v .\"----------------- .fo ''\\s+2%\\s0'' \" +2-pt page numbers in center footers .\"----------------- .nr $i .5i \" main text indented .nr so -\n($i \" section headers un-indented .\"------------------------------------ .rm xX \" scratch register .\"------------------------------------ .\"!Gexpand .\"--------------------------------------------------------------------------- .(l C .b \s+3The \*(PP User Manual\s0 .sp 2 .i Edited by Jon Rhein, Greg Kemnitz and The \*(PP Group EECS Dept. University of California, Berkeley .r .)l .sp 3 .\"--------------------------------------------------------------------------- .sh 1 "OVERVIEW" .lp This document is the user manual for the \*(PP database system under development at the University of California, Berkeley. This project, led by Professor Michael Stonebraker, is sponsored by the Defense Advanced Research Projects Agency (DARPA), the Army Research Office (ARO), the National Science Foundation (NSF), and ESL, Inc. .\"------------------------------------ .sh 2 "DISTRIBUTION" .lp This manual describes Version \*(PV of \*(PP. The \*(PP Group has compiled and tested Version \*(PV on the following platforms: .TS center tab(|); c | c l | l . architecture|operating system = DECstation (MIPS)|ULTRIX V4.2 SPARC|SunOS 4.1.2 Sequent Symmetry (386)|DYNIX V3.0 .TE .\"------------------------------------ .sh 2 "PERFORMANCE" .lp Version \*(PV has been tuned modestly. On the Wisconsin benchmark, one should expect performance about twice that of the public domain, University of California version of \*(II, a relational prototype from the late 1970's. .\"------------------------------------ .sh 2 "ACKNOWLEDGEMENTS" .lp \*(PP has been constructed by a team of undergraduate, graduate, and staff programmers. The contributors (in alphabetical order) consisted of James Bell, Jennifer Caetta, Jolly Chen, Ron Choi, Jeffrey Goh, Joey Hellerstein, Wei Hong, Anant Jhingran, Greg Kemnitz, Case Larsen, Jeff Meredith, Michael Olson, Lay-Peng Ong, Spyros Potamianos, Sunita Sarawagi and Cimarron Taylor. .lp For version \*(PV Jeff Meredith served as chief programmer and was responsible for overall coordination of the project and for individually implementing the .q "everything else" portion of the system. .lp The above implementation team contributed significantly to this manual, as did Claire Mosher, Chandra Ghosh, and Jim Frew. .\"--------------------------------------------------------------------------- .sh 1 "INTRODUCTION" .lp Traditional relational DBMSs support a data model consisting of a collection of named relations, each attribute of which has a specific type. In current commercial systems, possible types are floating point numbers, integers, character strings, money, and dates. It is commonly recognized that this model is inadequate for future data processing applications. .lp The relational model succeeded in replacing previous models in part because of its simplicity. The \*(PP data model offers substantial additional power by incorporating the following four additional basic constructs: .(l classes inheritance types functions .)l The \*(PP DBMS has been under construction since 1986. The initial concepts for the system were presented in [STON86] and the initial data model appeared in [ROWE87]. The first rule system that was implemented is discussed in [STON88] and the storage manager concepts are detailed in [STON87]. The first .q demo-ware was operational in 1987, and we released Version 1 of \*(PP to a few external users in June 1989. A critique of version 1 of \*(PP appears in [STON90]. Version 2 followed in June 1990, and it included a new rule system documented in [STON90B]. Version \*(PV, the current version of \*(PP, is about 200,000 lines of code in the C programming language. \*(PP is available free of charge, and is being used by approximately 200 sites around the world at this writing. .\"--------------------------------------------------------------------------- .sh 1 "ORGANIZATION" .lp This manual discusses the \*(PQ query language, including extensions such as user-defined types, operators, and both query language and programming language functions. Arrays of types and functions of an instance are discussed, as well as the \*(PP rule system. This manual concludes with a discussion on adding an operator class to \*(PP for use in access methods. .lp This manual describes the major concepts of the system and attempts to provide an accessible path into using the system. As such, it tries to give examples of the use of the major constructs, so a beginning user does not need to delve immediately into the Reference Manual. .\"--------------------------------------------------------------------------- .sh 1 "WHAT YOU SHOULD READ" .lp This manual is primarily intended to provide a broad overview of the system, as well as to illustrate how programmers would use functions to interact with the \*(PP .q backend. The \*(PP Reference Manual discusses additional aspects of the system, and provides full syntactic descriptions of every \*(PP and \*(PQ command in a format similar to that used in \*(UU .q "man pages." .lp If you are new to \*(PP, you should probably read this manual first, followed by the parts of the \*(PP Reference Manual necessary to build your application. In particular, you should read the section on \*(LP if you intend to build a client application around \*(PP, as this is not discussed at all in this manual. .\"--------------------------------------------------------------------------- .sh 1 "The \*(PQ Query Language" .lp \*(PQ is the query language used for interacting with \*(PP. Here, we give an overview of how to use \*(PQ to access data. In other sections, user extensions to \*(PQ will be discussed. .\"------------------------------------ .sh 2 "Creating a database" .lp Once \*(PP has been installed at your site by following the directions in the release notes, you can create a database named .cW foo using the following command: .(C % createdb foo .)C \*(PP allows you to create any number of databases at a given site and you automatically become the database administrator of the database just created. Database names must have an alphabetic first character and are limited to 16 characters in length. .lp Once you have constructed a database, there are four ways to interact with it: .ip \(bu You can run the \*(PP terminal monitor which allows you to interactively enter, edit, and execute commands in the query language \*(PQ. .ip \(bu You can interact with \*(PP from a C program by using the \*(LP library of subroutine and call facilities. This allows you to submit \*(PQ commands from C and get answers and status messages back to your program. This interface is discussed further in the \*(LP section of the Reference Manual. .ip \(bu You can use the .b "fast path" facility, which allows you to directly execute functions stored in the database. This facility is described in the Reference Manual under .q "Fast Path." .ip \(bu \*(PP is accessible from the \*(OF programming environment. \*(OF is a graphical user interface (GUI) toolkit that allows a user to build sophisticated DBMS-oriented applications. \*(OF is a separate research project described in a collection of reports [WANG88, SCHA90] and is not treated further in this manual. .lp The terminal monitor can be activated for the .cW foo database by typing the command\**: .(f \**You may first need to set the .cW POSTGRESHOME environment variable to the name of the \*(PP root directory at your site, if it is not the default .cW /usr/postgres . If the \*(PP you wish to access is on a remote host, then you will also need to set the .cW PGHOST environment variable to the name of the remote host. .)f .(C % monitor foo .)C (the .cW % '' `` is your \*(UU shell prompt.) You will be greeted by the following message: .(C Welcome to the C POSTGRES terminal monitor Go * .)C The .cW Go indicates the terminal monitor is listening to you and that you can type \*(PQ commands into a workspace maintained by the monitor. The monitor indicates it is listening by typing .cW * as a prompt. Printing the workspace can be performed by typing: .(C * \\p .)C and it can be passed to \*(PP for execution by typing: .(C * \\g .)C If you make a typing mistake, you can invoke the .cW vi text editor by typing: .(C * \\e .)C The workspace will be passed to the editor, and you have the full power of .cW vi to make any necessary changes. For more info on using .cW vi , type .(C % man vi .)C Once you exit .cW vi , your edited query will be in the monitor's query buffer and you can submit it to \*(PP by using the .cW \eg command described above. .lp To get out of the monitor and return to \*(UU, type .(C * \\q .)C and the monitor will respond: .(C I live to serve you. % .)C For a complete collection of monitor commands, see the manual page on .cW monitor in the \*(UU section of the Reference Manual. .lp If you are the database administrator for the database .cW foo , you can destroy it using the following \*(UU command: .(C % destroydb foo .)C Other DBA commands include .cW createuser and .cW destroyuser , which are discussed further in the \*(UU section of the Reference Manual. .\"------------------------------------ .sh 2 "Classes and the Query Language \*(PQ" .\"----------------- .sh 3 "Basic Capabilities" .lp The fundamental notion in \*(PP is that of a .b class, which is a named collection of instances of objects. Each instance has the same collection of named attributes, and each attribute is of a specific type. Furthermore, each instance has an installation-wide unique (never-changing) .b "object identifier" or .b oid . .\"----------------- .sh 3 "Creating a New Class" .lp (In order to try out the following \*(PQ examples, create the .cW foo database as described in the previous section, and start the terminal monitor.) .lp A user can create a new class by specifying the class name, along with all attribute names and their types: .(C * create EMP (name = text, salary = int4, age = int4, dept = char16) \\g * create DEPT (dname = char16, floor = int4, manager = text) \\g .)C The \*(PQ base types used above are a variable-length array of printable characters .cW text ), ( a 4-byte signed integer .cW int4 ), ( and a fixed-length array of 16 characters .cW char16 .)\** ( .(f \**See .q "Built-In Types" in the Reference Manual. .)f Spaces, tabs and newlines may be used freely in \*(PQ queries. .lp So far, the .cW create command looks exactly like the create statement in a traditional relational system. However, we will presently see that classes have properties that are extensions of the relational model, so we use a different word to describe them. .\"----------------- .sh 3 "Populating a Class with Instances" .ds xX \n($1.\n($2.\n($3 .lp To populate a class with instances, one can use the .cW append command: .(C * append EMP (name = "Joe", salary = 1400, age = 40, dept = "shoe") \\g * append EMP (name = "Sam", salary = 1200, age = 29, dept = "toy") \\g * append EMP (name = "Bill", salary = 1600, age = 36, dept = "candy") \\g .)C This will add 3 instances to .cW EMP , one for each .cW append command. .\"----------------- .sh 3 "Querying a Class" .lp The .cW EMP class can be queried with normal selection and projection queries. For example, to find the employees under 35 years of age, one would type: .(C * retrieve (EMP.name) where EMP.age < 35 \\g .)C and the output would be: .(T .TS allbox; l. name Sam .TE .)T Notice that parentheses are required around the .b "target list" of returned attributes (e.g., .cW EMP.name .) .lp \*(PQ allows you to return computations in the target list as long as they are given a name (e.g., .cW result ): .(C * retrieve (result = EMP.salary / EMP.age) where EMP.name = "Bill" \\g .)C .\"----------------- .sh 3 "Redirecting retrieve queries" .lp Any retrieve query can be redirected to a new class in the database, and arbitrary boolean operators .cW and , ( .cW or , .cW not ) are allowed in the qualification of any query: .(C * retrieve into temp (EMP.name) where EMP.age < 35 and EMP.salary > 1000 \\g .)C .\"----------------- .sh 3 "Joins" .lp To find the names of employees which are the same age, one could write: .(C * retrieve (E1.name, E2.name) from E1 in EMP, E2 in EMP where E1.age = E2.age and E1.name != E2.name \\g .)C In this case both E1 and E2 are .b surrogates for an instance of the class .cW EMP , and both range over all instances of the class. A \*(PQ query can contain an arbitrary number of class names and surrogates.\** .(f \**The semantics of such a join are that the qualification is a truth expression defined for the Cartesian product of the classes indicated in the query. For those instances in the Cartesian product for which the qualification is true, \*(PP must compute and return the target list. .)f .\"----------------- .sh 3 "Updates" .lp Updates are accomplished in \*(PQ using the .cW replace command: .(C * replace EMP (salary = E.salary) from E in EMP where EMP.name = "Joe" and E.name = "Sam" \\g .)C This command replaces the salary of Joe by that of Sam. .\"----------------- .sh 3 "Deletions" .lp Deletions are done using the .cW delete command: .(C * delete EMP where EMP.salary > 0 \\g .)C Since all employees have positive salaries, this command will leave the .cW EMP class empty. .\"----------------- .sh 3 "Arrays" .lp \*(PP supports both fixed-length and variable-length one-dimensional arrays. To illustrate their use, we first create a class with an array type. .(C * create SAL_EMP (name = char[], pay_by_quarter = int4[4]) \\g .)C The above query will create a class named .cW SAL_EMP with a variable-length array of .cW text strings .cW name ), ( and an array of 4 .cW int4 integers .cW pay_by_quarter ), ( which represents the employee's salary by quarter. Now we do some .cW append s; note that when appending to a non-character array, we enclose the values within braces and separate them by commas. .(C * append SAL_EMP (name = "bill", pay_by_quarter = "{10000, 10000, 10000, 10000}") \\g * append SAL_EMP (name = "jack", pay_by_quarter = "{10000, 15000, 15000, 15000}") \\g * append SAL_EMP (name = "joe", pay_by_quarter = "{20000, 25000, 25000, 25000}") \\g .)C \*(PP uses the FORTRAN numbering convention for arrays\(emthat is, \*(PP arrays start with array[1] and end with array[n]. .lp Now, we can run some queries on .cW SAL_EMP . This query retrieves the names of the employees whose pay changed in the second quarter: .(C * retrieve (SAL_EMP.name) where SAL_EMP.pay_by_quarter[1] != SAL_EMP.pay_by_quarter[2] \\g .)C This query retrieves the third quarter pay of all employees: .(C * retrieve (SAL_EMP.pay_by_quarter[3]) \\g .)C This query deletes everyone from .cW SAL_EMP whose name begins with the letter .q j. .cW SAL_EMP should now contain only the employee named .q bill : .(C * delete SAL_EMP where SAL_EMP.name[1] = 'j' \\g .)C Let's make sure (note that the attribute .cW all may be used as a shorthand for all attributes of a class): .(C * retrieve (SAL_EMP.all) \\g .TS allbox tab(|); l l. name|pay_by_quarter bill|{10000,10000,10000,10000} .TE .)C \*(PP supports arrays of base and user-defined types, as well as .q "arrays of arrays," as in the following example: .(C * create manager (name = char16, employees = text[]) \\g * append manager (name = "mike", employees = "{"wei", "greg", "jeff"}") \\g * append manager (name = "alice", employees = "{"bill", "joe"}") \\g * append manager (name = "marge", employees = "{"mike", "alice"}") \\g .)C This creates a class .cW manager , and provides a list of employees. .\"------------------------------------ .sh 2 "Advanced \*(PQ" .lp Now we have covered the basics of using \*(PQ to access your data. In this section we will discuss those features of \*(PP which distinguish it from other data managers, such as inheritance and time travel. In the next section we will cover how the user can extend the query language via query language functions and composite objects, as well as additional extensions to \*(PP using user defined types, operators, and programming language functions. .\"----------------- .sh 3 "Inheritance" .lp First, re-populate the .cW EMP class by repeating the .cW append commands in section \*(xX. Then, create a second class .cW STUD_EMP , and populate it as follows: .(C * create STUD_EMP (location = point) inherits (EMP) \\g * append STUD_EMP (name = "Sunita", salary = 1300, age = 41, dept = "electronics", location = "(3, 5)") \\g .)C In this case, an instance of .cW STUD_EMP .b inherits all data fields .cW name , ( .cW salary , .cW age , and .cW dept ) from its parent, .cW EMP . Furthermore, student employees have an extra field, .cW location , that shows their address as a coordinate pair. In \*(PP, a class can inherit from zero or more other classes,\** .(f \**i.e., the inheritance hierarchy is a directed acyclic graph. .)f and a query can reference either all instances of a class or all instances of a class plus all of its descendants. For example, the following query finds the employees over 39: .(C * retrieve (E.name) from E in EMP where E.age > 39 \\g .)C On the other hand, to find the names of all employees, including student employees, over age 40, the query is: .(C * retrieve (E.name) from E in EMP* where E.age > 39 \\g .)C which returns: .(T .TS allbox; l. name Joe Sunita .TE .)T Here the .cW * after .cW EMP indicates that the query should be run over .cW EMP and all classes below .cW EMP in the inheritance hierarchy. .lp Note that .cW location in .cW STUD_EMP is not a traditional relational data type. As we will see later, \*(PP can be customized with an arbitrary number of user-defined data types. .\"----------------- .sh 3 "Time Travel" .lp \*(PP supports the notion of .b "time travel" . This feature allows a user to run historical queries. For example, to find Sam's current salary, one would query: .(C * retrieve (E.salary) from E in EMP["now"] where E.name = "Sam" \\g .)C \*(PP will automatically find the version of Sam's record valid at the correct time and get the appropriate salary. .lp One can also give a time .b range . For example to see all the salaries that Sam has ever earned, one would query: .(C * retrieve (E.salary) from E in EMP["Jan 1 00:00:00 1970 GMT", "now"] where E.name = "Sam" \\g .)C If you have executed all of the examples so far, then the above query returns: .(T .TS allbox; l. salary 1200 1200 .TE .)T There are two salaries for Sam, since he was deleted from and then re-appended to the .cW EMP class. .lp The default beginning of a time range is the origin of the system clock (which just happens to be .cW "Jan 1 00:00:00 1970 GMT" '' `` on \*(UU systems), and the default end is the current time; thus, the above time range can be abbreviated as .cW [,] .'' `` .\"--------------------------------------------------------------------------- .sh 1 "User Extensions to \*(PQ" .lp Here, we will discuss user extensions to the \*(PQ query language, query language functions, composite types, and user defined types, functions and operators. .\"------------------------------------ .sh 2 "User Defined \*(PQ Functions" .lp \*(PQ provides two types of functions: .b "query language functions" (functions written in \*(PQ) and .b "programming language functions" (functions written in a separately-compiled programming language such as C.) In this section we will cover \*(PQ functions; programming language functions will be covered below with the discussion on user-defined types. .lp Any collection of commands in the \*(PQ query language can be packaged together and defined as a function, usually returning either a set of instances or a set of base types. For example, the following function .cW high_pay returns all employees in class .cW EMP whose salaries exceed 50,000: .(C * define function high_pay (language = "postquel", returntype = setof EMP) as retrieve (EMP.all) where EMP.salary > 50000 \\g .)C \*(PQ functions can also have parameters. The following function .cW large_pay allows the threshold salary to be specified as an argument: .(C * define function large_pay (language = "postquel", returntype = setof EMP) arg is (int4) as retrieve (EMP.all) where EMP.salary > $1 \\g .)C In addition to their obvious utility as .q aliases for commonly-used queries, \*(PQ functions are useful for creating composite types, as described below. .\"------------------------------------ .sh 2 "Composite Types" .lp Since \*(PQ functions return instances or sets of instances, they are the mechanism used to assign values to composite types. For example, consider extending the .cW EMP class with a .cW manager field. That is, for each instance of .cW EMP , we want to associate another instance of .cW EMP corresponding to the manager of the first instance. Specifically, we will define a \*(PQ function .cW manager : .(C * 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" \\g .)C The function .cW manager takes an instance as its only argument, so \*(PQ allows referencing into it with the use of the nested dot notation. Whenever such a function is defined over a class, a user can utilize the cascaded dot notation to reference into (i.e. access the fields of) the objects returned by the function. .lp The following query finds all the employees who work for Joe: .(C * retrieve (EMP.name) where EMP.manager.name = "Joe" \\g .)C This is exactly equivalent to: .(C * retrieve (EMP.name) where name(manager(EMP)) = "Joe" \\g .)C Here, we have essentially added an attribute to the .cW EMP class which is of type .cW EMP , i.e. it has a value which is an instance of the class .cW EMP . Since the value of .cW manager has a record-oriented structure, we call it a .b "composite object" . Consequently, the user can think of the function .cW manager as an attribute of .cW EMP and can reference it just like any other attribute, with the following two exceptions. First, one cannot do direct .cW append s\(emthat is, .(C * append emp (emp.manager.name = "Smith") \\g .)C .b won't work. Non-projected retrieves will also be rejected, i.e.: .(C * retrieve (emp.manager) \\g .)C will result in a warning from the \*(PQ language parser. .lp Note that .cW manager is defined as returning a single instance of .cW EMP. We can also write a \*(PQ function that returns sets of instances. For example, consider the function .(C * 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"\\g .)C The .cW children function is defined as returning a set of instances, rather than a single instance. Given the query .(C * retrieve(emp.name, emp.children.name) .)C if the query in the body of the .cW children function returns many instances, the retrieve query will return all of them, in a .q flattened form. If the query in the body of .cW manager returns more than one instance, the .cW manager function will return only one instance, arbitrarily chosen from the set returned by the query in the function's body. See the \*(PP Reference Manual's entry on the .cW "define function" command for further details and examples. .\"--------------------------------------------------------------------------- .sh 1 "User Defined Types, Operators, and Programming Language Functions" .lp The central concept of extending \*(PP lies in \*(PP's ability to .b "dynamically load" a binary object file created by the user. This allows \*(PP to call arbitrary user functions which can be written in a standard programming language. These functions can then be used: .ip \(bu to convert between .b internal (binary) and .b external (character string) representations of user-defined types; .ip \(bu as operators; and .ip \(bu to define ordering for indices on user-defined types. .lp \*(PP's concept of types includes .b built-in types and .b user-defined types. Built-in types are those required by the system to bootstrap itself. User-defined types are those created by the user in the manner described below. There is no intrinsic performance difference between using a system type or user-defined type, other than the overhead due to the complexity of the type itself. .\"------------------------------------ .sh 2 "Internal storage of types" .lp Internally, \*(PP regards a user-defined type as a .q "blob of memory" upon which user-defined functions impose structure and meaning. \*(PP will store and retrieve the data from disk and use user-defined functions to input, process, and output the data. .\"------------------------------------ .sh 2 "Functions needed for a user-defined type" .lp A completely defined user type requires the following user-defined functions: .ip \(bu .b input and .b output functions for the type: These functions determine how the type appears in strings (for input by the user and output to the user) and how the type is organized in memory. These at least are necessary to define the type. .ip \(bu .b operator functions for the type: These functions define the meanings of .q equal, .q "less than," .q "greater than," etc., for your type. .\"------------------------------------ .sh 2 "An Example User Defined Type" .lp In this discussion, we will be defining a .cW circle type, using functions written in the C programming language. .\"----------------- .sh 3 "Data structures for our type" .lp Before we do anything, we have to decide on what a circle looks like, both in string format and internally in memory. Circles have a center and a radius, so a reasonable string representation of a circle would be an ordered triple: .(l (center_x, center_y, radius) .)l where each element is a real number with arbitrary units, e.g.: .(C (5.0, 10.3, 3) .)C This is what the input to the circle input function looks like, and what the output from the circle output function looks like. .lp Now we have to come up with an internal representation for a circle in memory. The following declarations are legal and reasonable given the format we chose above: .(C typedef struct { double x, y; } POINT; typedef struct { POINT center; double r; } CIRCLE; .)C Memory containing values of type .cW CIRCLE will be written to disk and read from disk, so .cW CIRCLE must be both .b complete and .b contiguous ; that is, it cannot contain any pointers. The alternate declaration .(C typedef struct { POINT *center double r; } CIRCLE; .)C will .b NOT work, because only the address stored in .cW center would be written to disk, not the .cW POINT structure that .cW center presumably points to. \*(PP cannot detect this kind of coding error; you must guard against it yourself. .\"----------------- .sh 3 "Defining the input and output functions for our type" .lp Suppose in defining our type .q circle, we have a C source file called .cW circle.c , and a corresponding object code file .cW /usr/postgres/tutorial/circle.o . (All functions related to our .cW circle type have to be in the same object file.) For the sake of argument, suppose we our platform is a DECstation, where sizeof(double) is 8 bytes (this will be important later). .lp We will create source file .cW circle.c , containing C source code for the functions that support our .cW CIRCLE type. .cW circle.c contains three functions: .ip \(bu .cW circle_in , which is the input function for circles. It takes a string as an argument and returns a pointer to a .cW CIRCLE . .ip \(bu .cW circle_out , which is the output function for circles. It is takes a pointer to s .cW CIRCLE as input and returns a string. .ip The return value of .cW circle_in must be a legal argument to .cW circle_out , and vice versa. .ip \(bu .cW eq_area_circle , which is the equality function for circles. For the purposes of this discussion, circles are equal if their areas are equal. .lp The contents of .cW circle.c are: .(C #include #include #include #include "tmp/c.h" /* (always) */ #include "utils/geo-decls.h" /* for POINT declaration */ #include "utils/palloc.h" /* for palloc() declaration */ typedef struct { POINT center; double radius; } CIRCLE; #define LDELIM '(' #define RDELIM ')' #define NARGS 3 CIRCLE * circle_in(str) char *str; { char *p, *coord[NARGS]; int i; CIRCLE *result; if (str == NULL) return(NULL); for (i = 0, p = str; *p && i < NARGS && *p != RDELIM; p++) { if (*p == ',' || (*p == LDELIM && !i)) coord[i++] = p + 1; } if (i < NARGS - 1) return(NULL); result = (CIRCLE *) palloc(sizeof(CIRCLE)); result->center.x = atof(coord[0]); result->center.y = atof(coord[1]); result->radius = atof(coord[2]); return(result); } char * circle_out(circle) CIRCLE *circle; { char *result; if (circle == NULL) return(NULL); result = (char *) palloc(60); sprintf(result, "(%g, %g, %g)", circle->center.x, circle->center.y, circle->radius); return(result); } int eq_area_circle(circle1, circle2) CIRCLE *circle1, *circle2; { return(circle1->radius == circle2->radius); } .)C .lp Now that we have written these functions and compiled the source file,\** .(f \**You will need to supply an option like .cW -I$POSTGRESHOME/src/lib/H to your C compiler so it can find the \*(PP .cW .h '' `` files. Also, various platform-specific compiler options may be required to support \*(PP dynamic linking (for example, the DECstation ULTRIX compiler requires the .cW "-G0" '' `` option.) See .q "define function" in the Reference Manual for details. .)f we have to let \*(PP know that they exist. First, we run the following queries to define the input and output functions. These functions must be defined .b before we define the type. \*(PP will notify you that return type circle is not defined yet, but this is OK\**: .(f \**By default, user-defined C functions use addresses instead of values for all but .q small (<= 4-byte) argument and return types, so we can use the \*(PQ type .cW char16 as a placeholder for the C type .cW "char *" . .)f .(C * define function circle_in (language = "c", returntype = circle) arg is (char16) as "/usr/postgres/tutorial/circle.o" \\g * define function circle_out (language = "c", returntype = char16) arg is (circle) as "/usr/postgres/tutorial/circle.o" \\g .)C Note that the full pathname of the object code file must be specified, so you would change .cW /usr/postgres/tutorial to whatever is appropriate for your installation. .lp Now we can define the .cW circle type: .(C * define type circle (internallength = 24, input = circle_in, output = circle_out) \\g .)C where .cW internallength is the size of the .cW CIRCLE structure in bytes. For circles, the type members are three .cW double s, which on most platforms are 8 bytes each, with no additional alignment constraints. However, when defining your own types, you should .b not make assumptions about structure sizes, but instead write a test program that does a .(C printf("size is %d\en", sizeof (MYTYPE)); .)C on your type. .lp If .cW internallength is defined incorrectly, you will encounter strange errors which may crash the data manager itself. If this were to happen with our .cW CIRCLE type, we would have to do a .(C * remove type circle \\g .)C and then redefine the .cW circle type correctly. Note that we would .b not have to redefine our functions, since their behavior would not have changed. .\"----------------- .sh 3 "Defining an operator for our type" .lp Now that we have finished defining the .cW circle type, we can .cW create classes with circles in them, .cW append records to them with circles defined, and .cW retrieve the values of the entire list of records. But we can do nothing else until we have some circle operators. To do this, we make use of the concept of .b "operator overloading" , and in this case we will set the \*(PP equality operator .cW = '' `` to work for circles. First we have to tell \*(PP that our circle equality function exists: .(C * define function eq_area_circle (language = "c", returntype = bool) arg is (circle, circle) as "/usr/postgres/tutorial/circle.o" \\g .)C We will now bind this function to the equality symbol with the following query: .(C * define operator = (arg1 = circle, arg2 = circle, procedure = eq_area_circle) \\g .)C .\"----------------- .sh 3 "Using our type" .lp Let's create a class .cW tutorial that contains a .cW circle attribute, and run some queries against it: .(C * create tutorial(a = circle) \\g * append tutorial (a = "(1.0, 1.0, 10.0)"::circle) \\g * append tutorial (a = "(2.0, 2.0, 5.0)"::circle) \\g * append tutorial (a = "(0.0, 1.8, 10.0)"::circle) \\g * retrieve (tutorial.all) where tutorial.a = "(0.0, 0.0, 10.0)"::circle \\g .)C which returns: .(T .TS allbox; l. a (1.0, 1.0, 10.0) (0.0, 1.8, 10.0) .TE .)T Recall that we defined circles as being equal if their areas were equal. .lp Other operators (less than, greater than, etc.) can be defined in a similar way. Note that the .cW = '' `` symbol will still work for other types\(emit has merely had a new type added to the list of types it works on. Any string of .q "punctuation characters" other than brackets, braces, or parentheses can be used in defining an operator. .\"------------------------------------ .sh 2 "Additional info on creating a user-defined function" .\"----------------- .sh 3 "Use palloc and not malloc" .lp In order for \*(PP to correctly manage memory associated with processing your type, you must use the memory allocator .cW palloc and avoid standard \*(UU memory managers such as .cW malloc . If you do not, \*(PP will chew up ever increasing amounts of memory. .cW palloc has the same arguments as .cW malloc , that is .(C char *palloc(size) unsigned long size; .)C To free memory allocated with .cW palloc , use .cW pfree , which is analogous to the \*(UU library function .cW free : .(C void pfree(ptr) char *ptr; .)C .\"----------------- .sh 3 "Re-loading user functions" .lp In the process of creating a user-defined type, you may find it necessary to re-load a function in the course of debugging. This is .b not done automatically when you edit or re-compile the file, but .b is done if you quit and restart the data manager. .lp We would re-load our example functions by using the following command: .(C * load "/usr/postgres/tutorial/circle.o" \\g .)C .\"----------------- .sh 3 "Writing a Function of an Instance" .lp We've already discussed user functions which take \*(PP base or user defined types as arguments; in this section, we will discuss inheritable C functions or methods. .lp C language methods are useful particularly when we want to make a function .b inheritable ; that is, to have the function process every instance in an inheritance hierarchy of classes. .lp In using a function of an instance in qualifying an instance, \*(PP defines the .q "current instance" to be the instance being qualified at the moment your function is called. The instance itself will be passed in your function's parameter list as an opaque structure of type TUPLE, and you will use \*(PP library routines to access the data in the object as described below.\** .(f \**In \*(PP \*(PV, .cW TUPLE is defined as .cW "void *" . .)f .cW .lp Suppose we want to write a function to answer the query .(C * retrieve (EMP.all) where overpaid(EMP) \\g .)C In the query above, a reasonable .cW overpaid function might be: .(C bool overpaid(t) TUPLE t; /* the current instance */ { extern char *GetAttributeByName(); short salary, seniority, performance; salary = (short) GetAttributeByName(t, "salary"); seniority = (short) GetAttributeByName(t, "seniority"); performance = (short) GetAttributeByName(t, "performance"); return (salary > (seniority * performance)); } .)C .cW GetAttributeByName is the \*(PP system function that returns attributes out of the current instance. It has two arguments: the argument of type TUPLE passed into the function, and the name of the desired attribute. .cW GetAttributeByName will align data properly so you can cast its return value to the desired type. For example, if you have an attribute .cW name which is of the \*(PQ type .cW char16 , the .cW GetAttributeByName call would look like: .(C char *str; \&... str = (char *) GetAttributeByName(t, "name") .)C .lp To let \*(PP know about the .cW overpaid function, do: .(C * define function overpaid (language = "c", returntype = bool) arg is (EMP) as "/usr/postgres/tutorial/overpaid.o" \\g .)C .lp You can have additional complex, base or user-defined types as arguments to the inheritable function. Thus, .(C * retrieve (EMP.all) where overpaid2(EMP, DEPT, "bill", 8) \\g .)C could be written, and .cW overpaid2 would be declared: .(C bool overpaid2(emp, dept, name, number) TUPLE emp, dept; char *name; long number; .)C .\"------------------------------------ .sh 2 "Arrays of types" .lp As discussed above, \*(PP fully supports arrays of base types. Additionally, \*(PP supports arrays of user-defined types as well. When you define a type, \*(PP .b automatically provides support for arrays of that type. .\"----------------- .sh 3 "Arrays of user-defined types" .lp Using the .q circle example discussed above, we will create a class containing an array of circles: .(C * create circles (list = circle[]) \\g .)C and do some appends .(C * append circles (list = "{"(1.0, 1.0, 5.0)", "(2.0, 2.0, 10.0)"}") \\g * append circles (list = "{"(2.0, 3.0, 15.0)", "(2.0, 2.0, 10.0)"}") \\g * append circles (list = "{"(2.0, 3.0, 4.0)"}") \\g .)C We can now run queries like: .(C * retrieve (circles.list[1]) \\g .)C which returns the first element of each .cW list : .(T .TS allbox; l. list (1, 1, 5) (2, 3, 4) .TE .)T and .(C * retrieve (circles.all) where circles.list[1] = "(0.0, 0.0, 4.0)" \\g .)C which returns: .(T .TS allbox; l. list {"(2, 3, 4)"} .TE .)T Note the .cW {} s, indicating that an array has been retrieved, as opposed to a single element. .\"----------------- .sh 3 "Defining a new array type" .lp An array may be defined as an element of a class, as shown above, or it may be defined as a type in and of itself. This is useful for defining .b "arrays of arrays" . .lp The special built-in functions .cW array_in and .cW array_out are used by \*(PP to input and output arrays of any existing type. Here, we define an array of integers: .(C * define type int_array (element = int4, internallength = variable, input = array_in, output = array_out) \\g .)C The .cW element parameter indicates that this is an array, and setting .cW internallength to .cW variable indicates that the array is a variable-length attribute.\** .(f \**Note that any type using .cW array_in and .cW array_out .b must be variable-length. .)f .lp We can use our type defined above to create an array of integer arrays: .(C * define type int_arrays (element = int_array, internallength = variable, input = array_in, output = array_out) \\g * create stuff (a = int_arrays) \\g * append stuff (a = "{{1, 2, 3} , {4, 5}, {6, 7, 8}}") \\g * append stuff (a = "{{88, 99, 3}}") \\g * append stuff (a = "{{5, 4, 3} , {2, 2}}") \\g * retrieve (stuff.a[1]) where stuff.a[1][1] < stuff.a[1][2] \\g * retrieve (stuff.a) where stuff.a[3][1] < stuff.a[1][2] \\g * retrieve (s.all) from s in stuff where s.a[2][2] = stuff.a[1][1] \\g .)C We can also define operators for equality, less than, greater than, etc. which operate on our new array type as necessary. .\"----------------- .sh 3 "Creating an array type from scratch" .lp There are many situations in which the above scheme for creating an array type is inappropriate, particularly when it is necessary to define a fixed-length array. In this section, we will create an array of four longs called .cW quarterly , and a variable-length array of longs called .cW stats .\** .(f We assume .cW sizeof(long) == 4. .)f .lp The only special things we need to know when writing the input and output functions for .cW quarterly is that \*(PP will pass a .q simple (i.e. fixed-length) array of .cW long s to the output function and expect a simple array of .cW long s in return from the input function. A simple array suitable for .cW quarterly can be declared: .(C long quarterly[4]; .)C For the variable-length array .cW stats , the situation is a little more complicated. Because \*(PP will not know in advance how big the array is, \*(PP will expect the length of the array (in bytes) to be encoded in the first four bytes of the memory which contains the array. The expected structure is: .(C typedef struct { long length; unsigned char bytes[1]; /* Force contiguity */ } VAR_LEN_ATTR; .)C The input function for the .cW stats array will look something like: .(C VAR_LEN_ATTR * stats_in(s) char s; { VAR_LEN_ATTR *stats; long array_size, *arrayp, nbytes; /* * nbytes is the total number of bytes in stats, * INCLUDING the byte count at the beginning */ nbytes = array_size * sizeof(long) + sizeof(long); stats = (VAR_LEN_ATTR *) palloc(nbytes); stats->length = nbytes; arrayp = &(stats->bytes[0]); /* * put code here that loads interesting stuff into * arrayp[0] .. arrayp[array_size] */ return(stats); } .)C The output function for .cW stats will get the same .cW VAR_LEN_ATTR structure. .lp Now, assuming the functions are in .cW /usr/postgres/tutorial/stats.c and .cW /usr/postgres/tutorial/quarterly.c , we can define our two arrays. First we will define the fixed-size array .cW quarterly .\** .(f .cW internallength == 16 follows from our assumption about .cW sizeof(long) . .)f .(C * define function quarterly_in (language = "c", returntype = quarterly) arg is (char16) as "/usr/postgres/tutorial/quarterly.o" \\g * define function quarterly_out (language = "c", returntype = char16) arg is (quarterly) as "/usr/postgres/tutorial/quarterly.o" \\g * define type quarterly (element = int4, internallength = 16, input = quarterly_in, output = quarterly_out) \\g .)C Now we define the .cW stats array: .(C * define function stats_in (language = "c", returntype = stats) arg is (char16) as "/usr/postgres/tutorial/stats.o" \\g * define function stats_out (language = "c", returntype = char16) arg is (stats) as "/usr/postgres/tutorial/stats.o" \\g * define type stats (element = int4, internallength = variable, input = stats_in, output = stats_out) \\g .)C Now we can run some queries: .(C * create test (a = quarterly, b = stats) \\g * append test (a = "1 2 3 4"::quarterly, b = "5 6 7"::stats) \\g * append test (a = "1 3 2 4"::quarterly, b = "6 4"::stats) \\g * append test (a = "7 11 6 9"::quarterly, b = "1 2"::stats) \\g * retrieve (test.all) where test.a[4] = test.b[2] \\g .)C which returns: .(T .TS tab(|) allbox; l l. a|b 1324|64 .TE .)T .b NOTE that when you use your own functions to input and output array types, .b "your function" will define how to parse the external (string) representation. The braces notation is only a convention used by .cW array_in and .cW array_out and is .b not part of the formal \*(PQ definition. .\"------------------------------------ .sh 2 "Large Object types" .lp The types discussed to this point are all .b small objects\(emthat is, they are smaller than 8 Kbytes\** .(f \**8 * 1,024 == 8,192 bytes .)f in size. If you require a larger type for something like a document retrieval system or for storing bitmaps, you will need to use the \*(PP .b "large object" interface. The interface to large objects is quite similar to the \*(UU file system interface. The particulars are detailed in Section 7 of the \*(PP Reference Manual, which you should have available to consult as you read the following. .\"----------------- .sh 3 "Defining a large object" .lp Just like any other type, a large object type requires input and output functions. For the purposes of this discussion, we assume that two functions, .cW large_in and .cW large_out have been written using the large object interface, and that the compiled functions are in .cW /usr/postgres/tutorial/large.o . We also presume that we are using the .q "file as an ADT" interface for large objects discussed in the Reference Manual. .lp We define a large object which could be used for storing map data: .(C * define function large_in (language = "c", returntype = map) arg is (char16) as "/usr/postgres/tutorial/large.o" \\g * define function large_out (language = "c", returntype = char16) arg is (map) as "/usr/postgres/tutorial/large.o" \\g * define type map (internallength = variable, input = large_in, output = large_out) \\g .)C Note that large objects are .b always variable-length. .lp Now we can use our .cW map object: .(C * create maps (name = text, a = map) \\g * append maps (name = "earth", a = "/usr/postgres/maps/earth") \\g * append maps (name = "moon", a = "/usr/postgres/maps/moon") \\g .)C Notice that the above queries are identical in syntax to those we have been using all along to define types and such; the fact that this type is a large object is completely hidden in the large object interface and \*(PP storage manager. .\"----------------- .sh 3 "Writing functions and operators for large object types" .lp Like any other \*(PP type, you can define functions and operators for large object types. The only caveat is that, like any other functions which process a large object, they .b must use the large object interface described in Section 7 of the \*(PP Reference Manual. Possible queries which involve functions on large objects could include .(C * retrieve (emp.name) where beard(emp.picture) = "red" \\g * retrieve (mountain.name) where height(mountain.topomap) > 10000 \\g .)C Because all functionality is available to large objects, .b any aspect of \*(PP is available for use with them, including index access methods, if the appropriate operator classes have been defined. Operator classes for index access methods will be discussed later in this manual. .\"--------------------------------------------------------------------------- .sh 1 "The \*(PP Rule System" .lp The discussion in this section is intended to provide an overview of the \*(PP rule system and point the user at helpful references and examples. \*(PP actually has two rule systems, the .b instance-level rule system and the .b "query rewrite" rule system. .\"------------------------------------ .sh 2 "The Instance-level Rule System" .lp The instance-level rule system uses markers placed in each instance in a class to .q trigger rules. Examples of the instance-level rule system are explained and illustrated in .cW $POSTGRESHOME/demo , which is included with the \*(PP distribution. Additional discussion of the instance-level rule system can be found in the Reference Manual under .cW "define rule" . The theoretical foundations of the \*(PP rule system can be found in [STON90]. .\"------------------------------------ .sh 2 "The Query Rewrite Rule System" .lp The query rewrite rule system modifies queries to take rules into consideration, and then passes the modified query to the query optimizer for execution. It is very powerful, and can be used for many things such as query language procedures, views, and versions. Examples and discussion can be found in the demo in .cW $POSTGRESHOME/video , and further discussion is in the Reference Manual under .cW "define rule" . The power of this rule system is discussed in [ONG90] and [STON90]. .\"------------------------------------ .sh 2 "When to use either?" .lp Since each rule system is architected quite differently, they work best in different situations. The query rewrite system is best when rules affect most of the instances in a class, while the instance-level system is best when a rule affects only a few instances. .\"--------------------------------------------------------------------------- .sh 1 "Administering \*(PP" .lp In this section, we will discuss aspects of \*(PP of interest to those making extensive use of \*(PP, or who are the database administrator for a group of \*(PP users. .\"------------------------------------ .sh 2 "User administration" .lp The .cW createuser and .cW destroyuser enable and disable access to \*(PP by specific users on the host system. Please read the descriptions of these commands in the Reference Manual for specifics on their use. .\"------------------------------------ .sh 2 "Moving database directories out of $POSTGRESHOME/data/base" .lp By default, all \*(PP databases are stored in separate subdirectories under .cW $POSTGRESHOME/data/base/ .\** .(f \**Data for certain classes may stored elsewhere if a non-standard storage manager was specified when they were created. .)f To move a particular data base to an alternate directory (e.g., on a filesystem with more free space), do the following: .ip \(bu Create the database (if it doesn't already exist) using the .b createdb command. In the following steps we will assume the database is named .cW foo . .ip \(bu Copy the directory .cW $POSTGRESHOME/data/base/foo and it contents to its ultimate destination. It should still be owned by the .cW postgres user. .ip \(bu Remove the directory .cW $POSTGRESHOME/data/base/foo . .ip \(bu Make a symbolic link in .cW $POSTGRESHOME/data/base to the new directory. .\"------------------------------------ .sh 2 "Troubleshooting \*(PP" .lp Occasionally, \*(PP will fail with cryptic error messages that are due to relatively simple problems. The following are a list of \*(PP error messages and the likely fix. These messages are ones you would likely see in the monitor program. .(C Message: semget: No space left on device Explanation and Likely Fix: .)C Either the kernel has not been configured for System V shared memory, or some other program is using it up. On most machines, the \*(UU command .cW ipcs will show shared memory and semaphore usage. .lp To delete all shared memory and semaphores (may be necessary if a backend fails), run the .cW ipcclean command. Note, however, that .cW ipcclean deletes .b all semaphores belonging to the user running it, so the user should be certain that none of his/her non-\*(PP processes are using semaphores before running this command. .(C Message: Unable to get shared buffers Explanation and Likely Fix: .)C This message means that a \*(PP backend was expecting shared memory to be available and it was not. Usually this is due to .cW ipcclean being run while a .cW postmaster was also running. .(C Message: Can't connect to the backend (...) Explanation and Likely Fix: .)C This message means that you are running a \*(LP application but it could not link up with a .cW postmaster . If you see this error message, you should see if a .cW postmaster is truly running. If one is running, the problem is likely related to your network. .\"--------------------------------------------------------------------------- .sh 1 "REFERENCES" .\"------------------------------------ .xP [ONG90] Ong, L. and Goh, J., ``A Unified Framework for Version Modeling Using Production Rules in a Database System," Electronics Research Laboratory, University of California, Berkeley, ERL Memo M90/33, April 1990. .\"------------------------------------ .xP [ROWE87] Rowe, L. and Stonebraker, M., ``The POSTGRES Data Model,'' Proc. 1987 VLDB Conference, Brighton, England, Sept. 1987. .\"------------------------------------ .xP [SCHA90] Shapiro, L., ``Join Processing in Database Systems with Large Main Memories,'' ACM-TODS, Sept. 1986. .\"------------------------------------ .xP [STON86] (missing) .\"------------------------------------ .xP [STON87] Stonebraker, M., ``The POSTGRES Storage System,'' Proc. 1987 VLDB Conference, Brighton, England, Sept. 1987. .\"------------------------------------ .xP [STON88] (missing) .\"------------------------------------ .xP [STON90] Stonebraker, M. et. al., ``On Rules, Procedures, Caching and Views in Database Systems,'' Proc. 1990 ACM-SIGMOD Conference on Management of Data, Atlantic City, N.J., June 1990. .\"------------------------------------ .xP [STON90B] (missing) .\"------------------------------------ .xP [WANG88] (missing) .\"--------------------------------------------------------------------------- .uh "APPENDIX: User defined types and indices" .lp In this section, we will discuss how to extend \*(PP to use a user-defined type and associated functions with existing access methods. This way, you can define a BTREE or RTREE index on your own type. To do this, we will discuss how to define a new operator class in \*(PP for use with an existing access method. .lp Our example will be to add a new operator class to the BTREE access method. The new operator class will sort integers in ascending absolute value order. This tutorial will describe how to define the operator class. If you work the example, you will be able to define and use indices that sort integer keys by absolute value. .lp There are several \*(PP system classes that are important in understanding how the access methods work. These will be discussed, and then a sample procedure for adding a new set of operators to an existing access method will be shown as an example. .lp The .cW pg_am class contains one instance for every user defined access method. Support for the HEAP access method is built into \*(PP, but every other access method is described here. The schema is .TS center tab(|); lf(C)|l. amname|name of the access method _ amowner|object id of the owner's instance in pg_user _ amkind|not used at present, but set to 'o' as a place holder _ amstrategies|number of strategies for this access method (see below) _ amsupport|number of support routines for this access method (see below) _ am*|T{ procedure identifiers for interface routines to the access method. For example, .cW regproc ids for opening, closing, and getting instances from the access method appear here. T} .TE The object ID of the instance in .cW pg_am is used as a foreign key in lots of other classes. For BTREES, this object ID is 403. You don't need to add a new instance to this class; all you're interested in is the object ID of the access method instance you want to extend: .(C * retrieve (pg_am.oid) where pg_am.amname = "btree" \\g .TS allbox; l. oid 403 .TE .)C The .cW amstrategies attribute exists to standardize comparisons across data types. For example, BTREES impose a strict ordering on keys, less to greater. Since \*(PP allows the user to define operators, \*(PP cannot in general look at the name of an operator (eg, .cW > , .cW < ) and tell what kind of comparison it is. In fact, some access methods (like rtrees) don't impose a less-to-greater ordering, but some other ordering, like containment. \*(PP needs some consistent way of taking a scan qualification, looking at the operator, deciding if a usable index exists, and rewriting the query qualification in order to improve access speeds. This implies that \*(PP needs to know, for example, that .cW <= and .cW > partition a BTREE. Strategies is the way that we do this. .lp Defining a new set of strategies is beyond the scope of this discussion, but how the BTREE strategies work will be explained, since you'll need to know that to add a new operator class. In the .cW pg_am class, the .cW amstrategies attribute is the number of strategies defined for this access method. For BTREES, this number is 5. These strategies correspond to .TS center tab(|); l|l. less than|1 _ less than or equal|2 _ equal|3 _ greater than or equal|4 _ greater than|5 .TE The idea is that you'll add procedures corresponding to the comparisons above to the .cW pg_amop relation (see below). The access method code can use these numbers, regardless of data type, to figure out how to partition the BTREE, compute selectivity, and so on. Don't worry about the details of adding procedures yet; just understand that there's a set of these for .cW int2 , .cW int4 , .cW oid , and every other data type on which a BTREE can operate. .lp Strategies are used by all of the \*(PP access methods. Some access methods require other support routines in order to work. For example, the BTREE access method must be able to compare two keys and determine whether one is greater than, equal to, or less than the other. Similarly, the RTREE access method must be able to compute intersections, unions, and sizes of rectangles. These operations do not correspond to user qualifications in \*(PQ queries; they are administrative routines used by the access methods, internally. .lp In order to manage diverse support routines consistently across all \*(PP access methods, .cW pg_am includes a field called .cW amsupport . This field records the number of support routines used by an access method. For BTREES, this number is one\(emthe routine to take two keys and return \(mi\^1, 0, or \(pl\^1, depending on whether the first key is less than, equal to, or greater than the second. .lp The .cW amstrategies entry in .cW pg_am is just the number of strategies defined for the access method in question. The procedures for less than, less equal, and so on don't appear in .cW pg_am . Similarly, .cW amsupport is just the number of support routines required by the access method. The actual routines are listed elsewhere. .lp The next class of interest is .cW pg_opclass. This class exists only to associate a name with an .cW oid . In .cW pg_amop , every BTREE operator class has a set of procedures, one through five, above. Some existing opclasses are .cW int2_ops , .cW int4_ops , and .cW oid_ops . You need to add an instance with your opclass name (for example, .cW int4_abs_ops ) to .cW pg_opclass . The .cW oid of this instance is a foreign key in other classes. .(C * append pg_opclass (opcname = "int4_abs_ops") \\g * retrieve (cl.oid, cl.opcname) from cl in pg_opclass where cl.opcname = "int4_abs_ops" \\g .TS tab(|) allbox; l l. oid|opcname 17314|int4_abs_ops .TE .)C .b NOTE: The .cW oid for your .cW pg_opclass instance .b "may be different" ! You should substitute your value for 17314 wherever it appears in this discussion. .lp So now we have an access method and an operator class. We still need a set of operators; the procedure for defining operators was discussed earlier in this manual. For the .cW int4_abs_ops operator class on BTREES, the operators we require are: .(l absolute value less-than absolute value less-than-or-equal absolute value equal absolute value greater-than-or-equal absolute value greater-than .)l Suppose the code that implements the functions defined is stored in the file .cW /usr/postgres/tutorial/int4_abs.c . The code is .(C /* * int4_abs.c -- absolute value comparison functions * for int4 data */ #include "tmp/c.h" #define ABS(a) a = ((a < 0) ? -a : a) bool int4_abs_lt(a, b) int32 a, b; { ABS(a); ABS(b); return (a < b); } bool int4_abs_le(a, b) int32 a, b; { ABS(a); ABS(b); return (a <= b); } bool int4_abs_eq(a, b) int32 a, b; { ABS(a); ABS(b); return (a == b); } bool int4_abs_ge(a, b) int32 a, b; { ABS(a); ABS(b); return (a >= b); } bool int4_abs_gt(a, b) int32 a, b; { ABS(a); ABS(b); return (a > b); } .)C There are a couple of important things that are happening below. First, note that operators for less, less equal, equal, greater equal, and greater for .cW int4 are being defined. All of these operators are already defined for .cW int4 under the names .cW < , .cW <= , .cW = , .cW >= , and .cW > . The new operators behave differently, of course. In order to guarantee that \*(PP uses these new operators rather than the old ones, they need to be named differently from the old ones. This is a key point: you can overload operators in \*(PP, but only if the operator isn't already defined for the argument types. .lp That is, if you have .cW < defined for .cW int4 , ( .cW int4 ), you can't define it again. \*(PP .b doesn't check this when you define your operator, so be careful. To avoid this problem, odd names will be used for the operators. If you get this wrong, the access methods are likely to crash when you try to do scans. .lp The other important point is that all the functions return .b boolean values; the access methods rely on this fact. .(C * define function int4_abs_lt (language = "c", returntype = bool) arg is (int4, int4) as "/usr/postgres/tutorial/int4_abs.o" \\g * define function int4_abs_le (language = "c", returntype = bool) arg is (int4, int4) as "/usr/postgres/tutorial/int4_abs.o" \\g * define function int4_abs_eq (language = "c", returntype = bool) arg is (int4, int4) as "/usr/postgres/tutorial/int4_abs.o" \\g * define function int4_abs_ge (language = "c", returntype = bool) arg is (int4, int4) as "/usr/postgres/tutorial/int4_abs.o" \\g * define function int4_abs_gt (language = "c", returntype = bool) arg is (int4, int4) as "/usr/postgres/tutorial/int4_abs.o" \\g .)C Now define the operators that use them. As noted, the operator names must be unique for two .cW int4 operands. You can do a query on .cW pg_operator : .(C * retrieve (pg_operator.all) \\g .)C to see if your name is taken for the types you want. The important things here are the procedure (which are the C functions defined above) and the restriction and join selectivity functions. You should just use the ones used below\(emnote that there are different such functions for the less-than, equal, and greater-than cases. These .b must be supplied, or the access method will die when it tries to use the operator. You should copy the names for .cW restrict and .cW join , but use the procedure names you defined in the last step. .(C * define operator <<& (arg1 = int4, arg2 = int4, procedure=int4_abs_lt, associativity = left, restrict = intltsel, join = intltjoinsel) \\g * define operator <=& (arg1 = int4, arg2 = int4, procedure = int4_abs_le, associativity = left, restrict = intltsel, join = intltjoinsel) \\g * define operator ==& (arg1 = int4, arg2 = int4, procedure = int4_abs_eq, associativity = left, restrict = eqsel, join = eqjoinsel) \\g * define operator >=& (arg1 = int4, arg2 = int4, procedure = int4_abs_ge, associativity = left, restrict = intgtsel, join = intgtjoinsel) \\g * define operator >>& (arg1 = int4, arg2 = int4, procedure = int4_abs_gt, associativity = left, restrict = intgtsel, join = intgtjoinsel) \\g .)C Notice that five operators corresponding to less, less equal, equal, greater, and greater equal are defined. .lp We're just about finished. the last thing we need to do is to update the .cW pg_amop relation. To do this, we need the following attributes: .TS center tab(|); lf(C)|l. amopid|T{ the .cW oid of the .cW pg_am instance for BTREE (== 400, see above) T} _ amopclaid|T{ the .cW oid of the .cW pg_opclass instance for .cW int4_abs_ops (== whatever you got instead of 17314, see above) T} _ amopopr|T{ the .cW oid s of the operators for the opclass (which we'll get in just a minute) T} _ T{ amopselect, .br amopnpages T}|cost functions. .TE The cost functions are used by the query optimizer to decide whether or not to use a given index in a scan. Fortunately, these already exist. The two functions we'll use are .cW btreesel, which estimates the selectivity of the btree, and .cW btreenpage, which estimates the number of pages a search will touch in the tree. .lp So we need the .cW oid s of the operators we just defined. We'll look up the names of all the operators that take two .cW int4 s, and pick ours out: .(C * retrieve (o.oid, o.oprname) from o in pg_operator, t in pg_type where o.oprleft = t.oid and o.oprright = t.oid and t.typname = "int4" \\g .)C which returns: .(T .TS tab(|) allbox; l l. oid|oprname 96|\\= 97|< 514|* 518|!= 521|> 523|<= 525|>= 528|/ 530|% 551|+ 555|- 17321|<<& 17322|<=& 17323|==* 17324|>=& 17325|>>& .TE .)T (Note that your .cW oid numbers may be different.) The operators we are interested in are those with .cW oid s 17321 through 17325. The values you get will probably be different, and you should substitute them for the values below. We can look at the operator names and pick out the ones we just added. (Of course, there are lots of other queries we could used to get the oids we wanted.) .lp Now we're ready to update .cW pg_amop with our new operator class. The most important thing in this entire discussion is that the operators are ordered, from less equal through greater equal, in .cW pg_amop . Recall that the BTREE instance's .cW oid is 400 and .cW int4_abs_ops is .cW oid 17314. Then we add the instances we need: .(C * append pg_amop (amopid = "400"::oid, /* btree oid */ amopclaid = "17314"::oid, /* pg_opclass tuple */ amopopr = "17321"::oid, /* <<& tup oid */ amopstrategy = "1"::int2, /* 1 is <<& */ amopselect = "btreesel"::regproc, amopnpages = "btreenpage"::regproc) \\g * append pg_amop (amopid = "400"::oid, amopclaid = "17314"::oid, amopopr = "17322"::oid, amopstrategy = "2"::int2, amopselect = "btreesel"::regproc, amopnpages = "btreenpage"::regproc) \\g * append pg_amop (amopid = "400"::oid, amopclaid = "17314"::oid, amopopr = "17323"::oid, amopstrategy = "3"::int2, amopselect = "btreesel"::regproc, amopnpages = "btreenpage"::regproc) \\g * append pg_amop (amopid = "400"::oid, amopclaid = "17314"::oid, amopopr = "17324"::oid, amopstrategy = "4"::int2, amopselect = "btreesel"::regproc, amopnpages = "btreenpage"::regproc) \\g * append pg_amop (amopid = "400"::oid, amopclaid = "17314"::oid, amopopr = "17325"::oid, amopstrategy = "5"::int2, amopselect = "btreesel"::regproc, amopnpages = "btreenpage"::regproc) \\g .)C NOTE the order: .q less is 1, .q "less equal" is 2, .q equal is 3, .q "greater equal" is 4, and .q greater is 5. .lp Okay, now it's time to test the new opclass. First we'll create and populate a class: .(C * create pairs (name = char16, number = int4) \\g * append pairs (name = "mike", number = -10000) \\g * append pairs (name = "greg", number = 3000) \\g * append pairs (name = "lay peng", number = 5000) \\g * append pairs (name = "jeff", number = -2000) \\g * append pairs (name = "mao", number = 7000) \\g * append pairs (name = "cimarron", number = -3000) \\g * retrieve (pairs.all) \\g .TS tab(|) allbox; l l. name|number mike|-10000 greg|3000 lay peng|5000 jeff|-2000 mao|7000 cimarron|-3000 .TE .)C Okay, looks pretty random. Define an index using the new opclass: .(C * define index pairsind on pairs using btree (number int4_abs_ops) \\g .)C Now run a query that doesn't use one of our new operators. What we're trying to do here is to run a query that .b won't use our index, so that we can tell the difference when we see a query that .b does use the index. This query won't use the index because the operator we use in the qualification isn't one that appears in the list of strategies for our index. .(C * retrieve (pairs.all) where pairs.number < 9000 \\g .TS tab(|) allbox; l l. name|number mike|-10000 greg|3000 lay peng|5000 jeff|-2000 mao|7000 cimarron|-3000 .TE .)C Yup, just as random; that didn't use the index. Okay, let's run a query that .b does use the index: .(C * retrieve (pairs.all) where pairs.number <<& 9000 \\g .TS tab(|) allbox; l l. name|number jeff|-2000 cimarron|-3000 greg|3000 lay peng|5000 mao|7000 .TE .)C Note that the .cW number values are in order of increasing absolute value (as they should be, since the index was used for this scan) and that we got the right answer\(emthe instance for .cW mike doesn't appear, because \(mi10000 >=& 9000. @ 1.14 log @I lied @ text @d554 2 a555 1 \*(PP fully supports both fixed-length and variable-length arrays. a642 18 Note that the .cW text type in \*(PP is defined as an array of character, so queries like .(C * retrieve (manager.name) where manager.employees[1][1] = 'b' \\g * retrieve (manager.name) where manager.employees[3] = "jeff" \\g * retrieve (manager.employees[3][2]) \\g * delete manager where manager.employees[2][1] = 'g' \\g .)C will all work. However, in general \*(PP currently only supports arrays of one dimension. @ 1.13 log @minor stuff. I'll shut up now. @ text @d1318 1 a1318 1 printf("size is %d\n", sizeof (MYTYPE)); d1396 1 a1396 1 where tutorial.a = "(0.0, 0.0, 10.0)"::circle d1731 2 a1732 1 .cW "stats", the situation is a little more @ 1.12 log @fix up things relating to Postquel functions and C functions on complex types. @ text @d825 4 a828 3 Any collection of commands in the \*(PQ query language can be packaged together and defined as a function, usually returning either a set of instances or a set of base types. For example, the following function d865 1 a865 1 .cW EMP d884 2 a885 1 into it with the use of the nested dot notation. Whenever such a d941 2 a942 1 instances. For example, consider the function d954 2 a955 1 single instance. Given the query d962 4 a965 1 them, in a "flattened" form. If the query in the body of d971 2 a972 1 set returned by the query in the function's body. See the \*(PP d1487 2 a1488 1 called. The instance itself will be passed in your function's d1529 2 a1530 1 the desired type. For example, if you have an attribute d1558 2 a1559 1 * retrieve (EMP.all) where overpaid2(EMP, DEPT, "bill", 8) \\g d1567 3 a1569 3 TUPLE emp, dept; char *name; long number; @ 1.11 log @MAJOR revisions and reformatting. @ text @d825 3 a827 4 Any collection of commands in the \*(PQ query language can be packaged together and defined as a function, which is assumed to return a collection of instances. For example, the following function d855 1 a855 1 Since \*(PQ functions return sets of instances, d863 9 a871 1 We can do this by simply defining a function: d874 23 a896 5 (language = "postquel", returntype = setof EMP) arg is (char16) as "retrieve (EMP.all) where EMP.name = DEPT.manager and DEPT.name = $1" \\g d898 1 a898 1 Here, we have added an attribute to the d903 1 a903 1 i.e. it has a value which is one or more instances of a905 7 Specifically, the value of the .cW manager field is intended to be an instance of .cW EMP which is the manager of the indicated employee. a909 27 .lp We can also define a function to associate a manager with each instance of .cW EMP . Specifically, we will define a second \*(PQ function .cW lookup_mgr : .(C * define function lookup_mgr (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" \\g .)C In this case, the function .cW lookup_mgr has an argument which is an instance of the class .cW EMP . Therefore, it takes a value for each instance of .cW EMP , which is the result of the query with the field .cW EMP.dept filled in with its appropriate constant. .lp d911 1 a911 1 .cW lookup_mgr d921 1 a921 1 * append emp (emp.lookup_mgr.name = "Smith") \\g d929 1 a929 1 * retrieve (emp.lookup_mgr) \\g d934 6 a939 13 Since .cW lookup_mgr takes a class as its only argument and returns a class, \*(PQ allows referencing into it with the use of the nested dot notation. Whenever such a function is defined over a class, a user can utilize the cascaded dot notation to reference into (i.e. access the fields of) the objects returned by the function. .lp The following query finds all the employees who work for Joe: d941 28 a968 7 * retrieve (EMP.name) where EMP.manager.name = "Joe" \\g .)C This is exactly equivalent to: .(C * retrieve (EMP.name) where name(lookup_mgr(EMP)) = "Joe" \\g .)C d1241 1 a1241 1 .cW "-G 0" '' `` d1478 12 a1489 5 to be the instance being qualified at the moment your function is called. Rather than getting the instance itself in your function's parameter list, you will use \*(PP library routines to access the data in the instance as described below. d1500 2 a1501 1 overpaid() /* note that there are no arguments */ d1503 1 a1503 1 extern Datum GetAttribute(); d1506 3 a1508 3 salary = (short) GetAttribute("salary"); seniority = (short) GetAttribute("seniority"); performance = (short) GetAttribute("performance"); d1513 1 a1513 1 .cW GetAttribute d1516 5 a1520 15 It has one argument which is the name of the desired attribute, and its return type is a type "Datum" which is defined as being large enough to hold pointer values and all integer types.\** .(f \**In \*(PP \*(PV, .cW Datum is defined as .cW "long int" . .)f .cW GetAttribute will align data properly so you can cast its return value to the desired type. For example, if you have an attribute d1525 1 a1525 1 .cW GetAttribute d1530 1 a1530 1 str = (char *) GetAttribute("name") a1531 2 Note that the instance argument must be the first argument when using your function in \*(PQ queries. d1540 1 a1540 1 arg is (SET) d1543 3 a1545 8 The special flag .cW SET in the argument list tells \*(PP that this function will be processing an instance. .lp Your function may only access one instance via the .cW GetAttribute call, but you can have additional base or user-defined types as arguments. d1548 1 a1548 1 * retrieve (EMP.all) where overpaid2(EMP, "bill", 8) \\g d1555 2 a1556 1 overpaid2(name, number) a1559 7 Note that the arguments are .q shifted one to the left, and the .cW EMP argument is omitted; its values are referenced via .cW GetAttribute. @ 1.10 log @clean up formatting @ text @d1 5 a5 1 .he 'revision $Revision$'DRAFT'$Date$' d7 42 a48 7 .\" $Header: /home1/frew/s2k/postgres/manual/RCS/manual.me,v 1.9 1992/07/03 23:23:14 mer Exp frew $ .\" .\" Start of nroff/troff specific definitions section. .\" .\" Defines II, PP, UU, PQ, LP, PV, OF, for .\" INGRES, POSTGRES, UNIX, POSTQUEL, LIBPQ, PICASSO, 4.0 .\" d56 1 a56 6 .\" .\" Start of format definition section. .\" .nr sp 10 \" standard section title point size .nr ps .50v \" .15v larger paragraph prespace .nr pp 11 \" one point larger paragraph pointsize d59 17 a75 4 .fo ''\\s+2%\\s0'' \" page numbers in 12 point type .nr so -.5i \" -1/2 inch additional section title offset .nr $i .5i \" paragraph base indent .\" a76 1 .sz \n(sp d78 2 a79 4 The \*(PP User Manual .sp 1 .sz \n(pp .sp 1 a83 1 .sp 1 d87 1 d92 2 a93 1 Berkeley. This project, led by Professor Michael Stonebraker, is d97 1 d100 14 a113 4 This manual describes Version \*(PV of \*(PP. \*(PP Software is available for SUN 4 class machines, for DECstation 3100 and 5000 machines, and for the SEQUENT Symmetry machine. d116 2 a117 1 Version \*(PV has been tuned modestly. Hence, on the d121 1 d125 2 a126 1 graduate, and staff programmers. The contributors (in d134 3 a136 1 implementing the "everything else" portion of the system. d139 2 a140 15 this manual, as did Claire Mosher and Chandra Ghosh. .sh 1 "ABSTRACT" .lp The \*(PP project undertook to build a next generation DBMS whose purpose was to rectify the known deficiencies in current relational DBMSs. This system, constructed over a five year period by one full time programmer and 3-4 part time students, is about 200,000 lines of code in the C programming language. \*(PP is available free of charge, and is being used by approximately 200 sites around the world at this writing. This manual describes the major concepts of the system and attempts to provide an accessible path into using the system. As such, it tries to give examples of the use of the major constructs, so a beginning user does not need to delve immediately into the Reference. d145 2 a146 1 specific type. In current commercial systems, possible types are d148 2 a149 1 dates. It is commonly recognized that this model is insufficient d152 5 a156 7 \*(PP tried to build a data model with substantial additional power, yet requiring the understanding of as few concepts as possible. The relational model succeeded in replacing previous models in part because of its simplicity. We wanted to have as few concepts as possible so that users would have minimum complexity to contend with. Hence, \*(PP leverages the following four constructs: d165 2 a166 1 and the initial data model appeared in [ROWE87]. The first rule d168 8 a175 4 storage manager concepts are detailed in [STON87]. The first "demo-ware" was operational in 1987, and we released Version 1 of \*(PP to a few external users in June 1989. A critique of version 1 of \*(PP appears in [STON90]. Version 2 followed in d177 6 a182 2 [STON90B]. We are now delivering version \*(PV, which is the subject of this manual. d187 4 a190 2 language functions. Arrays of types and functions of an instance are discussed, as well as the \*(PP rule system. This manual concludes with d192 9 a200 1 .sh 1 "WHAT SHOULD BE READ" d204 4 a207 1 the \*(PP "backend". The \*(PP Reference discusses additional aspects of d209 2 a210 1 command in a format similar to that used in \*(UU "man pages". d213 1 a213 1 by the parts of the \*(PP Reference necessary to build your application. d217 1 d220 4 a223 2 \*(PQ is the query language used for interacting with \*(PP. Here, we give an overview of how to use \*(PQ to access data. In other sections, d225 1 d229 2 a230 2 the directions in the release notes, you can create a database, .b foo, d232 1 a232 2 .(l .ft C d234 1 a234 2 .ft .)l d237 2 a238 1 administrator of the database just created. Database names must d243 3 a245 1 interact with it. First, you can run the \*(PP terminal d247 3 a249 1 execute commands in the query language \*(PQ. Second, you can d251 2 a252 1 library of subroutine and call facilities. This allows you to submit d254 8 a261 6 to your program. This interface is discussed further in the \*(LP section of the Reference. The third way of interacting with \*(PP is to use the facility called .b fast .b path, d264 6 a269 3 This facility is described in the Reference under "Fast Path". Lastly, \*(PP is accessible from the \*(OF programming environment. \*(OF is a graphical user interface (GUI) toolkit d271 2 a272 1 applications. \*(OF is a separate research project and is described d276 25 a300 11 The terminal monitor can be activated for any database by typing the command: .(l .ft C % monitor .ft .)l As a result, you will be greeted by the following message: .(l .ft C Welcome to the POSTGRES terminal monitor d304 4 a307 3 .ft .)l The "Go" indicates the terminal monitor is listening to you and d309 5 a313 2 by the monitor. The monitor indicates it is listening by typing * as a prompt. Printing the workspace can be performed by d315 1 a315 2 .(l .ft C d317 1 a317 2 .ft .)l d319 1 a319 2 .(l .ft C d321 4 a324 3 .ft .)l If you make a typing mistake, you can invoke the vi text editor d326 1 a326 2 .(l .ft C d328 1 a328 2 .ft .)l d330 7 a336 4 power of vi to make any necessary changes. For more info on using vi, type .(l .ft C d338 7 a344 4 .ft .)l Once you exit vi, your edited query will be in the monitor's query buffer and you can submit it to \*(PP by using the "\\g" command d348 1 a348 2 .(l .ft C d350 1 a350 2 .ft .)l d352 1 a352 2 .(l .ft C a353 1 d355 1 a355 2 .ft .)l d357 7 a363 3 on "monitor" in the \*(UU section of the Reference. .lp If you are the database administrator for a database, you d365 1 a365 2 .(l .ft C d367 1 a367 2 .ft .)l d369 1 a369 1 .b createuser, d371 3 a373 2 .b destroyuser, which are discussed further in the \*(UU section of the Reference. d375 1 d378 16 a393 2 In order to begin using \*(PP, create the .b foo d396 1 a396 6 monitor. The fundamental notion in \*(PP is that of a class, which is a named collection of instances of objects. Each instance has the same collection of named attributes, and each attribute is of a specific type. Furthermore, each instance has an installation wide unique (never-changing) identifier (OID). .sh 3 "Creating a New Class" d399 27 a425 12 along with all attribute names and their types, for example: .(l .ft C * create EMP (name=text, salary=int4, age=int4, dept=char16) \\g * create DEPT (dname=char16, floor=int4, manager=text) \\g .ft .)l So far, the create command looks exactly like the create statement in a traditional relational system. However, we will presently see d428 1 d430 1 d432 19 a450 17 To populate a class with instances, one can use the append command as follows: .(l .ft C * append EMP (name="Joe",salary=1400, age=40,dept="shoe") \\g * append EMP (name="Sam",salary=1200, age=29,dept="toy") \\g * append EMP (name="Bill",salary=1600, age=36,dept="candy") \\g .ft .)l This will add 3 instances to EMP, one for each command. d453 5 a457 2 The EMP class can be queried with normal selection and projection queries. For example, to find the employees under 35 d459 28 a486 19 .(l .ft C * retrieve (EMP.name) where EMP.age<35 \\g .ft .)l Notice that parentheses are required around the target list of returned attributes. Like Quel, \*(PQ allows you to return computations in the target list as long as they are given a name, e.g: .(l .ft C * retrieve (result=EMP.salary/EMP.age) where EMP.name="Bill" \\g .ft .)l d489 7 a495 5 Moreover, like Quel, any retrieve query can be redirected to a new class in the database and arbitrary boolean operators (and, or, not) are allowed in the qualification of any query: .(l .ft C d497 3 a499 5 where EMP.age<35 and EMP.salary>1000 \\g .ft .)l d502 1 a502 2 Joins are done in \*(PQ in essentially the same way as in QUEL. To find the names of employees which are the same age, one d504 1 a504 2 .(l .ft C d506 10 a515 9 from E1 in EMP, E2 in EMP where E1.age=E2.age and E1.name != E2.name \\g .ft .)l In this case both E1 and E2 are surrogates for an instance of the class EMP and range over all instances of the class. A \*(PQ d517 4 a520 2 surrogates. The semantics of such a join are identical to those of QUEL, namely the qualification is a truth expression defined d525 2 d529 8 a536 11 Updates are accomplished in \*(PQ using the replacement statement, e.g: .(l .ft C * replace EMP (salary=E.salary) from E in EMP where EMP.name="Joe" and E.name="Sam" \\g .ft .)l d538 1 d541 6 a546 9 Lastly, deletions are done using the delete command, as follows: .(l .ft C * delete EMP where EMP.salary > 0 \\g .ft .)l d548 4 a551 1 leave the EMP class empty. d554 3 a556 4 \*(PP fully supports both fixed-length and variable-length arrays, and here we illustrate their use. First, we create a class with an array type. .(l .ft C d558 15 a572 10 pay_by_quarter = int4[4]) \\g .ft .)l The above query will create a class named SAL_EMP with a variable length field .b name and a fixed-length field .b pay_by_quarter, which represents the employee's salary by quarter. Now we do some appends; d574 2 a575 3 within {curly brackets} and separate them by commas. .(l .ft C d577 1 a577 3 pay_by_quarter = "{10000, 10000, 10000, 10000}") \\g d580 1 a580 3 pay_by_quarter = "{10000, 15000, 15000, 15000}") \\g d583 11 a593 10 pay_by_quarter = "{20000, 25000, 25000, 25000}") \\g .ft .)l \*(PP uses the FORTRAN numbering convention for arrays - that is, \*(PP arrays start with array[1] and end with array[n]. Now, we can run some queries on SAL_EMP: .(l .ft C d595 34 a628 33 where SAL_EMP.pay_by_quarter[1] != SAL_EMP.pay_by_quarter[2] \\g .ft .)l This query retrieves the names of the employees whose pay changed in the second quarter. .(l .ft C * retrieve (SAL_EMP.pay_by_quarter[3]) \\g .ft .)l This query retrieves the third quarter pay of all employees. .(l .ft C * delete SAL_EMP where SAL_EMP.name[1] = 'j' \\g .ft .)l This query deletes everyone from SAL_EMP whose name begins with the letter "j". SAL_EMP should now contain only bill. .lp \*(PP supports arrays of base and user-defined types, as well as "arrays of arrays", as in the following example: .(l .ft C * create manager (name = char16, employees = text[]) \\g d631 1 a631 2 employees = "{"wei", "greg", "jeff"}") \\g d634 1 a634 1 employees = "{"bill", "joe"}") \\g d637 6 a642 4 employees = "{"mike", "alice"}") \\g .ft .)l This creates a class "manager", and provides a list of employees. Note that d644 1 a644 1 .b text d646 1 a646 2 .(l .ft C d648 1 a648 1 where manager.employees[1][1] = 'b' \\g d651 1 a651 1 where manager.employees[3] = "jeff" \\g d655 4 a658 5 * delete manager where manager.employees[2][1] = 'g' \\g .ft .)l will all work. However, in general \*(PP currently only supports arrays of d660 1 d665 10 a674 3 distinguish it from other data managers, such as inheritance and time travel. In the next section we will cover how the user can extend the query language via query language functions and composite objects, as well d676 5 a680 1 user defined types, operators, and programming language functions. d683 36 a718 28 Starting with the .b EMP class from the previous section, reappend the three persons who were just deleted. .lp Now create a second class STUD_EMP, and populate it as follows: .(l .ft C * create STUD_EMP (location=point) inherits (EMP) \\g * append STUD_EMP (name = "Sunita", age = 41, salary=1300, dept="electronics", location="(3,5)") \\g .ft .)l In this case, an instance of STUD_EMP inherits all data fields from its parent, EMP, to wit: name, salary, age, and dept. Furthermore, student employees have an extra field, location, that shows their address as a (longitude, latitude) pair. In \*(PP a class can inherit from zero or more other classes, and the inheritance hierarchy is generally a directed acyclic graph. Additionally, in \*(PQ a query can either reference all d720 2 a721 1 descendants. For example, the following query finds the d723 35 a757 25 .(l .ft C * retrieve (E.name) from E in EMP where E.age > 39 \\g .ft .)l On the other hand, to find the names of all student employees and employees over 40, the query is: .(l .ft C * retrieve (E.name) from E in EMP* where E.age > 39 \\g .ft .)l Here the * after EMP indicates that the query should be run over EMP and all classes below EMP in the inheritance hierarchy. This use of * allows a user to easily run queries over a class and all its descendent classes. .lp Notice that location in STUD_EMP is not a traditional relational data type. As we will see later, a \*(PP installation can be d759 1 d762 10 a771 12 \*(PP supports the notion of time travel. This feature allows a user to run historical queries. For example, to find the salary of Sam at time T one would query: .(l .ft C * retrieve (E.salary) from E in EMP["now"] where E.name="Sam" \\g .ft .)l d773 8 a780 5 valid at the correct time and get the appropriate salary. One can also give a time range. For example to see all of the salaries that Sam has ever earned one would query: .(l .ft C d782 26 a807 17 from E in EMP["Jan 1 00:00:00 1970 GMT", "now"] where E.name = "Sam" \\g .ft .)l A short hand notation for the above time range can be given as: .(l .ft C * retrieve (E.salary) from E in EMP[,] where E.name = "Sam" \\g .ft .)l d810 1 a810 1 Here, we will discuss user extensions to the \*(PQ query language, via d813 1 d816 6 a821 2 \*(PQ provides two types of functions: query language functions and functions provided by the user via a programming language. d828 6 a833 4 For example, the following function defines the high-paid employees: .(l .ft C d835 8 a842 9 (language = "postquel", returntype = EMP) as retrieve (EMP.all) where EMP.salary>50000 \\g .ft .)l \*(PQ functions can also have parameters, for example: .(l .ft C d844 7 a850 7 (language = "postquel", returntype = EMP) arg is (int4) as retrieve (EMP.all) where EMP.salary>$1 \\g .ft .)l d853 1 d856 1 a856 1 Moreover, since \*(PQ functions return sets of instances, d858 42 a899 52 types. For example, consider extending the EMP class with a manager field: .(l .ft C * addattr (manager=EMP) to EMP \\g .ft .)l Here, we have added an attribute to the EMP class which is of type EMP, i.e. it has a value which is zero or more instances of the class EMP. Specifically, the value of the manager field is intended to be an instance of EMP which is the manager of the indicated employee. Since the value of manager has a record- oriented structure, we call it a composite object. We will now illustrate assigning values to instances of manager. First, we will define the function mgr_lookup: .(l .ft C * define function mgr_lookup (language = "postquel", returntype = EMP) arg is (char16) as retrieve (EMP.all) where EMP.name=DEPT.manager and DEPT.name=$1 \\g .ft .)l This function can be used to assign values to the manager attribute in the EMP class, for example: .(l .ft C * append EMP (name="Sam",salary=1000.0 ,age=40,dept="shoe", manager=mgr_lookup("shoe")) \\g .ft .)l Since EMP.manager is a composite object, \*(PQ allows referencing into it with a second use of the dot notation. Whenever a composite object appears in a class, a user can utilize the cascaded dot notation to reference into the object. .lp In this case, the same \*(PQ function is used to define the value of manager for every EMP instance. As a result, there is a second more efficient way to utilize \*(PQ functions to assign values to the manager attribute. Specifically, we will define a second \*(PQ function, lookup_mgr, as follows: .(l .ft C d901 52 a952 18 (language="postquel", returntype = EMP) arg is (EMP) as retrieve (E.all) from E in EMP where E.name=DEPT.manager and DEPT.name=EMP.dept \\g .ft .)l In this case, the function lookup_mgr has an argument which is an instance of the class EMP. Therefore, it takes a value for each instance of EMP, which is the result of the query with the field "EMP.dept" filled in with its appropriate constant. .lp Consequently, the user can think of the function lookup_mgr as an attribute of EMP and can reference it just like any other attribute (except for direct appends - that is, append emp (emp.manager.name = "Smith") won't work). d956 5 a960 11 .(l .ft C * retrieve (EMP.name) where EMP.manager.name="Joe" \\g .ft .)l The same query is also available in functional notation: .(l .ft C d962 3 a964 5 where lookup_mgr(EMP).name="Joe" \\g .ft .)l d968 3 a970 3 .b dynamically .b load a binary object file created by the user. This allows \*(PP to call d972 15 a986 3 language. These functions can then be used to input and output user types, used as operators for qualifying data, and can be used to define ordering for use in defining indices on user-defined types. d988 5 a992 1 \*(PP's concept of types includes built-in types and user-defined types. d995 2 a996 1 below. There is no intrinsic performance difference between using a system d999 1 d1003 3 a1005 1 \*(PP regards a user-defined type as a "blob of memory" upon which d1009 1 d1013 5 a1017 4 .lp .b o Input and output functions for the type. .lp d1020 11 a1030 7 in memory. These at least are necessary to define the type. .lp .b o Operator functions for the type. .lp These functions define the meanings of "equal," "less than," "greater than," etc for your type. d1034 3 a1036 2 .b "circle" type, using functions written in the "C" programming language. d1040 4 a1043 3 like, both in string format and internally in memory. Circles have a center and a radius, so a reasonable way to define a circle is: a1044 1 .ft C a1045 1 .ft d1047 14 a1060 11 in whatever Cartesian units we desire. Now we essentially have defined what the input to our circle input function looks like, and what the output from the circle output function should look like. Now we have to come up with an internal representation for a circle in memory. Note that as this memory will be written to disk and read from disk, our internal representation must be both .b COMPLETE and .b CONTIGUOUS, that is, we can have no pointers within our structure. The following declaration for a circle is legal d1062 4 a1065 7 .(l .ft C typedef struct { double x,y; } POINT; d1067 1 a1067 2 typedef struct { d1070 15 a1084 14 } CIRCLE; .ft .)l Now, just for the sake of argument, we will define what may seem at first glance to be a correct type and explain why it .b will .b not .b work. Suppose we define our circle type thus: .(l .ft C typedef struct { d1087 15 a1101 14 } CIRCLE; .ft .)l where POINT is as in the first example. It will .b not work because .b center is a volatile pointer and will not have its value written to disk. .b Beware of such constructions when defining types, since they may appear to work until you shut down the data manager, and because there is no way for \*(PP to let you know that there is a problem until garbage is written to disk and passed to your output function. d1104 11 a1114 9 Suppose in defining our type "circle," we have a file called "circle.c" and it is in /usr/postgres/tutorial/circle.c. It has been compiled, and the compiled object is in /usr/postgres/tutorial/circle.o. Note that as mentioned above, all functions related to our "circle" type have to be in one object (".o") file. The source code for circle.c is at the end of this section. .lp For the sake of argument, suppose we are on a DECstation, d1117 29 a1145 19 In "circle.c," there are three functions: .lp .b circle_in, which is the input function for circles. It takes a string as input and outputs a pointer to type CIRCLE. It must know how to parse the string itself. .lp .b circle_out, which is the output function for circles. It is passed a pointer to type CIRCLE as input and returns a string. This string must be suitable for input to circle_in. That is, the following piece of code should work: .(l .ft C CIRCLE *circle, *circle_in(); char *circle_out(); .ft .)l .b eq_area_circle, d1147 2 a1148 1 circles. For the purposes of this discussion, circles are equal d1151 7 a1157 119 Now that we have written these functions and compiled them, we have to let \*(PP know that they exist. We will do this first by running the following queries to define the input and output functions: .(l .ft C * define function circle_in (language = "c", returntype=circle) arg is (char16) as "/usr/postgres/tutorial/circle.o" \\g * define function circle_out (language = "c", returntype=char16) arg is (circle) as "/usr/postgres/tutorial/circle.o" \\g .ft .)l Now that we have done this, we can define the type. Note that in and out functions have to be defined BEFORE we define the type. Defining the type first will not work. \*(PP will notify you that return type circle is not defined yet, but everything is OK. The query to define the "circle" type is: .(l .ft C * define type circle (internallength=24, input=circle_in, output=circle_out) \\g .ft .)l where .b internallength is the size of the circle structure itself in bytes. For circles, the arguments are three doubles, thus .(l .ft C sizeof(circle) = 3 * sizeof(double) = 3 * 8 = 24 .ft .)l When defining your own types, you should write a test program that does a .(l .ft C printf("size is %d\n", sizeof (mystruct)); .ft .)l on your type. If .b internallength is defined incorrectly, you will encounter strange errors which may crash the data manager itself. If this were to happen with our CIRCLE type, we would have to do a .(l .ft C * remove type circle \\g .ft .)l and redefine the circle type. Note that we would .b not have to redefine our functions - \*(PP already knows about them. .sh 3 "Defining an operator for our type" .lp Now that we have finished defining the "circle" type, we can create classes with circles in them, append records to them with circles defined, and retrieve the values of the entire list of records. But we can do nothing else until we have some circle operators. To do this, we make use of the concept of .b operator .b overloading, and in this case we will set the \*(PP equality operator "=" to work for circles. First we have to tell \*(PP that our circle equality function exists with the following query: .(l .ft C * define function eq_area_circle (language = "c", returntype=bool) arg is (circle,circle) as "/usr/postgres/tutorial/circle.o" \\g .ft .)l Now that we have done this, we will now bind this function to the equality symbol with the following query: .(l .ft C * define operator = (arg1=circle, arg2=circle, procedure=eq_area_circle) \\g .ft .)l .sh 3 "Using our type" .lp Now we are ready to create a class and run some queries on our new type: .(l .ft C * create tutorial(a=circle) \\g * append tutorial (a="(1.0,1.0,10.0)" ::circle) \\g * append tutorial (a="(2.0,2.0,5.0)" ::circle) \\g * append tutorial (a="(0.0,1.8,10.0)" ::circle) \\g d1159 3 a1161 33 * retrieve (tutorial.all) where tutorial.a = "(0.0,0.0,10.0)"::circle .ft .)l And the monitor will display the result of the retrieve command... .(l .ft C .TS tab (+) allbox; l l. a (1.0,1.0,10.0) (0.0,1.8,10.0) .TE .ft .)l Recall that we defined circles as being equal if their areas were equal. .lp Other operators (less than, greater than, etc) can be defined in a similar way. Note that the "=" symbol will still work for other types--it has merely had a new type added to the list of types it works on. Any string of "punctuation characters" other than brackets, braces, or parentheses can be used in defining an operator. .sh 3 "C Source code for our type" .lp The following is the source code for circle.c. .(l .ft C #include #include "utils/geo-decls.h" d1164 2 a1165 2 POINT center; double radius; d1168 3 a1170 7 CIRCLE *circle_in(); char *circle_out(); int pt_in_circle(); #define LDELIM '(' #define RDELIM ')' #define NARGS 3 d1174 1 a1174 2 char *str; d1176 3 a1178 5 double atof(), tmp; char *strcpy(), *p, *coord[NARGS], buf2[1000]; int i, fd; CIRCLE *result; d1203 1 a1203 2 CIRCLE *circle; d1205 1 a1205 1 char *result; d1211 2 a1212 4 sprintf(result, "(%g,%g,%g)", circle->center.x, circle->center.y, d1214 1 d1219 2 a1220 3 pt_in_circle(point, circle) POINT *point; CIRCLE *circle; d1222 155 a1376 1 extern double point_dt(); d1378 33 a1410 7 return ( point_dt(point, &circle->center) < circle->radius ); } .ft .)l d1412 1 d1415 15 a1429 8 In order for \*(PP to free memory associated with processing your type, you must use the memory allocator "palloc" and avoid standard \*(UU memory managers such as malloc. If you do not, \*(PP will chew up ever increasing amounts of memory. "palloc" has the same arguments as malloc, that is .(l .ft C char *palloc(size) d1431 10 a1440 8 .ft .)l To free memory allocated with palloc, use "pfree", which is used in identical manner as the \*(UU "free" library call: .(l .ft C void pfree(ptr) d1442 2 a1443 2 .ft .)l d1447 2 a1448 1 re-load a function in the course of debugging. This is d1455 4 a1458 7 .(l .ft C * load "/usr/postgres/tutorial/circle.o" \\g .ft .)l d1466 1 a1466 1 .b inheritable; d1471 5 a1475 2 \*(PP defines the "current instance" to be the instance being qualified at the moment your function is called. Rather than getting the instance itself d1480 7 a1486 8 .(l .ft C * retrieve (emp.all) where overpaid(emp) .ft .)l In the query above, a reasonable "overpaid" function would be: .(l .ft C d1488 1 a1488 2 overpaid() /* note that there are no arguments */ d1491 1 a1491 7 short age, salary, performance; salary=(short) GetAttribute("salary"); seniority=(short) GetAttribute("seniority"); performance=(short) GetAttribute("performance"); d1493 3 a1495 2 return (salary > (seniority * performance)); d1497 1 d1499 2 a1500 3 .ft .)l .b GetAttribute d1502 2 a1503 1 attributes out of the current instance. It has one argument d1506 8 a1513 3 pointer values and all other types--currently it is defined as a "long." .b GetAttribute d1515 8 a1522 3 its return value into the desired form. So if you have an attribute "name" which is of the \*(PQ type "char16," the .b GetAttribute d1524 3 a1526 4 .(l .ft C char *str d1528 1 a1528 2 .ft .)l d1532 5 a1536 4 To let \*(PP know about the "overpaid" function, just do the following: .(l .ft C d1538 7 a1544 8 (language = "c", returntype=bool) arg is (SET) as "/usr/postgres/tutorial/overpaid.o" \\g .ft .)l The special flag "set" in the argument list tells d1548 1 a1548 1 .b GetAttribute d1551 7 a1557 9 .(l .ft C * retrieve (emp.all) where overpaid2(emp, "bill", 8) .ft .)l could be written, and overpaid2 would be declared thus .(l .ft C a1559 1 d1562 6 a1567 3 .ft .)l Note that the arguments are "shifted" one to the left, and the "emp" argument d1569 2 a1570 1 .b GetAttribute. d1573 4 a1576 2 As discussed above, \*(PP fully supports arrays of base types. Additionally, \*(PP supports arrays of user-defined types as well. When you define a type, d1580 1 d1584 7 a1590 10 .b circle example discussed above, we will create a class containing an array of circles thus: .(l .ft C * create circles (list = circle[]) \\g .ft .)l d1592 26 a1617 25 .(l .ft C * append circles (list = "{"(1.0, 1.0, 5.0)", "(2.0, 2.0, 10.0)"}") \\g * append circles (list = "{"(2.0, 3.0, 15.0)", "(2.0, 2.0, 10.0)"}") \\g * append circles (list = "{"(2.0, 3.0, 4.0)"}") \\g .ft .)l We can now run queries like the following: .(l .ft C * retrieve (circles.list[1]) \\g d1619 15 a1633 7 where circles.list[1] = "(0.0, 0.0, 4.0)" \\g .ft .)l .b NOTE: Recall how we defined circle equality above. d1637 3 a1639 2 be defined as a type in and of itself. This is useful for defining "arrays of arrays". d1641 2 a1642 2 The special functions .b array_in d1644 3 a1646 2 .b array_out are used by \*(PP to input and output arrays of any existing type. Here, d1648 1 a1648 2 .(l .ft C d1650 3 a1652 7 (input = array_in, output = array_out, internallength = variable, element = int4) \\g .ft .)l d1654 1 a1654 1 .b element d1656 1 a1656 1 .b internallength d1658 4 a1661 3 .b variable indicates that it is a variable length attribute. .b NOTE d1663 1 a1663 1 .b array_in d1665 4 a1668 3 .b array_out .i must be variable length. d1670 2 a1671 3 Now we can use our type defined above to create an array of integer arrays: .(l .ft C d1673 3 a1675 9 (input = array_in, output = array_out, internallength = variable, element = int_array) \\g .ft .)l Now we use our new type: .(l .ft C d1678 1 a1678 3 * append stuff (a = "{{1, 2, 3} , {4, 5}, {6, 7, 8}}") \\g d1682 1 a1682 2 * append stuff (a = "{{5, 4, 3} , {2, 2}}") \\g d1685 1 a1685 1 where stuff.a[1][1] < stuff.a[1][2] \\g d1688 1 a1688 1 where stuff.a[3][1] < stuff.a[1][2] \\g d1690 7 a1696 8 * retrieve (s.all) from s in stuff where s.a[2][2] = stuff.a[1][1] \\g .ft .)l We can also define operators for equality, less than, greater than, etc. which operate on our new array type as necessary. .sh 3 "Creating an array type ``from scratch''" d1700 27 a1726 17 fixed-length array. In this section, we will discuss how to go about doing this. .lp For the purpose of this section, we will create an array of four longs called .b quarterly, and a variable length array of longs called .b stats. Also, assume we are on a machine where sizeof(long) is 4. .lp The only special things we need to know when writing the in/out functions for .b quarterly is that \*(PP will pass a "simple" array of longs to the output function and expect a "simple" array of longs in return from the input function. A sample "simple" array suitable for quarterly can be declared as follows: .(l .ft C d1728 5 a1732 4 .ft .)l For the variable length array "stats", the situation is a little more complicated. Because \*(PP will not know in advance how big the array is, d1734 16 a1749 5 first four bytes of the memory which contains the array. The expected structure is as follows: .(l .ft C typedef struct d1751 8 a1758 11 long length; /* Force contiguity */ unsigned char bytes[1]; } VAR_LEN_ATTR; .ft .)l The in function for the stats array will look something like this: .(l .ft C VAR_LEN_ATTR *stats_in(s) d1760 1 a1760 1 char s; d1762 1 a1762 3 { VAR_LEN_ATTR *stats; long array_size, *arrayp; d1764 1 a1764 2 ... figure out how long the stats array should be d1766 4 a1769 24 /* * We need the extra sizeof(long) * to hold the length */ stats = (VAR_LEN_ATTR *) palloc(array_size * sizeof(long) + sizeof(long)); /* length is length of the entire structure * in bytes, INCLUDING ItSELF */ stats->length = array_size * sizeof(long) + sizeof(long); arrayp = &(stats->bytes[0]); for (i = 0; i < length; i++, arrayp++) { populate the stats array with numbers from s } d1771 1 a1771 1 return(stats); d1773 22 a1794 21 .ft .)l The output function for stats will get the same VAR_LEN_ATTR structure. .lp Now, using the .b define .b function command, and assuming the functions are in /usr/postgres/tutorial/stats.c and /usr/postgres/tutorial/quarterly.c, we can define our two arrays. First we will define the fixed-size array "quarterly". Note that internallength is known to be 16, because .(l .ft C sizeof(long) * 4 = 4 * 4 = 16 .ft .)l on our hypothetical machine. .lp First we define the in/out functions: .(l .ft C d1796 3 a1798 5 (language="c", returntype=quarterly) arg is (char16) as "/usr/postgres/tutorial/quarterly.o" \\g d1801 3 a1803 3 (language = "c", returntype = char16) arg is (quarterly) as "/usr/postgres/tutorial/quarterly.o" a1804 6 \\g .ft .)l And now we can define the type: .(l .ft C d1806 7 a1812 11 (internallength = 16, input = quarterly_in, output = quarterly_out, element = int4) \\g .ft .)l Now we define the stats array. First we define its in/out functions: .(l .ft C d1814 3 a1816 5 (language = "c", returntype = stats) arg is (char16) as "/usr/postgres/tutorial/stats.o" \\g d1819 3 a1821 3 (language = "c", returntype = char16) arg is (stats) as "/usr/postgres/tutorial/stats.o" a1822 7 \\g .ft .)l and now we define the type. Note that setting internallength to variable tells \*(PP that it is a variable length type. .(l .ft C d1824 6 a1829 4 (internallength = variable, input = stats_in, output = stats_out, element = int4) d1831 2 a1832 7 \\g .ft .)l Now we can run some queries: .(l .ft C * create test (a = quarterly, b = stats) \eg d1834 2 a1835 11 * append test (a = "1 2 3 4"::quarterly, b = "5 6 7"::stats) \eg * append test (a = "1 3 2 4"::quarterly, b = "6 4"::stats) \eg * append test (a = "7 11 6 9"::quarterly, b = "1 2"::stats) \eg d1837 7 a1843 3 * retrieve (test.all) where test.a[4] = test.b[2] \eg .ft C d1845 1 a1845 2 tab (+) allbox; l l d1847 2 a1848 2 a+b 1324+64 d1850 1 a1850 2 .ft .)l d1853 4 a1856 3 .i your .i function will define how to parse the string. The brackets notation is only a d1858 1 a1858 1 .b array_in d1860 1 a1860 1 .b array_out d1862 3 a1864 2 .i not a part of the formal \*(PQ definition. d1867 9 a1875 2 The types discussed to this point are all "small" objects - that is, they are smaller than 8K in size. If you require a larger type for something like d1877 9 a1885 9 the \*(PP Large Object interface. The basic model of this interface is the \*(UU file system interface; the particulars of this interface are detailed in Section 7 of the \*(PP Reference. .sh 3 "What can you do with large objects?" .lp The answer to this question is anything that can be done with small objects, as long as all functions which access a large object use the \*(PP large object interface. All discussion after this point will presume that you have read this section of the \*(PP Reference. d1889 3 a1891 2 functions. For the purposes of this discussion, we assume that two functions, .b large_in d1893 13 a1905 7 .b large_out have been written using the large object interface, and that these functions are in /usr/postgres/tutorial/large.c. We also presume that we are using the "file as an ADT" interface for large objects discussed in the Reference. Now, we define a large object which could be used for storing map data: .(l .ft C d1907 3 a1909 3 (language = "c", returntype = map) arg is (char16) as "/usr/postgres/tutorial/large.o" a1910 2 \\g d1912 3 a1914 5 (language = "c", returntype = char16) arg is (map) as "/usr/postgres/tutorial/large.o" \\g d1917 5 a1921 8 (input = large_in, output = large_out, internallength = variable) \\g .ft .)l .b NOTE: Large objects are d1923 6 a1928 4 variable length. Now we can use our large object: .(l .ft C d1931 2 a1932 9 * append maps (name = "earth", a = "/usr/postgres/maps/earth") \\g * append maps (name = "moon", a = "/usr/postgres/maps/moon") d1934 5 a1938 4 \\g .ft .)l Notice that the above queries are identical to the syntax we have been using d1941 1 d1945 2 a1946 1 object types. The only caveat is that, like any other functions which process d1949 2 a1950 1 use the large object interface described in Section 7 of the \*(PP Reference. d1952 2 a1953 4 .(l .ft C * retrieve (emp.name) where beard(emp.picture) = "red" \\g d1956 2 a1957 5 where height(mountain.topomap) > 10000 \\g .ft .)l d1962 2 a1963 1 methods, presuming appropriate operator classes have been defined. Operator d1965 221 a2185 1 .sh 2 "User defined types and indices" d2188 4 a2191 2 type and associated functions with existing access methods. This way, you can define a BTREE or RTREE index on your own type. To do this, we will d2205 2 a2206 1 access methods work. These will be discussed, and then a sample procedure d2210 3 a2212 1 The pg_am class contains one instance for every user defined access method. d2216 30 a2245 25 .(l .ft C amname: name of the access method amowner: object id of the owner's instance in pg_user amkind: not used at present, but set to 'o' as a place holder amstrategies: number of strategies for this access method (see below) amsupport: number of support routines for this access method (see below) am*: procedure identifiers for interface routines to the access method. For example, regproc ids for opening, closing, and getting instances from the access method appear here. .ft .)l The object ID of the instance in pg_am is used as a foreign key in lots of other classes. For BTREES, this object ID is 403. You don't need to add d2247 3 a2249 8 the access method instance you want to extend. .(l .ft C * retrieve (pg_am.oid) where pg_am.amname = "btree" \eg d2251 1 a2251 2 tab (+) allbox; l d2256 1 a2256 2 .ft .)l d2258 1 a2258 1 .b amstrategies d2260 11 a2270 5 types. For example, BTREES impose a strict ordering on keys, less to greater. Since \*(PP allows the user to define operators, \*(PP cannot in general look at the name of an operator (eg, ">", "<") and tell what kind of comparison it is. In fact, some access methods (like rtrees) don't impose a less to greater ordering, but some other ordering, like containment. d2273 9 a2281 3 qualification in order to improve access speeds. This implies that \*(PP needs to know, for example, that "<=" and ">" partition a BTREE. Strategies is the way that we do this. d2285 5 a2289 2 to add a new operator class. In the pg_am class, the .b amstrategies d2292 2 a2293 3 number is 5. These strategies correspond to .(l .ft C d2295 11 a2305 8 tab (+); l l l l. less than+1 less than or equal+2 equal+3 greater than or equal+4 greater than+5 a2306 2 .ft .)l d2308 4 a2311 1 above to the pg_amop relation (see below). The access method code can use d2313 7 a2319 3 BTREE, compute selectivity, and so on. Don't worry about the details of adding procedures yet; just understand that there's a set of these for int2's, int4's, oid's, and every other data type on which a BTREE can operate. d2337 1 a2337 1 .b pg_am d2339 1 a2339 1 .b amsupport . d2342 5 a2346 2 For BTREES, this number is one \*- the routine to take two keys and return \(mi\^1, 0, or \(pl\^1, d2352 4 a2355 2 .b amstrategies entry in pg_am is just the number of strategies defined d2357 2 a2358 1 The procedures for less than, less equal, and so on don't appear in pg_am. d2360 1 a2360 1 .b amsupport d2365 6 a2370 3 .b pg_opclass. This class exists only to associate a name with an oid. In pg_amop, every BTREE operator class has a set of procedures, d2372 4 a2375 1 Some existing opclasses are int2_ops, int4_ops, and oid_ops. d2377 8 a2384 6 "int4_abs_ops") to pg_opclass. The oid of this instance is a foreign key in other classes. .(l .ft C * append pg_opclass (opcname = "int4_abs_ops") \eg d2386 2 a2387 5 * retrieve (cl.oid, cl.opcname) from cl in pg_opclass where cl.opcname = "int4_abs_ops" \eg d2389 1 a2389 2 tab (+) allbox; l l d2391 2 a2392 2 oid+opcname 17314+int4_abs_ops d2394 1 a2394 2 .ft .)l d2396 9 a2404 2 The oid for your pg_opclass instance MAY BE DIFFERENT! You should substitute your value for 17314 wherever it appears in this discussion. d2409 3 a2411 1 For the "int4_abs_ops" operator class on BTREES, d2414 5 a2418 7 .ft C absolute value less-than absolute value less-than-or-equal absolute value equal absolute value greater-than-or-equal absolute value greater-than .ft d2421 1 a2421 5 .(l .ft C /usr/postgres/tutorial/int4_abs.c .ft .)l d2423 1 a2423 2 .(l .ft C d2425 2 a2426 3 * int4_abs.c -- * absolute value comparison functions * for int4 data d2428 1 d2447 1 a2447 2 .ft .)l d2450 3 a2452 1 and greater for int4 are being defined. d2454 9 a2462 1 for int4 under the names "<", "<=", "=", ">=", and ">". d2471 6 a2476 1 That is, if you have "<" defined for (int4, int4), you can't define it again. d2486 3 a2488 1 The other important point is that all the functions return BOOLEAN values; d2490 1 a2490 2 .(l .ft C d2492 3 a2494 3 (language = "c", returntype = bool) arg is (int4, int4) as "/usr/postgres/tutorial/int4_abs.o" \eg d2497 3 a2499 3 (language = "c", returntype = bool) arg is (int4, int4) as "/usr/postgres/tutorial/int4_abs.o" \eg d2502 3 a2504 3 (language = "c", returntype = bool) arg is (int4, int4) as "/usr/postgres/tutorial/int4_abs.o" \eg d2507 3 a2509 3 (language = "c", returntype = bool) arg is (int4, int4) as "/usr/postgres/tutorial/int4_abs.o" \eg d2512 4 a2515 5 (language = "c", returntype = bool) arg is (int4, int4) as "/usr/postgres/tutorial/int4_abs.o" \eg .ft .)l d2518 8 a2525 7 the operator names must be unique for two int4 operands. You can do a query on pg_operator .(l .ft C * retrieve (pg_operator.all) \eg .ft .)l d2528 1 a2528 1 (which is the C function defined above) d2530 2 a2531 2 You should just use the ones used below -- note that there are different such functions for the less, d2533 1 a2533 1 and greater cases. d2538 4 a2541 1 You should copy the names for restrict and join, d2543 1 a2543 2 .(l .ft C d2545 3 a2547 4 (arg1 = int4, arg2 = int4, procedure=int4_abs_lt, associativity = left, restrict = intltsel, join = intltjoinsel) a2548 2 \\g d2550 3 a2552 6 (arg1 = int4, arg2 = int4, procedure=int4_abs_le, associativity = left, restrict = intltsel, join = intltjoinsel) \\g d2555 3 a2557 4 (arg1 = int4, arg2 = int4, procedure=int4_abs_eq, associativity = left, restrict = eqsel, join = eqjoinsel) a2558 2 \\g d2560 3 a2562 6 (arg1 = int4, arg2 = int4, procedure=int4_abs_ge, associativity = left, restrict = intgtsel, join = intgtjoinsel) \\g d2565 4 a2568 8 (arg1 = int4, arg2 = int4, procedure=int4_abs_gt, associativity = left, restrict = intgtsel, join = intgtjoinsel) \\g .ft .)l d2570 7 a2576 2 and greater equal are defined. We're just about finished. the last thing we need to do is to update the pg_amop relation. To do this, we need the d2578 32 a2609 14 .(l .ft C amopid: the oid of the pg_am instance for BTREE (== 400, see above); amopclaid: the oid of the pg_opclass instance for int4_abs_ops (== whatever you got instead of 17314, see above); amopopr: the oids of the operators for the opclass (which we'll get in just a minute); and d2611 4 a2614 3 amopnpages: cost functions. .ft .)l d2616 5 a2620 3 or not to use a given index in a scan. Fortunately, these already exist. The two functions we'll use are .b btreesel, d2623 1 a2623 1 .b btreenpage, d2627 8 a2634 5 So we need the oids of the operators we just defined. We'll look up the names of all the operators that take two int4's, and pick ours out. (Note that your numbers may be different.) .(l .ft C d2636 6 a2641 5 from o in pg_operator, t in pg_type where o.oprleft = t.oid and o.oprright = t.oid and t.typname = "int4" \\g d2643 1 a2643 2 tab (#) allbox; l l d2645 17 a2661 17 oid+oprname 96#\\= 97#< 514#* 518#!= 521#> 523#<= 525#>= 528#/ 530#% 551#+ 555#- 17321#<<& 17322#<=& 17323#==* 17324#>=& 17325#>>& d2663 8 a2670 3 .ft .)l The operators we are interested in are those with oids 17321 through 17325. d2672 1 a2672 1 them for the values I use below. d2677 4 a2680 1 Now we're ready to update pg_amop with our new operator class. The most d2682 12 a2693 2 ordered, from less equal through greater equal, in pg_amop. Recall that the BTREE instance's oid is 400 and int4_abs_ops is oid 17314. Then we d2695 1 a2695 2 .(l .ft C d2697 47 a2743 53 (amopid = "400"::oid, /* btree oid */ amopclaid = "17314"::oid, /* pg_opclass tup */ amopopr = "17321"::oid, /* <<& tup oid */ amopstrategy = "1"::int2, /* 1 is <<& */ amopselect = "btreesel"::regproc, amopnpages = "btreenpage"::regproc) \\g * append pg_amop (amopid = "400"::oid, amopclaid = "17314"::oid, amopopr = "17322"::oid, amopstrategy = "2"::int2, amopselect = "btreesel"::regproc, amopnpages = "btreenpage"::regproc) \\g * append pg_amop (amopid = "400"::oid, amopclaid = "17314"::oid, amopopr = "17323"::oid, amopstrategy = "3"::int2, amopselect = "btreesel"::regproc, amopnpages = "btreenpage"::regproc) \\g * append pg_amop (amopid = "400"::oid, amopclaid = "17314"::oid, amopopr = "17324"::oid, amopstrategy = "4"::int2, amopselect = "btreesel"::regproc, amopnpages = "btreenpage"::regproc) \\g * append pg_amop (amopid = "400"::oid, amopclaid = "17314"::oid, amopopr = "17325"::oid, amopstrategy = "5"::int2, amopselect = "btreesel"::regproc, amopnpages = "btreenpage"::regproc) \\g .ft .)l NOTE the order: "less" is 1, "less equal" is 2, "equal" is 3, "greater equal" is 4, and "greater" is 5. d2745 2 a2746 1 Okay, now it's time to test the new opclass. First we'll create and d2748 2 a2749 4 .(l .ft C * create pairs (name = char16, number = int4) \\g d2751 1 a2751 2 * append pairs (name = "mike", number = -10000) \\g d2753 1 a2753 2 * append pairs (name = "greg", number = 3000) \\g d2755 1 a2755 2 * append pairs (name = "lay peng", number = 5000) \\g d2757 1 a2757 2 * append pairs (name = "jeff", number = -2000) \\g d2759 1 a2759 2 * append pairs (name = "mao", number = 7000) \\g d2761 1 a2761 3 * append pairs (name = "cimarron", number = -3000) \\g d2763 1 a2763 3 * retrieve (pairs.all) \\g d2765 1 a2765 2 tab (+) allbox; l l d2767 7 a2773 7 name+number mike+-10000 greg+3000 lay peng+5000 jeff+-2000 mao+7000 cimarron+-3000 d2775 4 a2778 5 .ft .)l okay, looks pretty random. Define an index using the new opclass: .(l .ft C d2780 10 a2789 7 using btree (number int4_abs_ops) \\g .ft .)l Now run a query that doesn't use one of our new operators. What we're trying to do here is to run a query that *won't* use our index, so that we can tell the difference when we see a query that *does* use the index. d2792 2 a2793 5 .(l .ft C * retrieve (pairs.all) where pairs.number < 9000 \\g d2795 1 a2795 2 tab (+) allbox; l l d2797 7 a2803 7 name+number mike+-10000 greg+3000 lay peng+5000 jeff+-2000 mao+7000 cimarron+-3000 d2805 3 a2807 3 .ft .)l Yup, just as random; that didn't use the index. Okay, let's run a query d2811 2 a2812 4 .(l .ft C * retrieve (pairs.all) where pairs.number <<& 9000 \\g d2814 1 a2814 2 tab (+) allbox; l l d2816 6 a2821 6 name+number jeff+-2000 cimarron+-3000 greg+3000 lay peng+5000 mao+7000 d2823 4 a2826 3 .ft .)l Note that the "number" values are in order of increasing absolute value d2828 3 a2830 113 we got the right answer -- the instance for "mike" doesn't appear, because -10000 >=& 9000 .sh 1 "The \*(PP Rule System" .lp The discussion in this section is intended to provide an overview of the \*(PP rule system and point the user at helpful references and examples. \*(PP actually has two rule systems, the Instance-level Rule System and the Query Rewrite Rule System. .sh 2 "The Instance-level Rule System" .lp The Instance-level Rule System uses markers placed in each instance in a class to "trigger" rules. Examples of the Instance-level Rule System are explained and illustrated in ~postgres/demo, which is included with the \*(PP distribution. Additional discussion of the Instance-level Rule System can be found in the Reference in "define rule". The theoretical foundations of the \*(PP rule system can be found in [STON90]. .sh 2 "The Query Rewrite Rule System" .lp The Query Rewrite Rule System modifies queries to take rules into consideration, and then passes the modified query to the query optimizer for execution. It is very powerful, and can be used for many things such as query language procedures, views, and versions. Examples and discussion can be found in the demo in ~postgres/video, and further discussion is in the Reference under "define rule". The power of this rule system is discussed in [ONG90] and [STON90]. .sh 2 "When to use either?" .lp Since each rule system is architected quite differently, they work best in different situations. The Query Rewrite system is best when rules affect most of the instances in a class, while the Instance-level system is best when a rule affects only a few instances. .sh 1 "Administering \*(PP" .lp In this section, we will discuss aspects of \*(PP of interest to those making extensive use of \*(PP, or who are the database administrator for a group of \*(PP users. .sh 2 "User administration" .lp The .b createuser and .b destroyuser commands create and destroy \*(PP users. Please read the "man pages" on these commands in the Reference for specifics on their use. .sh 2 "Moving database directories out of ~postgres/data/base" .lp If you do not want all users to have databases in ~postgres/data/base/, you can put their data directories in arbitrary places by using the following mechanism: .lp .b o Create a database using the .b createdb command. .lp .b o Move the directory ~postgres/data/base/ to its ultimate destination. It should still be owned by the "postgres" user. .lp .b o Make a symbolic link from ~postgres/data/base to the new directory. .sh 2 "Troubleshooting \*(PP" .lp Occasionally, \*(PP will fail with cryptic error messages that are due to relatively simple problems. The following are a list of \*(PP error messages and the likely fix. These messages are ones you would likely see in the monitor program. .(l .ft C Message: semget: No space left on device Explanation and Likely Fix: .ft .)l Either the kernel has not been configured for System V shared memory, or some other program is using it up. On most machines, the \*(UU command "ipcs" will show shared memory and semaphore usage. To delete all shared memory and semaphores (may be necessary if a backend fails), run the "ipcclean" command. .lp Note, however, that the "ipcclean" command deletes all semaphores belonging to the user running it, so the user should be certain that no non-\*(PP processes are using semaphores before running this command. .(l .ft C Message: Unable to get shared buffers Explanation and Likely Fix: .ft .)l This message means that a \*(PP backend was expecting shared memory to be available and it was not. Usually this is due to "ipcclean" being run while a "postmaster" was also running. .b NOTE: "ipcclean" will delete ALL semaphores and shared memory .i whether .i they .i are .i being .i used .i or .i not. .(l .ft C Message: Can't connect to the backend (...) Explanation and Likely Fix: .ft .)l This message means that you are running a \*(LP application but it could not link up with a postmaster. If you see this error message, you should see if a postmaster is truly running. If one is running, the problem is likely related to your network. @ 1.9 log @no message @ text @d1 1 d3 1 a3 1 .\" $Header: /home/postgres/mer/docs/RCS/manual.me,v 1.8 1992/06/16 01:04:31 mer Exp mer $ a4 67 .\" Start of generic definitions section. .\" .\" Defines .SB, .SE, .SP, .SS, .XA, .XP. .\" Uses constant IW (TOC indent width). .\" Uses temporary TI (TOC total indent). .\" .\" Usage: .\" .\" .SB .\" begin section formatting (Section formatting is *on* by default.) .\" .\" .SE .\" end section formatting .\" .\" .SP title section data .\" start page with given title in given section last modified on date .\" .\" .SS section data .\" start section with data of last modification .\" .\" .XA indent title [page number("_" for no number/dots)] .\" add title to TOC with specified indent .\" .\" .XP .\" print TOC .\" .\" Example: .\" .\" .SP CREATEDB UNIX 5/4/88 .\" .\" .XA 0 Introduction .\" .XA 1 "Section 1" _ .\" .XA 2 "Page 1.1" .\" .XP .\" .de SB .nr si .5i \" 1/2 inch relative base indent per section depth .nr so -.5i \" -1/2 inch additional section title offset .nr $0 1 \" section depth of 1 .nr $i \\n(si*\\n($0 \" paragraph base indent .. .de SE .nr si 0 .nr so 0 .nr $0 0 .nr $i \\n(si*\\n($0 .in \\n($i+\\n(po .. .de SP .he '\fB\\$1 (\\$2)'\\$3'\\$1 (\\$2)\fR' .bp .. .de SS .he '\fB\\$1'\\$2'\\$1\fR' .bp .. .de XA .nr TI \\n(IW*\\$1 .(x t .ti +\\n(TIu \\$2 .)x \\$3 .. .de XP .xp t .. .\" d7 2 a8 6 .\" Defines .A. .\" .\" Usage: .\" .\" .A acronym concatenation .\" display acronym in standard acronym format with concatenation a9 8 .\" Defines II, PP, UU, LL, PQ, LQ, OF, AM, PV +- for .\" INGRES, POSTGRES, UNIX, LISP, \*(PQ, LIBPQ, PICASSO, "AM Guide," 4.0 +-. .\" .if t \ \{\ .de A \\s-1\\$1\\s0\\$2 .. a12 1 . ds LL \s-1LISP\s0 d16 1 a16 19 .\} .if n \ \{\ .de A \\$1\\$2 .. . ds II INGRES . ds PP POSTGRES . ds UU UNIX . ds LL LISP . ds PQ \*(PQ . ds LP LIBPQ . ds PV "4.0 .\} .\" . ds OF Picasso . ds AM \*(PP Access Method Implementors' Guide .if !\n(.V .ds +- +- .if \n(.V .ds +- \(+- a19 3 .nr IW 3n \" 3en table of contents indentation .\" .\" .nr tp 10 \" standard title point size a20 2 .\" .nr fp 8 \" standard footnote pointsize .\" .nr pi 5n \" standard paragraph indent d26 2 a27 1 .SB d33 1 a33 1 d37 1 a37 1 Edited by Jon Rhein, Greg Kemnitz and The Postgres Group d40 1 a40 1 .sp d52 1 a53 3 .sh 2 "DISTRIBUTION" a57 1 d59 1 a59 1 d63 1 a63 2 Ingres, a relational prototype from the late 1970's. d65 1 a65 1 a78 1 .lp d81 1 a81 1 The POSTGRES project undertook to build a next generation d89 1 a89 1 and attempts to provide an accessible path into using the system. a92 1 .lp a108 1 .lp d110 4 a113 4 classes inheritance types functions d115 1 a115 2 .lp The \*(PP DBMS has been under construction since 1986. a125 1 .lp d139 1 a139 1 command in a format similar to that used in Unix "man pages". a145 1 .lp a150 1 .lp a156 1 .lp d159 1 a159 1 % createdb foo d172 1 a172 1 interact with \*(PP from a C program by using the "libpq" d182 1 a182 1 allows you to directly execute functions stored in the database. d184 2 a185 2 Lastly, \*(PP is accessible from the PICASSO programming environment. PICASSO is a graphical user interface (GUI) toolkit d187 1 a187 1 applications. PICASSO is a separate research project and is described d195 1 a195 1 % monitor a197 1 .lp a198 1 .lp d201 1 a201 2 Welcome to the POSTGRES terminal monitor d203 2 a204 2 Go * a206 1 .lp a211 1 .lp d214 1 a214 2 * \\p a217 1 .lp d220 1 a220 2 * \\g d227 1 a227 2 * \\e d232 1 a232 1 vi, type d235 1 a235 2 % man vi d239 1 a239 1 buffer and you can submit it to postgres by using the "\\g" command d242 1 a242 1 To get out of the monitor and return to Unix, type d245 1 a245 2 * \\q a248 1 .lp d251 1 a251 2 I live to serve you. d253 1 a253 1 % d257 1 a257 1 on "monitor" in the Unix section of the Reference. d260 1 a260 2 can destroy it using the following UNIX command: .lp d263 1 a263 2 % destroydb foo d270 1 a270 2 which are discussed further in the Unix section of the Reference. .lp a271 1 .lp d274 1 a274 1 In order to begin using \*(PP, create the a282 1 .lp a286 1 .lp d289 2 d292 1 a292 4 * create EMP (name=text, salary=int4, age=int4, dept=char16) \g * create DEPT (dname=char16, floor=int4, a301 1 a305 1 a318 1 .lp a323 1 .lp a325 1 a336 1 a344 1 a349 1 d352 1 a352 1 a357 1 a362 1 d365 1 a365 1 where E1.age=E2.age d376 2 a377 2 for the cartesian product of the classes indicated in the query. For those instances in the cartesian product for which the a379 1 .lp a381 1 a385 1 d393 1 a393 2 This command replaces the salary of Joe by that of Sam. a395 1 a397 1 a399 1 a404 1 .lp a412 1 d419 1 a419 1 field a427 1 d429 1 a429 1 pay_by_quarter = "{10000, 10000, 10000, d434 1 a434 1 pay_by_quarter = "{10000, 15000, 15000, d439 1 a439 1 pay_by_quarter = "{20000, 25000, 25000, a448 1 a459 1 d468 1 a468 2 * delete SAL_EMP d481 1 a481 2 * create manager (name = char16, d485 1 a485 1 employees = "{"wei", "greg", d496 1 a496 1 the a500 1 a514 1 .lp a516 1 a523 1 d526 1 a526 2 Starting with the d534 1 a534 2 * create STUD_EMP (location=point) d548 1 a548 1 from its parent, EMP, to wit: name, salary, age, and dept. d552 1 a552 1 the inheritance hierarchy is generally a directed acyclic graph. a556 1 .lp d559 1 a559 2 * retrieve (E.name) from E in EMP d569 1 a569 2 * retrieve (E.name) from E in EMP* a582 1 .lp a584 1 a589 1 a602 1 a613 1 a620 1 .lp a625 1 .lp d635 1 a635 1 function, which is assumed to return a collection of instances. a637 1 .lp a639 1 a647 1 .lp a649 1 a659 1 .lp a661 1 a667 1 a682 1 d687 1 a687 1 where EMP.name=DEPT.manager a696 1 d705 1 a705 1 referencing into it with a second use of the dot notation. a715 1 d720 1 a720 1 where E.name=DEPT.manager and d733 1 a733 1 attribute (except for direct appends - that is, a739 1 a748 1 a754 1 .lp d760 2 a761 2 a binary object file created by the user. This allows \*(PP to call arbitrary user functions which can be written in a standard programming d772 1 a772 2 .lp .sh 2 "Internal storage of types" a778 1 .lp a794 1 .lp d797 1 a797 1 In this discussion, we will be defining a a799 1 .lp a807 1 a808 1 a824 1 d840 1 a840 1 glance to be a correct type and explain why it a846 1 d857 1 a857 1 work because a864 1 .lp d887 1 a887 1 passed a pointer to type CIRCLE as input and returns a string. a891 1 a895 1 .lp a906 1 a921 1 .lp a928 1 d931 1 a931 1 output=circle_out) d936 1 a936 1 where a941 1 a949 1 a950 1 d953 1 a953 1 on your type. If a959 1 d965 1 a965 1 and redefine the circle type. Note that we would a967 1 .lp d974 1 a974 1 operators. To do this, we make use of the concept of a982 1 a994 1 a1001 1 .lp a1006 1 d1022 1 a1022 1 where tutorial.a = a1046 1 .lp d1049 1 a1049 1 The following is the source code for circle.c. a1051 1 a1133 1 .lp d1137 1 a1137 1 must use the memory allocator "palloc" and avoid standard Unix memory managers a1141 1 a1143 1 d1148 1 a1148 1 manner as the Unix "free" library call: a1150 1 a1152 1 a1155 1 .lp d1161 1 a1161 1 done automatically when you edit or re-compile the file, but a1167 1 d1184 1 a1184 1 In using a function of an instance in qualifying an instance, a1192 1 a1198 1 d1200 1 a1200 1 overpaid() /* note that there are no a1201 1 a1202 1 d1207 1 a1207 1 seniority=(short) d1209 1 a1209 1 performance=(short) d1212 1 a1212 1 return (salary > a1232 1 a1244 1 d1248 1 a1248 1 as "/usr/postgres/tutorial/overpaid.o" d1256 2 a1257 2 Your function may only access one instance via the .b GetAttribute a1261 1 a1268 1 a1278 1 .lp d1283 1 a1283 1 \*(PP a1293 1 a1301 1 d1303 1 a1303 1 (list = "{"(1.0, 1.0, 5.0)", d1309 1 a1309 1 (list = "{"(2.0, 3.0, 15.0)", d1313 1 a1313 1 * append circles a1316 1 a1321 1 d1337 1 a1337 1 of arrays". a1346 1 d1349 1 a1349 1 internallength = variable, d1361 1 a1361 1 indicates that it is a variable length attribute. d1363 1 a1363 1 that any type using a1372 1 a1382 1 d1386 1 a1386 1 (a = "{{1, 2, 3} , {4, 5}, d1391 1 a1391 1 * append stuff (a = "{{5, 4, 3} , d1400 1 a1400 1 * retrieve (s.all) a1406 1 .lp a1425 1 .lp a1427 1 a1435 1 .lp a1437 1 a1449 1 d1458 2 a1459 2 ... figure out how long the stats array should be d1482 1 a1482 1 populate the stats array with a1489 1 .lp d1492 2 a1493 2 Now, using the .b define a1498 1 .lp a1500 1 a1503 1 .lp a1506 1 .lp a1508 1 a1523 1 .lp a1524 1 .lp a1526 1 d1528 1 a1528 1 (internallength = 16, d1530 1 a1530 1 output = quarterly_out, d1532 1 a1532 1 a1535 1 .lp a1536 1 .lp a1538 1 a1553 1 .lp a1555 1 .lp a1557 1 a1566 1 .lp a1567 1 .lp a1569 1 d1597 1 a1597 1 that when you use your own functions to input and output array types, d1605 1 a1605 1 and is d1614 1 a1614 1 Unix file system interface; the particulars of this interface are detailed in a1621 1 .lp d1627 1 a1627 1 and a1634 1 d1657 1 a1657 1 Large objects are a1662 1 a1680 1 .lp d1685 1 a1685 1 a large object, they a1690 1 d1696 1 a1696 1 d1701 1 a1701 1 to large objects, a1705 1 .lp a1731 1 .lp a1733 1 a1754 1 .lp a1758 1 .lp d1761 2 a1763 3 * retrieve (pg_am.oid) where pg_am.amname = "btree" a1774 1 .lp a1795 1 .lp a1809 1 .lp d1826 1 a1826 1 These operations do not correspond to user qualifications in a1862 1 .lp a1864 1 a1881 1 .lp a1892 1 a1897 1 a1900 1 .lp a1906 1 .lp a1908 1 d1910 1 a1910 1 * int4_abs.c -- a1933 1 .lp d1948 1 a1948 1 \*(PP d1952 1 a1952 1 To avoid this problem, a1958 1 .lp a1960 1 a1986 1 .lp a1992 1 d2004 1 a2004 1 These a2009 1 .lp a2011 1 a2052 1 .lp a2056 1 .lp a2058 1 a2074 1 .lp d2083 1 a2083 1 pages a search will touch in the tree. a2087 1 .lp a2089 1 a2131 1 .lp a2133 1 d2136 1 a2136 1 amopclaid = "17314"::oid, /* a2185 1 .lp a2190 1 .lp a2192 1 d2211 1 a2211 1 * append pairs (name = "cimarron", a2231 1 .lp a2232 1 .lp a2234 1 a2239 1 .lp a2244 1 .lp d2247 1 a2247 2 * retrieve (pairs.all) a2263 1 .lp d2265 1 a2265 1 that a2267 1 .lp a2269 1 a2284 1 .lp a2288 1 .lp d2294 1 a2294 2 Query Rewrite Rule System. .lp d2302 1 a2302 2 of the \*(PP rule system can be found in [STON90]. .lp a2317 1 .lp d2321 1 a2321 1 making extensive use of \*(PP, or who are the database administrator for d2329 1 a2329 1 commands create and destroy \*(PP users. Please read the "man pages" on these a2347 1 .lp a2353 1 .lp a2360 1 .lp d2362 1 a2362 1 other program is using it up. On most machines, the UNIX command "ipcs" will a2368 1 .lp a2370 1 a2375 1 .lp d2378 1 a2378 1 "postmaster" was also running. d2381 1 a2381 1 and shared memory a2388 1 .lp a2390 1 d2396 1 a2396 2 .lp This message means that you are running a LIBPQ application but it could @ 1.8 log @oops, I checked in the wrong thing last time (major rework + lie fixes) @ text @d2 1 a2 1 .\" $Header: /home/postgres/mer/docs/RCS/manual.me,v 1.6 1992/06/12 00:05:50 hong Exp mer $ d174 1 a174 1 Ron Choi, Adam Glass, Jeffrey Goh, Joey Hellerstein, Wei Hong, @ 1.7 log @major rework + lie fixes @ text @d183 1 a183 1 this manual, as did Claire Mosher and Chandra Gosh. d422 5 a426 4 * create EMP (name=text, salary=int4, age=int4, dept=char16) \\g * create DEPT (dname=char16, floor=int4) \\g d442 1 a442 1 age=40,dept="shoe") d446 1 a446 1 age=29,dept="toy") d450 1 a450 1 age=36,dept="candy") \\g d508 2 a509 1 where E1.age=E2.age and E1.name != E2.name d566 1 a566 1 pay_by_quarter = int4[4]) d582 2 a583 1 pay_by_quarter = "{10000, 10000, 10000, 10000}") d587 2 a588 1 pay_by_quarter = "{10000, 15000, 15000, 15000}") d592 2 a593 1 pay_by_quarter = "{20000, 25000, 25000, 25000}") d624 2 a625 1 * delete SAL_EMP where SAL_EMP.name[1] = 'j' d638 2 a639 1 * create manager (name = char16, employees = text[]) \\g d642 2 a643 1 employees = "{"wei", "greg", "jeff"}") \\g d646 1 a646 1 employees = "{"bill", "joe"}") \\g d649 1 a649 1 employees = "{"mike", "alice"}") \\g d697 2 a698 1 * create STUD_EMP (location=point) inherits (EMP) d724 2 a725 1 * retrieve (E.name) from E in EMP where E.age > 39 d735 2 a736 1 * retrieve (E.name) from E in EMP* where E.age > 39 d774 2 a775 1 from E in EMP["Jan 1 00:00:00 1970 GMT","now"] d816 1 a816 1 (language = "postquel", returntype = EMP) d828 1 a828 1 (language = "postquel", returntype = EMP) d868 2 a869 1 where EMP.name=DEPT.manager and DEPT.name=$1 d900 1 a900 1 (language="postquel", returntype = EMP) d903 2 a904 1 where E.name=DEPT.manager and DEPT.name=EMP.dept d1000 1 d1093 1 a1093 1 .b equal_area_circle, d1106 1 a1106 1 (language = "c", returntype=circle) d1113 1 a1113 1 (language = "c", returntype=char16) d1130 2 a1131 2 (internallength=24, input=circle_in, output=circle_out) d1144 1 a1144 1 3 * sizeof(double) = 3 * 8 = 24 d1153 1 d1189 2 a1190 2 * define function equal_area_circle (language = "c", returntype=bool) d1203 2 a1204 2 (arg1=circle, arg2=circle, procedure=equal_area_circle) d1219 2 a1220 1 * append tutorial(a="(1.0,1.0,10.0)"::circle) d1222 2 a1223 1 * append tutorial(a="(2.0,2.0,5.0)"::circle) d1225 2 a1226 1 * append tutorial(a="(0.0,1.8,10.0)"::circle) d1231 2 a1232 1 where tutorial.a = "(0.0,0.0,10.0)"::circle a1237 1 d1239 3 a1241 2 |c| |l|. d1420 2 a1421 1 overpaid() /* note that there are no arguments */ d1429 4 a1432 2 seniority=(short) GetAttribute("seniority"); performance=(short) GetAttribute("performance"); d1434 2 a1435 1 return (salary > (seniority * performance)); d1470 1 a1470 1 (language = "c", returntype=bool) d1532 2 a1533 1 (list = "{"(1.0, 1.0, 5.0)", "(2.0, 2.0, 10.0)"}") d1538 2 a1539 1 (list = "{"(2.0, 3.0, 15.0)", "(2.0, 2.0, 10.0)"}") d1542 2 a1543 1 * append circles (list = "{"(2.0, 3.0, 4.0)"}") d1580 3 a1582 2 (input = array_in, output = array_out, internallength = variable, element = int4) d1607 4 a1610 4 (input = array_in, output = array_out, internallength = variable, element = int_array) \\g d1620 2 a1621 1 (a = "{{1, 2, 3} , {4, 5}, {6, 7, 8}}") \\g d1625 2 a1626 1 * append stuff (a = "{{5, 4, 3} , {2, 2}}") \\g d1698 2 a1699 1 ... figure out how long the stats array should be d1708 1 a1708 1 sizeof(long)); d1711 1 a1711 1 * in bytes, INCLUDING ITSELF d1722 2 a1723 1 populate the stats array with numbers from s d1756 1 a1756 1 (language="c", returntype=quarterly) d1763 1 a1763 1 (language = "c", returntype = char16) d1777 4 a1780 2 (internallength = 16, input = quarterly_in, output = quarterly_out, element = int4) d1792 1 a1792 1 (language = "c", returntype = stats) d1799 1 a1799 1 (language = "c", returntype = char16) d1828 1 a1828 1 * create test (a = quarterly, b = stats) \\g d1832 2 a1833 1 b = "5 6 7"::stats) \\g d1836 5 a1840 1 b = "6 4"::stats) \\g d1843 9 a1851 7 where test.a[4] = test.b[2] \\g ------------------ | a | b | ------------------ | 1 3 2 4 | 6 4 | ------------------ d1896 1 a1896 1 (language = "c", returntype = map) d1903 1 a1903 1 (language = "c", returntype = char16) d1910 2 a1911 2 (input = large_in, output = large_out, internallength = variable) d1927 1 a1927 1 (name = "earth", d1933 1 a1933 1 (name = "moon", d2035 7 a2041 5 --------------- | oid | --------------- | 403 | --------------- d2069 10 a2078 7 .ta 0.5i 2.2i less than 1 less than or equal 2 equal 3 greater than or equal 4 greater than 5 d2147 7 a2153 5 ----------------------------- | oid | opcname | ----------------------------- | 17314 | int4_abs_ops| ----------------------------- d2174 1 d2199 1 a2199 1 { ABS(a); ABS(b); return (a < b); } d2202 1 a2202 1 { ABS(a); ABS(b); return (a <= b); } d2205 1 a2205 1 { ABS(a); ABS(b); return (a == b); } d2208 1 a2208 1 { ABS(a); ABS(b); return (a >= b); } d2211 1 a2211 1 { ABS(a); ABS(b); return (a > b); } d2245 1 a2245 1 (language = "c", returntype = bool) d2250 1 a2250 1 (language = "c", returntype = bool) d2255 1 a2255 1 (language = "c", returntype = bool) d2260 1 a2260 1 (language = "c", returntype = bool) d2265 1 a2265 1 (language = "c", returntype = bool) d2300 4 a2303 4 (arg1 = int4, arg2 = int4, procedure=int4_abs_lt, associativity = left, restrict = intltsel, join = intltjoinsel) d2308 4 a2311 4 (arg1 = int4, arg2 = int4, procedure=int4_abs_le, associativity = left, restrict = intltsel, join = intltjoinsel) d2316 4 a2319 4 (arg1 = int4, arg2 = int4, procedure=int4_abs_eq, associativity = left, restrict = eqsel, join = eqjoinsel) d2324 4 a2327 4 (arg1 = int4, arg2 = int4, procedure=int4_abs_ge, associativity = left, restrict = intgtsel, join = intgtjoinsel) d2332 4 a2335 4 (arg1 = int4, arg2 = int4, procedure=int4_abs_gt, associativity = left, restrict = intgtsel, join = intgtjoinsel) d2389 22 a2410 37 ----------------------------- | oid | oprname | ----------------------------- | 96 | = | ----------------------------- | 97 | < | ----------------------------- | 514 | * | ----------------------------- | 518 | != | ----------------------------- | 521 | > | ----------------------------- | 523 | <= | ----------------------------- | 525 | >= | ----------------------------- | 528 | / | ----------------------------- | 530 | % | ----------------------------- | 551 | + | ----------------------------- | 555 | - | -----------------------------_ | 17321 | <<& | \e ----------------------------- \e these are the ones | 17322 | <=& | \e we want-- the values ----------------------------- \e you get for oids will | 17323 | ==& | |probably be different, ----------------------------- / and you should substitute | 17324 | >=& | / them for the values i use ----------------------------- / below. | 17325 | >>& | / ------------------------------ d2413 3 d2430 3 a2432 2 (amopid = "400"::oid, /* btree oid */ amopclaid = "17314"::oid, /* pg_opclass tup */ d2509 2 a2510 1 * append pairs (name = "cimarron", number = -3000) d2516 12 a2527 15 ----------------------------- | name | number | ----------------------------- | mike | -10000 | ----------------------------- | greg | 3000 | ----------------------------- | lay peng | 5000 | ----------------------------- | jeff | -2000 | ----------------------------- | mao | 7000 | ----------------------------- | cimarron | -3000 | ----------------------------- d2551 2 a2552 1 * retrieve (pairs.all) where pairs.number < 9000 d2554 12 a2565 16 ----------------------------- | name | number | ----------------------------- | mike | -10000 | ----------------------------- | greg | 3000 | ----------------------------- | lay peng | 5000 | ----------------------------- | jeff | -2000 | ----------------------------- | mao | 7000 | ----------------------------- | cimarron | -3000 | ----------------------------- d2579 11 a2589 14 ----------------------------- | name | number | ----------------------------- | jeff | -2000 | ----------------------------- | cimarron | -3000 | ----------------------------- | greg | 3000 | ----------------------------- | lay peng | 5000 | ----------------------------- | mao | 7000 | ----------------------------- @ 1.6 log @fixed the arrays of user-defined types section @ text @d2 1 a2 1 .\" $Header: /mnt/hong/pghome/doc/RCS/manual.me,v 1.5 1992/06/11 22:42:13 mer Exp hong $ d120 1 a120 1 .\" .nr sp 10 \" standard section title point size d173 1 a173 1 alphabetical order) consisted of James Bell, Jennifer Caetta, Jolly Chen d183 1 a183 1 this manual, as did Claire Mosher. d218 4 a221 4 classes inheritance types functions d271 3 a273 1 % createdb foo d307 3 a309 3 % monitor d315 1 d317 1 a317 1 Welcome to the POSTGRES terminal monitor d319 3 a321 3 Go * d331 1 d333 2 a334 2 * \\p d339 1 d341 2 a342 2 * \\g d347 1 d349 2 a350 2 * \\e d356 1 d358 2 a359 2 % man vi d367 1 d369 2 a370 2 * \\q d375 1 d377 1 a377 1 I live to serve you. d379 2 a380 2 % d389 1 d391 2 a392 2 % destroydb foo d420 1 d422 2 a423 1 * create EMP (name=text, salary=int4, age=int4, dept=char16) \\g d425 2 a426 2 * create DEPT (dname=char16, floor=int4) \\g d438 1 d440 7 a446 1 * append EMP (name="Joe",salary=1400,age=40,dept="shoe") \\g d448 3 a450 4 * append EMP (name="Sam",salary=1200,age=29,dept="toy") \\g * append EMP (name="Bill",salary=1600,age=36,dept="candy") \\g d461 1 d463 1 a463 1 * retrieve (EMP.name) where EMP.age<35 d465 2 a466 2 \\g d473 1 d475 2 a476 2 * retrieve (result=EMP.salary/EMP.age) where EMP.name="Bill" d478 2 a479 2 \\g d488 1 d490 2 a491 2 * retrieve into temp (EMP.name) where EMP.age<35 and EMP.salary>1000 d493 2 a494 2 \\g d503 1 d505 3 a507 3 * retrieve (E1.name, E2.name) from E1 in EMP, E2 in EMP where E1.age=E2.age and E1.name != E2.name d509 2 a510 2 \\g d528 1 d530 3 a532 3 * replace EMP (salary=E.salary) from E in EMP where EMP.name="Joe" and E.name="Sam" d534 2 a535 2 \\g d546 1 d548 1 a548 1 * delete EMP where EMP.salary > 0 d550 2 a551 2 \\g d561 1 d563 4 a566 2 * create SAL_EMP (name = char[], pay_by_quarter = int4[4]) \\g d577 1 d579 12 a590 12 * append SAL_EMP (name = "bill", pay_by_quarter = "{10000, 10000, 10000, 10000}") \\g * append SAL_EMP (name = "jack", pay_by_quarter = "{10000, 15000, 15000, 15000}") \\g * append SAL_EMP (name = "joe", pay_by_quarter = "{20000, 25000, 25000, 25000}") \\g d596 1 d598 3 a600 3 * retrieve (SAL_EMP.name) where SAL_EMP.pay_by_quarter[1] != SAL_EMP.pay_by_quarter[2] d602 2 a603 2 \\g d608 1 d610 1 a610 1 * retrieve (SAL_EMP.pay_by_quarter[3]) d612 2 a613 2 \\g d617 1 d619 1 a619 1 * delete SAL_EMP where SAL_EMP.name[1] = 'j' d621 2 a622 2 \\g d630 1 d632 1 a632 1 * create manager (name = char[], employees = text[]) \\g d634 2 a635 2 * append manager (name = "mike", employees = "{"wei", "greg", "jeff"}") \\g d637 2 a638 2 * append manager (name = "alice", employees = "{"bill", "joe"}") \\g d640 3 a642 3 * append manager (name = "marge", employees = "{"mike", "alice"}") \\g d649 1 d651 2 a652 2 * retrieve (manager.name) where manager.employees[1][1] = 'b' \\g d654 2 a655 2 * retrieve (manager.name) where manager.employees[3] = "jeff" \\g d657 1 a657 1 * retrieve (manager.employees[3][2]) \\g d659 3 a661 2 * delete manager where manager.employees[2][1] = 'g' \\g d687 1 d689 1 a689 1 * create STUD_EMP (location=point) inherits (EMP) d691 1 a691 1 \\g d693 4 a696 2 * append STUD_EMP (name = "Sunita", age = 41, salary=1300, dept="electronics", location="(3,5)") d698 2 a699 2 \\g d713 1 d715 1 a715 1 * retrieve (E.name) from E in EMP where E.age > 39 d717 2 a718 2 \\g d723 1 d725 1 a725 1 * retrieve (E.name) from E in EMP* where E.age > 39 d727 2 a728 2 \\g d746 1 d748 3 a750 1 * retrieve (E.salary) from E in EMP["now"] where E.name="Sam" d752 2 a753 2 \\g d760 1 d762 3 a764 3 * retrieve (E.salary) from E in EMP["Jan 1 00:00:00 1970 GMT","now"] where E.name = "Sam" d766 2 a767 2 \\g d771 1 d773 3 a775 1 * retrieve (E.salary) from E in EMP[,] where E.name = "Sam" d777 2 a778 2 \\g d801 1 d803 3 a805 4 * define function high_pay (language = "postquel", returntype = EMP) as retrieve (EMP.all) where EMP.salary>50000 d807 2 a808 2 \\g d813 1 d815 4 a818 4 * define function large_pay (language = "postquel", returntype = EMP) arg is (int4) as retrieve (EMP.all) where EMP.salary>$1 d820 2 a821 2 \\g d834 1 d836 1 a836 1 * addattr (manager=EMP) to EMP d838 2 a839 2 \\g d850 1 d852 5 a856 4 * define function mgr_lookup (language = "postquel", returntype = EMP) arg is (char16) as retrieve (EMP.all) where EMP.name=DEPT.manager and DEPT.name=$1 d858 2 a859 2 \\g d864 1 d866 3 a868 3 * append to EMP (name="Sam",salary=1000.0 ,age=40,dept="shoe", manager=mgr_lookup("shoe")) d870 2 a871 2 \\g d884 1 d886 5 a890 4 * define function lookup_mgr (language="postquel", returntype = EMP) arg is (EMP) as retrieve (E.all) from E in EMP where E.name=DEPT.manager and DEPT.name=EMP.dept d892 2 a893 2 \\g d908 1 d910 2 a911 1 * retrieve (EMP.name) where EMP.manager.name="Joe" d913 2 a914 2 \\g d918 1 d920 2 a921 1 * retrieve (EMP.name) where lookup_mgr(EMP).name="Joe" d923 2 a924 2 \\g d983 1 d985 2 a986 2 (center_x, center_y, radius) d1001 1 d1003 5 a1007 5 typedef struct { double x,y; } POINT; d1009 7 a1015 7 typedef struct { POINT center; double r; } CIRCLE; d1024 1 d1026 7 a1032 6 typedef struct { POINT *center double r; } CIRCLE; d1071 1 d1073 3 a1075 4 CIRCLE *circle, *circle_in(); char *circle_out(); circle=circle_in(circle_out(circle_in("(2.0,3.0,5.0)"))); d1088 1 a1088 3 * define C function circle_in (file="/usr/postgres/tutorial/circle.o", returntype=circle) arg is (char16) d1090 11 a1100 1 * \\g d1102 2 a1103 6 * define C function circle_out (file="/usr/postgres/tutorial/circle.o", returntype=char16) arg is (circle) * \\g d1112 1 d1114 3 a1116 2 * define type circle (internallength=24, input=circle_in, output=circle_out) d1118 2 a1119 2 * \\g d1126 1 d1128 3 a1130 2 sizeof(circle) = 3 * sizeof(double) = 3 * 8 = 24 d1135 1 d1137 2 a1138 2 printf("size is %d\n", sizeof (mystruct)); d1146 1 d1148 1 a1148 1 * remove type circle d1150 2 a1151 2 * \\g d1171 1 d1173 4 a1176 3 * define C function equal_area_circle (file="/usr/postgres/tutorial/circle.o", returntype=bool) arg is (circle,circle) d1178 2 a1179 2 * \\g d1184 1 d1186 3 a1188 2 * define operator = (arg1=circle,arg2=circle,procedure=equal_area_circle) d1190 2 a1191 2 * \\g d1198 1 d1200 2 a1201 2 * create tutorial(a=circle) * \\g d1203 6 a1208 4 * append tutorial(a="(1.0,1.0,10.0)"::circle) * append tutorial(a="(2.0,2.0,5.0)"::circle) * append tutorial(a="(0.0,1.8,10.0)"::circle) * \\g d1211 3 a1213 3 * retrieve (tutorial.all) where tutorial.a = "(0.0,0.0,10.0)"::circle * \\g d1217 1 d1219 8 a1226 8 ---------------- | a | ---------------- |(1.0,1.0,10.0)| ---------------- |(0.0,1.8,10.0)| ----------------- d1241 1 d1272 4 a1275 1 for (i = 0, p = str; *p && i < NARGS && *p != RDELIM; p++) d1278 1 d1302 5 a1306 2 sprintf(result, "(%g,%g,%g)", circle->center.x, circle->center.y, circle->radius); d1317 4 a1320 1 return (point_dt(point, &circle->center) < circle->radius ); d1322 1 a1322 1 d1333 4 a1336 2 char *palloc(size) d1338 2 a1339 2 unsigned long size; d1344 1 a1344 2 void pfree(ptr) d1346 2 a1347 1 char *ptr; d1349 2 d1364 1 d1366 1 a1366 1 * load "/usr/postgres/tutorial/circle.o" d1368 2 a1369 2 * \\g d1390 1 d1392 2 a1393 2 * retrieve (emp.all) where overpaid(emp) d1397 1 d1399 2 a1400 2 bool overpaid() /* note that there are no arguments */ d1402 1 a1402 1 { d1404 2 a1405 2 extern Datum GetAttribute(); short age, salary, performance; d1407 3 a1409 3 salary=(short) GetAttribute("salary"); seniority=(short) GetAttribute("seniority"); performance=(short) GetAttribute("performance"); d1411 1 a1411 1 return (salary > (seniority * performance)); d1413 2 a1414 1 } d1430 1 d1432 1 a1432 1 char *str d1434 2 a1435 2 str = (char *) GetAttribute("name") d1443 6 a1448 4 * define function overpaid (file="/usr/postgres/tutorial/overpaid.o", language = "c", returntype=bool) arg is (SET) d1450 2 a1451 1 * \\g d1461 1 a1461 1 * retrieve (emp.all) where overpaid2(emp, "bill", 8) d1463 3 d1469 1 d1476 1 a1476 1 d1496 1 a1496 1 * create circles (list = circle[]) d1498 4 a1501 1 \\g d1505 1 a1505 1 * append circles (list = "{"(1.0, 1.0, 5.0)", "(2.0, 2.0, 10.0)"}") d1507 2 a1508 1 \\g d1510 1 a1510 1 * append circles (list = "{"(2.0, 3.0, 15.0)", "(2.0, 2.0, 10.0)"}") d1512 3 a1514 1 \\g d1516 1 a1516 1 * append circles (list = "{"(2.0, 3.0, 4.0)"}") d1518 3 a1520 1 \\g d1524 3 a1526 1 * retrieve (circles.list[1]) d1528 3 a1530 2 \\g * retrieve (circles.all) where circles.list[1] = "(0.0, 0.0, 4.0)" d1532 2 a1533 1 \\g d1550 5 a1554 3 * define type int_array (input = array_in, output = array_out, internallength = variable, element = int4) d1556 2 a1557 1 \\g d1576 1 d1578 6 a1583 3 * define type int_arrays (input = array_in, output = array_out, internallength = variable, element = int_array) \\g d1587 3 a1589 1 * create stuff (a = int_arrays) \\g d1591 2 a1592 1 * append stuff (a = "{{1, 2, 3} , {4, 5}, {6, 7, 8}}") \\g d1594 1 a1594 1 * append stuff (a = "{{88, 99, 3}}") \\g d1596 1 a1596 1 * append stuff (a = "{{5, 4, 3} , {2, 2}}") \\g d1598 2 a1599 2 * retrieve (stuff.a[1]) where stuff.a[1][1] < stuff.a[1][2] \\g d1601 2 a1602 2 * retrieve (stuff.a) where stuff.a[3][1] < stuff.a[1][2] \\g d1604 4 a1607 3 * retrieve (s.all) from s in stuff where s.a[2][2] = stuff.a[1][1] \\g d1633 1 d1636 1 a1636 1 d1645 1 d1650 2 a1651 1 unsigned char bytes[1]; /* Force contiguity */ d1654 1 d1658 1 d1665 2 a1666 2 VAR_LEN_ATTR *stats; long array_size, *arrayp; d1668 1 a1668 1 ... figure out how long the stats array should be ... d1670 4 a1673 1 /* We need the extra sizeof(long) to hold the length */ d1675 3 a1677 1 stats = (VAR_LEN_ATTR *) palloc(array_size * sizeof(long) + sizeof(long)); d1679 3 a1681 1 /* length is length of the entire structure in bytes, INCLUDING ITSELF */ d1683 3 a1685 1 stats->length = array_size * sizeof(long) + sizeof(long); d1687 1 a1687 1 arrayp = &(stats->bytes[0]); d1689 4 a1692 4 for (i = 0; i < length; i++, arrayp++) { ... populate the stats array with numbers from s .... } d1694 1 a1694 1 return(stats); d1696 1 d1710 1 d1713 1 a1713 1 d1721 1 d1723 11 a1733 11 * define function quarterly_in (file = "/usr/postgres/tutorial/quarterly.o", language = "c", returntype = quarterly) arg is (char16) \\g * define function quarterly_out (file = "/usr/postgres/tutorial/quarterly.o",language = "c", returntype = char16) arg is (quarterly) d1735 2 a1736 1 \\g d1742 1 d1744 3 a1746 3 * define type quarterly (internallength = 16, input = quarterly_in, output = quarterly_out, element = int4) d1748 2 a1749 2 * \\g d1755 1 d1757 11 a1767 11 * define function stats_in (file = "/usr/postgres/tutorial/stats.o", language = "c", returntype = stats) arg is (char16) \\g * define function stats_out (file = "/usr/postgres/tutorial/stats.o", language = "c", returntype = char16) arg is (stats) d1769 2 a1770 1 \\g d1777 1 d1779 5 a1783 3 * define type stats (internallength = variable, input = stats_in, output = stats_out, element = int4) d1785 2 a1786 1 * \\g d1792 1 d1794 1 a1794 1 * create test (a = quarterly, b = stats) \\g d1796 6 a1801 2 * append test (a = "1 2 3 4"::quarterly, b = "5 6 7"::stats) \\g * append test (a = "1 3 2 4"::quarterly, b = "6 4"::stats) \\g d1803 2 a1804 1 * retrieve (test.all) where test.a[4] = test.b[2] \\g d1808 1 a1808 1 ------------------- d1811 1 d1852 1 d1854 4 a1857 4 * define function large_in (file = "/usr/postgres/tutorial/large.o", language = "c", returntype = map) arg is (char16) d1859 1 a1859 1 \\g d1861 4 a1864 4 * define function large_out (file = "/usr/postgres/tutorial/large.o", language = "c", returntype = char16) arg is (map) d1866 1 a1866 1 \\g d1868 3 a1870 2 * define type map (input = large_in, output = large_out, internallength = variable) d1872 2 a1873 2 \\g d1881 1 d1883 1 a1883 1 * create maps (name = text, a = map) \\g d1885 3 a1887 1 * append maps (name = "earth", a = "/usr/postgres/maps/earth") d1889 1 a1889 1 \\g d1891 3 a1893 1 * append maps (name = "moon", a = "/usr/postgres/maps/moon") d1895 2 a1896 1 \\g d1911 1 d1913 2 a1914 1 * retrieve (emp.name) where beard(emp.picture) = "red" \\g d1916 2 a1917 2 * retrieve (mountain.name) where height(mountain.topomap) > 10000 d1919 2 a1920 2 \\g d1957 1 a1957 1 amname: name of the access method d1959 1 a1959 1 amowner: object id of the owner's instance in pg_user d1961 2 a1962 1 amkind: not used at present, but set to 'o' as a place holder d1964 2 a1965 1 amstrategies: number of strategies for this access method (see below) d1967 2 a1968 1 amsupport: number of support routines for this access method (see below) d1970 9 a1978 3 am*: procedure identifiers for interface routines to the access method. For example, regproc ids for opening, closing, and getting instances from the access method appear here. d1988 3 a1990 1 * retrieve (pg_am.oid) where pg_am.amname = "btree" d1992 1 a1992 1 * \eg d1994 5 a1998 5 --------------- | oid | --------------- | 403 | --------------- d2025 1 d2027 7 a2033 5 less than 1 less than or equal 2 equal 3 greater than or equal 4 greater than 5 a2091 2 * append pg_opclass (opcname = "int4_abs_ops") * \eg d2093 2 a2094 10 * retrieve (cl.oid, cl.opcname) from cl in pg_opclass where cl.opcname = "int4_abs_ops" * \eg ----------------------------- | oid | opcname | ----------------------------- | 17314 | int4_abs_ops| ----------------------------- d2096 11 d2119 8 a2126 5 absolute value less-than absolute value less-than-or-equal absolute value equal absolute value greater-than-or-equal absolute value greater-than d2131 1 d2133 1 d2138 10 d2149 2 a2150 2 /* int4_abs.c -- absolute value comparison functions for int4 data */ #include "tmp/c.h" d2152 2 a2153 1 #define ABS(a) a = ((a < 0) ? -a : a) d2155 2 a2156 5 bool int4_abs_lt(a, b) int32 a, b; { ABS(a); ABS(b); return (a < b); } bool int4_abs_le(a, b) int32 a, b; { ABS(a); ABS(b); return (a <= b); } bool int4_abs_eq(a, b) int32 a, b; { ABS(a); ABS(b); return (a == b); } bool int4_abs_ge(a, b) int32 a, b; { ABS(a); ABS(b); return (a >= b); } bool int4_abs_gt(a, b) int32 a, b; { ABS(a); ABS(b); return (a > b); } d2158 6 d2194 25 a2218 24 * define function int4_abs_lt (file = "/usr/postgres/tutorial/int4_abs.o", language = "c", returntype = bool) arg is (int4, int4) \eg * define function int4_abs_le (file = "/usr/postgres/tutorial/int4_abs.o", language = "c", returntype = bool) arg is (int4, int4) \eg * define function int4_abs_eq (file = "/usr/postgres/tutorial/int4_abs.o", language = "c", returntype = bool) arg is (int4, int4) \eg * define function int4_abs_ge (file = "/usr/postgres/tutorial/int4_abs.o", language = "c", returntype = bool) arg is (int4, int4) \eg * define function int4_abs_gt (file = "/usr/postgres/tutorial/int4_abs.o", language = "c", returntype = bool) arg is (int4, int4) \eg d2228 2 a2229 1 * retrieve (pg_operator.all) \eg d2248 1 d2250 37 a2286 4 * define operator <<& (arg1 = int4, arg2 = int4, procedure=int4_abs_lt, associativity = left, restrict = intltsel, join = intltjoinsel) d2288 2 a2289 29 * \\g * define operator <=& (arg1 = int4, arg2 = int4, procedure=int4_abs_le, associativity = left, restrict = intltsel, join = intltjoinsel) * \\g * define operator ==& (arg1 = int4, arg2 = int4, procedure=int4_abs_eq, associativity = left, restrict = eqsel, join = eqjoinsel) * \\g * define operator >=& (arg1 = int4, arg2 = int4, procedure=int4_abs_ge, associativity = left, restrict = intgtsel, join = intgtjoinsel) * \\g * define operator >>& (arg1 = int4, arg2 = int4, procedure=int4_abs_gt, associativity = left, restrict = intgtsel, join = intgtjoinsel) * \\g d2298 1 d2300 2 a2301 1 amopid: the oid of the pg_am instance for BTREE (== 400, see above); d2303 8 a2310 2 amopclaid: the oid of the pg_opclass instance for int4_abs_ops (== whatever you got instead of 17314, see above); d2312 3 a2314 6 amopopr: the oids of the operators for the opclass (which we'll get in just a minute); and amopselect, amopnpages: cost functions. d2332 1 d2334 42 a2375 42 * retrieve (o.oid, o.oprname) from o in pg_operator, t in pg_type where o.oprleft = t.oid and o.oprright = t.oid and t.typname = "int4" * \\g ----------------------------- | oid | oprname | ----------------------------- | 96 | = | ----------------------------- | 97 | < | ----------------------------- | 514 | * | ----------------------------- | 518 | != | ----------------------------- | 521 | > | ----------------------------- | 523 | <= | ----------------------------- | 525 | >= | ----------------------------- | 528 | / | ----------------------------- | 530 | % | ----------------------------- | 551 | + | ----------------------------- | 555 | - | ----------------------------- _ | 17321 | <<& | \e ----------------------------- \e | 17322 | <=& | \e ----------------------------- \e | 17323 | ==& | | these are the ones we want ----------------------------- / -- the values you get for oids | 17324 | >=& | / will probably be different, and ----------------------------- / you should substitute them for | 17325 | >>& | / the values i use below. ----------------------------- - d2377 1 d2390 1 d2392 47 a2438 42 * append pg_amop (amopid = "400"::oid, /* btree oid */ amopclaid = "17314"::oid, /* pg_opclass tup */ amopopr = "17321"::oid, /* <<& tup oid */ amopstrategy = "1"::int2, /* 1 is <<& */ amopselect = "btreesel"::regproc, amopnpages = "btreenpage"::regproc) * \\g * append pg_amop (amopid = "400"::oid, amopclaid = "17314"::oid, amopopr = "17322"::oid, amopstrategy = "2"::int2, amopselect = "btreesel"::regproc, amopnpages = "btreenpage"::regproc) * \\g * append pg_amop (amopid = "400"::oid, amopclaid = "17314"::oid, amopopr = "17323"::oid, amopstrategy = "3"::int2, amopselect = "btreesel"::regproc, amopnpages = "btreenpage"::regproc) * \\g * append pg_amop (amopid = "400"::oid, amopclaid = "17314"::oid, amopopr = "17324"::oid, amopstrategy = "4"::int2, amopselect = "btreesel"::regproc, amopnpages = "btreenpage"::regproc) * \\g * append pg_amop (amopid = "400"::oid, amopclaid = "17314"::oid, amopopr = "17325"::oid, amopstrategy = "5"::int2, amopselect = "btreesel"::regproc, amopnpages = "btreenpage"::regproc) d2440 2 a2441 2 * \\g d2451 1 d2453 2 a2454 2 * create pairs (name = char16, number = int4) * \\g d2456 2 a2457 2 * append pairs (name = "mike", number = -10000) * \\g d2459 2 a2460 2 * append pairs (name = "greg", number = 3000) * \\g d2462 31 a2492 31 * append pairs (name = "lay peng", number = 5000) * \\g * append pairs (name = "jeff", number = -2000) * \\g * append pairs (name = "mao", number = 7000) * \\g * append pairs (name = "cimarron", number = -3000) * \\g * retrieve (pairs.all) * \\g ----------------------------- | name | number | ----------------------------- | mike | -10000 | ----------------------------- | greg | 3000 | ----------------------------- | lay peng | 5000 | ----------------------------- | jeff | -2000 | ----------------------------- | mao | 7000 | ----------------------------- | cimarron | -3000 | ----------------------------- d2498 1 d2500 4 a2503 3 * define index pairsind on pairs using btree (number int4_abs_ops) * \\g d2513 1 d2515 2 a2516 2 * retrieve (pairs.all) where pairs.number < 9000 * \\g d2518 16 a2533 16 ----------------------------- | name | number | ----------------------------- | mike | -10000 | ----------------------------- | greg | 3000 | ----------------------------- | lay peng | 5000 | ----------------------------- | jeff | -2000 | ----------------------------- | mao | 7000 | ----------------------------- | cimarron | -3000 | ----------------------------- d2542 1 d2544 2 a2545 2 * retrieve (pairs.all) where pairs.number <<& 9000 * \\g d2547 14 a2560 14 ----------------------------- | name | number | ----------------------------- | jeff | -2000 | ----------------------------- | cimarron | -3000 | ----------------------------- | greg | 3000 | ----------------------------- | lay peng | 5000 | ----------------------------- | mao | 7000 | ----------------------------- d2639 1 d2643 1 d2656 1 d2661 1 a2661 1 d2679 1 d2684 1 a2684 1 @ 1.5 log @fix some of the lies @ text @d2 1 a2 1 .\" $Header: /home/postgres/mer/docs/RCS/manual.me,v 1.4 1992/02/28 05:38:55 mao Exp mer $ d1392 1 a1392 1 * \\g d1398 2 d1402 2 d1406 1 a1406 1 * \\g d1412 1 d1415 1 a1415 1 * \\g d1436 1 a1436 1 * \\g d1458 1 a1458 1 element = int_array) d1462 1 a1462 1 * create stuff (a = int_arrays) d1464 1 a1464 1 * append stuff (a = "{{1, 2, 3} , {4, 5}, {6, 7, 8}}") d1466 1 a1466 1 * append stuff (a = "{{88, 99, 3}}) d1468 1 a1468 1 * append stuff (a = "{{5, 4, 3} , {2, 2}}") d1471 1 a1471 1 where stuff.a[1][1] < stuff.a[1][2] d1474 1 a1474 1 where stuff.a[3][1] < stuff.a[1][2] d1478 1 a1478 1 where s.a[2][2] = stuff.a[1][1] d1582 2 d1589 1 a1589 1 * \\g d1613 2 d1620 1 a1620 1 * \\g d1639 1 a1639 1 * create test (a = quarterly, b = stats) d1641 2 a1642 2 * append test (a = "1 2 3 4"::quarterly, b = "5 6 7"::stats) * append test (a = "1 3 2 4"::quarterly, b = "6 4"::stats) d1644 1 a1644 3 * retrieve (test.all) where test.a[4] = test.b[2] * \\g d1697 2 d1704 2 d1709 1 a1709 1 * \\g d1719 1 a1719 1 * create maps (name = text, a = map) d1722 3 d1727 1 a1727 1 * \\g d1743 1 a1743 1 * retrieve (emp.name) where beard(emp.picture) = "red" d1747 2 @ 1.4 log @made some changes to section on adding new operator classes to btrees. still some stuff to do, but don't want to keep the whole file locked forever. @ text @d2 1 a2 1 .\" $Header: /users/mao/postgres/doc/RCS/manual.me,v 1.3 1992/01/09 21:09:12 mer Exp mao $ d80 2 a81 2 .\" Defines II, PP, UU, LL, PQ, LQ, OF, AM, +- for .\" INGRES, POSTGRES, UNIX, LISP, \*(PQ, LIBPQ, PICASSO, "AM Guide," +-. d94 1 d107 1 d138 1 a138 1 Edited by Jon Rhein and Greg Kemnitz d157 2 a158 2 This manual describes Version 3.0 of \*(PP. \*(PP Software is available for SUN 3 and SUN 4 class machines, for d164 1 a164 1 Version 3.0 has been tuned modestly. Hence, on the d172 5 a176 5 graduate, and staff programmers. The Version 3 contributors (in alphabetical order) consisted of James Bell, Jennifer Caetta, Ron Choi, Adam Glass, Jeffrey Goh, Wei Hong, Anant Jhingran, Greg Kemnitz, Jeff Meredith, Michael Olson, Lay-Peng Ong, Spyros Potamianos, and Cimarron Taylor. d178 2 a179 2 Greg Kemnitz served as chief programmer and was responsible for overall coordination of the project and for individually d189 1 a189 1 current relational DBMSs. This system, constructed over a four d233 1 a233 1 [STON90B]. We are now delivering version 3, which is the subject d298 2 a299 1 applications. PICASSO is described in a collection of reports d337 1 d352 1 d368 1 a368 3 * I live to serve you. * GoodBye d402 1 a402 1 a unique (never-changing) identifier (OID). d410 2 a411 3 * create EMP (name=char[12], salary=float8, age=int4, dept=char[12]) * create DEPT (dname=c[12], floor=int4) d413 1 a413 1 * \\g d417 1 a417 1 statement in a relational system. However, we will presently see d427 1 a427 3 * append EMP (name="Joe",salary=1400.0,age=40,dept="shoe") * append EMP (name="sam",salary=1200.0,age=29,dept="toy") d429 1 a429 1 * append EMP (name="Bill",salary=1600.0,age=36,dept="candy") d431 1 a431 1 * \\g d446 1 a446 1 * \\g d455 2 a456 1 * retrieve (result=EMP.salary/EMP.age) where EMP.name="Bill" d458 1 a458 1 * \\g d466 1 a466 1 or, not) are allowed in any query: d469 4 a472 1 * retrieve into temp (EMP.name) where EMP.age<35 and EMP.salary>1000 a473 2 * \\g d484 4 a487 3 from E1 in EMP, E2 in EMP where E1.age=E2.age and E1.name != E2.name a488 1 * \\g d508 2 a509 2 from E in EMP where EMP.name="Joe" and E.name="Sam" d511 2 a512 1 * \\g d526 2 a527 1 * \\g d538 1 a538 1 * create SAL_EMP (name = char[], pay_by_quarter = int4[4]) d553 1 d557 1 d561 1 a562 1 * \\g d570 4 a573 1 where SAL_EMP.pay_by_quarter[1] != SAL_EMP.pay_by_quarter[2] a574 1 * \\g d582 1 a582 1 * \\g d590 2 a591 1 * \\g d600 1 a600 1 * create manager (name = char[], employees = text[]) d602 2 a603 1 * append manager (name = "mike", employees = "{"wei", "greg", "jeff"}") d605 2 a606 1 * append manager (name = "alice", employees = "{"bill", "joe"}") d608 2 a609 1 * append manager (name = "marge", employees = "{"mike", "alice"}") a610 1 * \\g d618 2 a619 1 * retrieve (manager.name) where manager.employees[1][1] = 'b' d621 2 a622 1 * retrieve (manager.name) where manager.employees[3] = "jeff" d624 1 a624 1 * retrieve (manager.employees[3][2]) d626 1 a626 1 * delete manager where manager.employees[2][1] = 'g' a627 1 * \\g d629 2 a630 1 will all work. d636 1 a636 1 In this section, we will discuss those features of \*(PP which d638 1 a638 1 travel. In the next section, we will cover how the user can extend d647 1 a647 1 .b foo d651 1 a651 1 Now create a second class STUD_EMP, as follows: d656 7 a662 1 * \\g d669 1 a669 1 the inheritance hierarchy is thereby a directed graph in general. d673 1 a673 1 employees over 40: d677 1 a677 1 * retrieve (E.name) from E in EMP where E.age > 40 d679 2 a680 1 * \\g d682 2 a683 2 On the other hand, if one wanted the names of all student employees and employees over 40, the notation is: d686 3 a688 1 * retrieve (E.name) from E in EMP* where E.age > 40 d697 2 a698 3 relational data type. A \*(PP installation can be customized with an arbitrary number of user-defined data types; this will be discussed later. d708 1 a708 1 * retrieve (EMP.salary) using EMP[T] where EMP.name="Sam" d710 1 a710 1 * \\g d714 20 a733 1 valid at the correct time and get the appropriate salary. d761 2 a762 1 * \\g d773 1 a773 1 * \\g d788 1 a788 1 * addattr EMP (manager=EMP) d790 1 a790 1 * \\g d808 1 a808 1 * \\g d819 1 a819 1 * \\g d839 1 a839 1 * \\g d858 1 a858 1 * \\g d866 1 a866 1 * \\g @ 1.3 log @fix reported from Greg @ text @d2 1 a2 1 .\" $Header: /users/mer/postgres/doc/RCS/manual.me,v 1.2 1991/12/02 05:16:06 kemnitz Exp mer $ d1709 8 d1723 4 a1726 3 pg_am contains one instance for every user defined access method. Support for the HEAP access method is built into \*(PP, but every other access method is described here. The schema is d1737 2 d1740 1 a1740 1 for example, regproc ids for opening, closing, and getting instances a1741 1 d1745 1 a1745 1 other classes. For BTREES, this object ID is 400. You don't need to add d1747 1 a1747 1 the BTREE instance. d1750 2 a1751 2 * retrieve (pg_am.oid) where pg_am.amname = "btree" d1753 1 a1753 1 * \\g d1758 1 a1758 1 | 400 | d1760 1 a1760 1 d1763 3 a1765 1 The AMSTRATEGIES attribute exists to standardize comparisons across data d1768 1 a1768 1 in general look at the name of an operator (eg, ">", "<") and tell what sort d1779 4 a1782 2 to add a new operator class. In the pg_am class, the AMSTRATEGIES attribute is the number of strategies defined for this access method. For BTREES, this d1786 6 a1791 6 less than 1 less than or equal 2 equal 3 greater than or equal 4 greater than 5 d1801 46 a1846 10 The AMSTRATEGIES entry in pg_am is just the *number* of strategies defined for the access method in question. The procedures for less than, less equal, and so on don't appear in pg_am. .lp The next class of interest is PG_OPCLASS. This class exists only to associate a name with an oid. In pg_amop, every operator class has a set of procedures, one through five, above. Some existing opclasses are int2_ops, int4_ops, and oid_ops. You need to add an instance with your opclass name (for example, "int4_abs_ops") to pg_opclass. The oid of this instance is a foreign key in other classes. d1849 1 a1849 1 d1851 1 a1851 1 * \\g d1856 1 a1856 1 * \\g d1870 13 a1882 4 So now you have an access method and an operator class. You need some operators; use the method for defining operators discussed previously. Now, suppose the c code that implements the functions defined is stored in the file d1904 10 a1913 7 There are a couple of important things that are happening below. First, note that operators for less, less equal, equal, greater equal, and greater for int4 are being defined. All of these operators are already defined for int4 under the names "<", "<=", "=", ">=", and ">". The new operators behave differently, of course. In order to guarantee that \*(PP uses these new operators rather than the old ones, they need to be named differently from the old ones. This is a key point: you can overload operators in d1917 2 a1918 2 That is, if you have "<" defined for (int4, int4), you can't define it again. \*(PP d1920 2 a1921 1 check this when you define your operator, so be careful. d1923 3 a1925 4 odd names will be used for the operators. If you *do* get this wrong, the access methods are likely to .b crash when you try to do scans. d1931 46 a1976 53 so here goes -- let's define some functions. * define function int4_abs_lt (file = "/usr/postgres/tutorial/int4_abs.o", language = "c", returntype = bool) arg is (int4, int4) * \\g * define function int4_abs_le (file = "/usr/postgres/tutorial/int4_abs.o", language = "c", returntype = bool) arg is (int4, int4) * \\g * define function int4_abs_eq (file = "/usr/postgres/tutorial/int4_abs.o", language = "c", returntype = bool) arg is (int4, int4) * \\g * define function int4_abs_ge (file = "/usr/postgres/tutorial/int4_abs.o", language = "c", returntype = bool) arg is (int4, int4) * \\g * define function int4_abs_gt (file = "/usr/postgres/tutorial/int4_abs.o", language = "c", returntype = bool) arg is (int4, int4) * \\g .)l .lp Now define the operators that use them. As noted, the operator names must be unique for two int4 operands. You can do a query on pg_operator .(l * retrieve (pg_operator.all) * \\g .)l to see if your name is taken for the types you want. The important things here are the procedure (which is the C function defined above) and the restriction and join selectivity functions. You should just use the ones used below -- note that there are different such functions for the less, equal, and greater cases. These d1978 4 a1981 5 be supplied or the access method will .b die when it tries to use the operator. You should copy the names for restrict and join, but use the procedure names you defined in the last step. @ 1.2 log @fixed up some stupidities. @ text @d2 1 a2 1 .\" $Header: RCS/manual.me,v 1.1 91/12/02 04:07:14 kemnitz Exp Locker: kemnitz $ d1594 1 a1594 1 * retrieve (test.all) where test.a[3] = test.b[1] @ 1.1 log @Initial revision @ text @d2 1 a2 1 .\" $Header: RCS/pgtmacs,v 1.1 90/07/18 16:26:06 mao Exp $ d196 1 a196 1 immediately into the appendix of this manual. d285 1 a285 2 appendix of this manual, in the "libpq" section. .b libpq d292 1 a292 1 This facility is described in the appendix under "Fast Path". d371 1 a371 1 on "monitor" in the Unix section of the Appendix. d385 1 a385 1 which are discussed further in the Unix section of the Appendix. d585 1 a585 1 'j'. SAL_EMP should now contain only bill. d627 1 a627 1 as user extensions to \*(PQ using d687 1 a687 1 * retrieve (EMP.salary) using EMP [T] where EMP.name="Sam" d689 1 a689 1 * \g d738 1 a738 1 .sh 2 "Composite Objects" d743 1 a743 1 objects. For example, consider extending the EMP class with a d829 1 a829 1 .sh 2 "User Defined Types, Operators, and Programming Language Functions" d847 1 a847 1 .sh 3 "Internal storage of types" d855 1 a855 1 .sh 3 "Functions needed for a user-defined type" d1246 5 a1250 5 C language method in \*(PP takes an argument named where emp is an instance in the emp class. Because of this, \*(PP has a notion of the "current instance" which is being examined in the course of processing a query. The current instance is simply the instance being qualified at the moment your function is called. a1257 5 Unlike functions that take simple types as arguments, these functions must call the \*(PP library function .b GetAttribute, whose use will be described below. .lp d1270 1 a1270 1 age=(short) GetAttribute("age"); d1273 1 a1273 1 return (salary > (age * performance)); d1297 2 a1298 2 Note that in queries, the instance argument must be the first argument. d1300 1 a1300 1 To let \*(PP know about the first "overpaid" function, d1332 2 a1333 1 is ommitted. d1341 1 a1341 1 creates a type suitable for an array of that type. d1371 1 a1371 1 .b NOTE d1432 1 a1432 1 where s.a[1][3] = stuff.a[1][3] d1667 1 a1667 1 * create maps (name = text, a = big_object) @