<!--

$Header: /cvsroot/pgsql/doc/src/sgml/Attic/admin.sgml,v 1.28 2000/11/24 17:44:21 petere Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/Attic/admin.sgml,v 1.29 2001/01/13 23:58:55 petere Exp $

-->

<book id="admin">

developed originally in the UC Berkeley Computer Science Department,

pioneered many of the object-relational concepts

now becoming available in some commercial databases.

It provides SQL92/SQL3 language support,

It provides SQL92/SQL99 language support,

transaction integrity, and type extensibility.

<productname>PostgreSQL</productname> is an open-source descendant

of this original Berkeley code.

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/advanced.sgml,v 1.20 2001/01/05 06:34:15 tgl Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/advanced.sgml,v 1.21 2001/01/13 23:58:55 petere Exp $

-->

<chapter id="advanced">

<title>Inheritance</title>

<para>

Let's create twoclasses. The capitalsclass contains

Let's create twotables. The capitalstable contains

state capitals that are also cities. Naturally, the

capitalsclass should inherit from cities.

capitalstable should inherit from cities.

<programlisting>

CREATE TABLE cities (

name text,

populationfloat,

populationreal,

altitude int -- (in ft)

);

) INHERITS (cities);

</programlisting>

In this case,an instance of capitals <firstterm>inherits</firstterm> all

attributes (name, population, and altitude) from its

parent, cities. The type of theattribute name is

In this case,a row of capitals <firstterm>inherits</firstterm> all

columns (name, population, and altitude) from its

parent, cities. The type of thecolumn name is

<type>text</type>, a native <productname>Postgres</productname>

type for variable length

ASCII strings. The type of the attribute population is

<type>float</type>, a native <productname>Postgres</productname>

type for double precision

ASCII strings. The type of the column population is

<type>real</type>, a type for single precision

floating point numbers. State capitals have an extra

attribute, state, that shows their state.

column, state, that shows their state.

In <productname>Postgres</productname>,

aclass can inherit from zero or more otherclasses,

and a query can reference either allinstances of a

class or allinstances of aclass plus all of its

atable can inherit from zero or more othertables,

and a query can reference either allrows of a

table or allrows of atables plus all of its

descendants.

<note>

<para>

Here the <quote>ONLY</quote> before cities indicates that the query should

be run over only the cities table, and notclasses below cities in the

be run over only the cities table, and nottables below cities in the

inheritance hierarchy. Many of the commands that we

have already discussed -- <command>SELECT</command>,

<command>UPDATE</command> and <command>DELETE</command> --

In previous versions of <productname>Postgres</productname>, the

default was not to get access to child tables. This was found to

be error prone and is also in violation of SQL99. Under the old

syntax, to get the sub-classes you append "*" to the table name.

syntax, to get the sub-tables you append "*" to the table name.

For example

<programlisting>

SELECT * from cities*;

<para>

One of the tenets of the relational model is that the

attributes of arelation are atomic.

columns of atable are atomic.

<productname>Postgres</productname> does not

have this restriction;attributes can themselves contain

have this restriction;columns can themselves contain

sub-values that can be accessed from the query

language. For example, you can createattributes that

language. For example, you can createcolumns that

are arrays of base types.

</para>

<sect2>

<title>Arrays</title>

<para>

<productname>Postgres</productname> allowsattributes ofan

instance to be defined

<productname>Postgres</productname> allowscolumns ofa

row to be defined

as fixed-length or variable-length multi-dimensional

arrays. Arrays of any base type or user-defined type

can be created. To illustrate their use, we first create a

class with arrays of base types.

table with arrays of base types.

<programlisting>

CREATE TABLE SAL_EMP (

name text,

pay_by_quarterint4[],

pay_by_quarterinteger[],

schedule text[][]

);

</programlisting>

</para>

<para>

The above query will create aclass named SAL_EMP with

The above query will create atable named SAL_EMP with

a <firstterm>text</firstterm> string (name), a one-dimensional

array of <firstterm>int4</firstterm>

