relrv=makeRangeVarFromNameList(textToQualifiedNameList(relname,

"relation_size"));

relation=relation_openrv(relrv,AccessShareLock);

relation=heap_openrv(relrv,AccessShareLock);

relnode=relation->rd_rel->relfilenode;

segcount++;

}

relation_close(relation,AccessShareLock);

heap_close(relation,AccessShareLock);

PG_RETURN_INT64(totalsize);

}

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_type.sgml,v 1.33 2002/08/23 00:33:24 tgl Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_type.sgml,v 1.34 2002/08/29 00:17:01 tgl Exp $

PostgreSQL documentation

-->

)

CREATE TYPE <replaceable class="parameter">typename</replaceable> AS

( <replaceable class="PARAMETER">column_definition_list</replaceable> )

where <replaceable class="PARAMETER">column_definition_list</replaceable> can be:

( <replaceable class="PARAMETER">column_name</replaceable> <replaceable class="PARAMETER">data_type</replaceable> [, ... ] )

( <replaceable class="PARAMETER">column_name</replaceable> <replaceable class="PARAMETER">data_type</replaceable> [, ... ] )

</synopsis>

<refsect2 id="R2-SQL-CREATETYPE-1">

type names also must not conflict with table names in the same schema.)

</para>

<refsect2>

<title>Base Types</title>

<para>

The first form of <command>CREATE TYPE</command> requires the

The first form of <command>CREATE TYPE</command> creates a new base type

(scalar type). It requires the

registration of two functions (using CREATE FUNCTION) before defining the

type. The representation of a new base type is determined by

<replaceable class="parameter">input_function</replaceable>, which

a row fit, but they will be kept in the main table preferentially over

<literal>extended</literal> and <literal>external</literal> items.)

</para>

</refsect2>

<refsect2>

<title>Composite Types</title>

<para>

The second form of <command>CREATE TYPE</command> requires a column

definition list in the form ( <replaceable class="PARAMETER">column_name</replaceable>

<replaceable class="PARAMETER">data_type</replaceable> [, ... ] ). This

creates a composite type, similar to that of a TABLE or VIEW relation.

A stand-alone composite type is useful as the return type of FUNCTION.

The second form of <command>CREATE TYPE</command>

creates a composite type.

The composite type is specified by a list of column names and datatypes.

This is essentially the same as the row type

of a table, but using <command>CREATE TYPE</command> avoids the need to

create an actual table when all that is wanted is to define a type.

A stand-alone composite type is useful as the return type of a function.

</para>

</refsect2>

<refsect2>

<title>Array Types</title>

<para>

Whenever a user-defined data type is created,

Whenever a user-definedbasedata type is created,

<productname>PostgreSQL</productname> automatically creates an

associated array type, whose name consists of the base type's

name prepended with an underscore. The parser understands this

This example creates a composite type and uses it in

a table function definition:

<programlisting>

CREATE TYPE compfoo AS (f1 int, f2int);

CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS 'SELECT fooid,foorefid FROM foo' LANGUAGE SQL;

CREATE TYPE compfoo AS (f1 int, f2text);

CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS 'SELECT fooid,fooname FROM foo' LANGUAGE SQL;

</programlisting>

</para>

</refsect1>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/ref/select.sgml,v 1.57 2002/08/28 14:35:37 momjian Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/ref/select.sgml,v 1.58 2002/08/29 00:17:01 tgl Exp $

PostgreSQL documentation

-->

( <replaceable class="PARAMETER">select</replaceable> )

[ AS ] <replaceable class="PARAMETER">alias</replaceable> [ ( <replaceable class="PARAMETER">column_alias_list</replaceable> ) ]

|

<replaceable class="PARAMETER">table_function_name</replaceable> ( [ <replaceable class="parameter">argtype</replaceable> [, ...] ] )

<replaceable class="PARAMETER">table_function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] )