array of <firstterm>integer</firstterm>

(pay_by_quarter), which represents the employee's

salary by quarter and a two-dimensional array of

<firstterm>text</firstterm>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/array.sgml,v 1.8 2000/12/18 23:39:37 tgl Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/array.sgml,v 1.9 2001/01/13 23:58:55 petere Exp $

-->

<Chapter Id="arrays">

</Para>

<Para>

<ProductName>Postgres</ProductName> allowsattributes of aclass

<ProductName>Postgres</ProductName> allowscolumns of atable

to be defined as variable-length multi-dimensional

arrays. Arrays of any built-in type or user-defined type

can be created. To illustrate their use, we create thisclass:

can be created. To illustrate their use, we create thistable:

<ProgramListing>

CREATE TABLE sal_emp (

</Para>

<Para>

The above query will create aclass named <FirstTerm>sal_emp</FirstTerm> with

The above query will create atable named <FirstTerm>sal_emp</FirstTerm> with

a <FirstTerm>text</FirstTerm> string (name), a one-dimensional array of <FirstTerm>int4</FirstTerm>

(pay_by_quarter), which represents the employee's

salary by quarter, and a two-dimensional array of <FirstTerm>text</FirstTerm>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/extend.sgml,v 1.8 2000/12/26 00:10:37 petere Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/extend.sgml,v 1.9 2001/01/13 23:58:55 petere Exp $

-->

<chapter id="extend">

about databases, tables, columns, etc., in what are

commonly known as system catalogs. (Some systems call

this the data dictionary). The catalogs appear to the

user asclasses, like any other, but the <acronym>DBMS</acronym> stores

user astables like any other, but the <acronym>DBMS</acronym> stores

its internal bookkeeping in them. One key difference

between <productname>Postgres</productname> and standard relational systems is

that <productname>Postgres</productname> stores much more information in its

catalogs -- not only information about tables and columns,

but also information about its types, functions, access

methods, and so on. Theseclasses can be modified by

methods, and so on. Thesetables can be modified by

the user, and since <productname>Postgres</productname> bases its internal operation

on theseclasses, this means that <productname>Postgres</productname> can be

on thesetables, this means that <productname>Postgres</productname> can be

extended by users. By comparison, conventional

database systems can only be extended by changing hardcoded

procedures within the <acronym>DBMS</acronym> or by loading modules

by the user and only understands the behavior of such

types to the extent that the user describes them.

Composite types are created whenever the user creates a

class. EMP is an example of a composite type.

table. EMP is an example of a composite type.

</para>

<para>

<productname>Postgres</productname> stores these types

in only one way (within the

file that stores allinstances ofthe class) but the

file that stores allrows ofa table) but the

user can "look inside" at the attributes of these types

from the query language and optimize their retrieval by

(for example) defining indices on the attributes.

reference.

All system catalogs have names that begin with

<firstterm>pg_</firstterm>.

The followingclasses contain information that may be

The followingtables contain information that may be

useful to the end user. (There are many other system

catalogs, but there should rarely be a reason to query

them directly.)

</row>

<row>

<entry>pg_class</entry>

<entry>classes</entry>

<entry>tables</entry>

</row>

<row>

<entry>pg_attribute</entry>

<entry>class attributes</entry>

<entry>table columns</entry>

</row>

<row>

<entry>pg_index</entry>

</figure>

The Reference Manual gives a more detailed explanation

of these catalogs and theirattributes. However,

of these catalogs and theircolumns. However,

<xref linkend="EXTEND-CATALOGS">

shows the major entities and their relationships