[ AS ] <replaceable class="PARAMETER">alias</replaceable> [ ( <replaceable class="PARAMETER">column_alias_list</replaceable> | <replaceable class="PARAMETER">column_definition_list</replaceable> ) ]

|

<replaceable class="PARAMETER">table_function_name</replaceable> ( [ <replaceable class="parameter">argtype</replaceable> [, ...] ] )

<replaceable class="PARAMETER">table_function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] )

AS ( <replaceable class="PARAMETER">column_definition_list</replaceable> )

|

<replaceable class="PARAMETER">from_item</replaceable> [ NATURAL ] <replaceable class="PARAMETER">join_type</replaceable> <replaceable class="PARAMETER">from_item</replaceable>

<term><replaceable class="PARAMETER">alias</replaceable></term>

<listitem>

<para>

A substitute name for the preceding

<replaceable class="PARAMETER">table_name</replaceable>.

A substitute name for the FROM item containing the alias.

An alias is used for brevity or to eliminate ambiguity for self-joins

(where the same table is scanned multiple times). If an alias is

(where the same table is scanned multiple times). When an alias

is provided, it completely hides the actual name of the table or

table function; for example given <literal>FROM foo AS f</>, the

remainder of the SELECT must refer to this FROM item as <literal>f</>

not <literal>foo</>.

If an alias is

written, a column alias list can also be written to provide

substitute names for one or more columns of the table.

</para>

A table function can appear in the FROM clause. This acts as though

its output were created as a temporary table for the duration of

this single SELECT command. An alias may also be used. If an alias is

written, a column alias list can also be written to providesubstitute names

for one or more columns of the table function. If the table function has been

defined as returning the RECORD data type, an alias, or the keyword AS, must

also be present, followed by a column definition list in the form

( <replaceable class="PARAMETER">column_name</replaceable> <replaceable class="PARAMETER">data_type</replaceable> [, ... ] ).

The column definition list must match the actual number and types returned by the function.

written, a column alias list can also be written to provide substitute

names for one or more columns of the table function. If the table

function has been defined as returning the <type>record</> data type,

an alias, or the keyword <literal>AS</>, must be present, followed by

a column definition list in the form ( <replaceable

class="PARAMETER">column_name</replaceable> <replaceable

class="PARAMETER">data_type</replaceable> [, ... ] ).

The column definition list must match the actual number and types

of columns returned by the function.

</para>

</listitem>

</varlistentry>

this was the default result, and adding sub-tables was done

by appending <command>*</command> to the table name.

This old behavior is available via the command

<command>SET SQL_Inheritance TO OFF;</command>

<command>SET SQL_Inheritance TO OFF</command>.

</para>

<para>

</para>

<para>

A FROM item can be a table function (i.e. a function that returns

multiple rows and columns). When a table function is created, it may

be defined to return a named scalar or composite data type (an existing

scalar data type, or a table or view name), or it may be defined to return

a RECORD data type. When a table function is defined to return RECORD, it

must be followed in the FROM clause by an alias, or the keyword AS alone,

and then by a parenthesized list of column names and types. This provides

a query-time composite type definition. The FROM clause composite type

must match the actual composite type returned from the function or an

ERROR will be generated.

A FROM item can be a table function (typically, a function that returns

multiple rows and/or columns, though actually any function can be used).

The function is invoked with the given argument value(s), and then its

output is scanned as though it were a table.

</para>

<para>

In some cases it is useful to define table functions that can return

different column sets depending on how they are invoked. To support this,

the table function can be declared as returning the pseudo-type

<type>record</>. When such a function is used in FROM, it must be

followed by an alias, or the keyword <literal>AS</> alone,

and then by a parenthesized list of column names and types. This provides

a query-time composite type definition. The composite type definition

must match the actual composite type returned from the function, or an

error will be reported at run-time.

</para>

<para>

unless ORDER BY is used to constrain the order.

</para>

</refsect2>

<refsect2 id="SQL-FOR-UPDATE">

<refsect2info>

<date>2002-08-28</date>

</refsect2info>

<title id="sql-for-update-title">

FOR UPDATE Clause

</title>

<para>

<synopsis>

FOR UPDATE [ OF <replaceable class="PARAMETER">tablename</replaceable> [, ...] ]

</synopsis>

</para>

<para>

FOR UPDATE causes the rows retrieved by the query to be locked as though

for update. This prevents them from being modified or deleted by other

transactions until the current transaction ends.

</para>

<para>

If specific tables are named in FOR UPDATE, then only rows coming from

those tables are locked.

</para>

<para>

FOR UPDATE cannot be used in contexts where returned rows can't be clearly

identified with individual table rows; for example it can't be used with

aggregation.

</para>

</refsect2>

</refsect1>

<refsect1 id="R1-SQL-SELECT-2">

<productname>PostgreSQL</productname> allows one to omit

the <command>FROM</command> clause from a query. This feature

was retained from the original PostQuel query language. It has

a straightforward use to compute the results of simple constant

expressions:

a straightforward use to compute the results of simple expressions:

<programlisting>

SELECT 2+2;

contains an explicit FROM clause.

</para>

<para>

The table-function feature is a <productname>PostgreSQL</productname>

extension.

</para>

</refsect2>

<refsect2 id="R2-SQL-SELECT-5">

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/ref/select_into.sgml,v 1.19 2002/08/28 14:35:37 momjian Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/ref/select_into.sgml,v 1.20 2002/08/29 00:17:01 tgl Exp $

PostgreSQL documentation

-->

[ HAVING <replaceable class="PARAMETER">condition</replaceable> [, ...] ]

[ { UNION | INTERSECT | EXCEPT } [ ALL ] <replaceable class="PARAMETER">select</replaceable> ]

[ ORDER BY <replaceable class="PARAMETER">expression</replaceable> [ ASC | DESC | USING <replaceable class="PARAMETER">operator</replaceable> ] [, ...] ]

[ LIMIT[ <replaceable class="PARAMETER">start</replaceable> , ]{ <replaceable class="PARAMETER">count</replaceable> | ALL } ]

[ LIMIT { <replaceable class="PARAMETER">count</replaceable> | ALL } ]

[ OFFSET <replaceable class="PARAMETER">start</replaceable> ]

[ FOR UPDATE [ OF <replaceable class="PARAMETER">tablename</replaceable> [, ...] ] ]

where <replaceable class="PARAMETER">from_item</replaceable> can be:

[ ONLY ] <replaceable class="PARAMETER">table_name</replaceable> [ * ]

[ [ AS ] <replaceable class="PARAMETER">alias</replaceable> [ ( <replaceable class="PARAMETER">column_alias_list</replaceable> ) ] ]

|

( <replaceable class="PARAMETER">select</replaceable> )

[ AS ] <replaceable class="PARAMETER">alias</replaceable> [ ( <replaceable class="PARAMETER">column_alias_list</replaceable> ) ]

|

<replaceable class="PARAMETER">from_item</replaceable> [ NATURAL ] <replaceable class="PARAMETER">join_type</replaceable> <replaceable class="PARAMETER">from_item</replaceable>

[ ON <replaceable class="PARAMETER">join_condition</replaceable> | USING ( <replaceable class="PARAMETER">join_column_list</replaceable> ) ]

</synopsis>

<refsect2 id="R2-SQL-SELECTINTO-1">

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/release.sgml,v 1.152 2002/08/27 04:55:07 tgl Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/release.sgml,v 1.153 2002/08/29 00:17:01 tgl Exp $

-->

<appendix id="release">

<literallayout><![CDATA[

PREPARE statement allows caching query plans for interactive statements

Type OPAQUE is now deprecated in favor of pseudo-types cstring, trigger, etc

Standalone composite types can now be created with CREATE TYPE

Files larger than 2 GB are now supported (if supported by the operating system)

SERIAL no longer implies UNIQUE; specify explicitly if index is wanted

pg_dump -n and -N options have been removed. The new behavior is like -n but knows about key words.