in the system catalogs. (Attributes that do not refer

in the system catalogs. (Columns that do not refer

to other entities are not shown unless they are part of

a primary key.)

This diagram is more or less incomprehensible until you

some of these join queries (which are often

three- or four-way joins) more understandable,

because you will be able to see that the

attributes used in the queries form foreign keys

in otherclasses.

columns used in the queries form foreign keys

in othertables.

</para>

</listitem>

<listitem>

<para>

Many different features (classes, attributes,

Many different features (tables, columns,

functions, types, access methods, etc.) are

tightly integrated in this schema. A simple

create command may modify many of these catalogs.

</note>

Nearly every catalog contains some reference to

instances in one or both of theseclasses. For

rows in one or both of thesetables. For

example, <productname>Postgres</productname> frequently uses type

signatures (e.g., of functions and operators) to

identify uniqueinstances of other catalogs.

identify uniquerows of other catalogs.

</para>

</listitem>

<listitem>

<para>

There are manyattributes and relationships that

There are manycolumns and relationships that

have obvious meanings, but there are many

(particularly those that have to do with access

methods) that do not. The relationships between

<para>

For a <firstterm>functional index</firstterm>, an index is defined

on the result of a function applied

to one or moreattributes of a singleclass.

to one or morecolumns of a singletable.

This is a single-column index (namely, the function result)

even if the function uses more than one input field.

Functional indices can be used to obtain fast access to data

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/Attic/inherit.sgml,v 1.12 2001/01/05 06:34:15 tgl Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/Attic/inherit.sgml,v 1.13 2001/01/13 23:58:55 petere Exp $

-->

<chapter id="inherit">

<title>Inheritance</title>

<para>

Let's create twoclasses. The capitalsclass contains

Let's create twotables. The capitalstable contains

state capitals which are also cities. Naturally, the

capitalsclass should inherit from cities.

capitalstable should inherit from cities.

<programlisting>

CREATE TABLE cities (

) INHERITS (cities);

</programlisting>

In this case,an instance of capitals <firstterm>inherits</firstterm> all

In this case,a row of capitals <firstterm>inherits</firstterm> all

attributes (name, population, and altitude) from its

parent, cities. The type of the attribute name is

<type>text</type>, a native <productname>Postgres</productname> type for variable length

ASCII strings. The type of the attribute population is

<type>float</type>, a native <productname>Postgres</productname> type for double precision

floating point numbers. State capitals have an extra

attribute, state, that shows their state. In <productname>Postgres</productname>,

aclass can inherit from zero or more otherclasses,

and a query can reference either allinstances of a

class or allinstancesof aclass plus all of its

atable can inherit from zero or more othertables,

and a query can reference either allrows of a

table or allrowsof atable plus all of its

descendants.

<note>

<para>

Here the <quote>ONLY</quote> before cities indicates that the query should

be run over only cities and notclasses below cities in the

be run over only cities and nottables below cities in the

inheritance hierarchy. Many of the commands that we

have already discussed -- <command>SELECT</command>,

<command>UPDATE</command> and <command>DELETE</command> --

<para>

In some cases you may wish to know which table a particular tuple

originated from. There is a systemattribute called

originated from. There is a systemcolumn called

<quote>TABLEOID</quote> in each table which can tell you the

originating table:

In previous versions of <productname>Postgres</productname>, the

default was not to get access to child tables. This was found to

be error prone and is also in violation of SQL99. Under the old

syntax, to get the sub-classes you append "*" to the table name.

syntax, to get the sub-tables you append "*" to the table name.

For example

<programlisting>

SELECT * from cities*;

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/intro.sgml,v 1.12 2000/09/29 20:21:34 petere Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/intro.sgml,v 1.13 2001/01/13 23:58:55 petere Exp $

-->

<chapter id="intro">

extend the system:

<simplelist>

<member>classes</member>

<member>tables</member>

<member>inheritance</member>

<member>types</member>

<member>functions</member>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/Attic/libpq++.sgml,v 1.22 2000/12/26 00:10:37 petere Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/Attic/libpq++.sgml,v 1.23 2001/01/13 23:58:55 petere Exp $

-->

<chapter id="libpqplusplus">

<listitem>

<para>

<function>Tuples</function>

Returns the number of tuples (instances) in the query result.

Returns the number of tuples (rows) in the query result.

<synopsis>

int PgDatabase::Tuples()

</synopsis>