<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.54 2006/02/12 19:11:00 momjian Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.55 2006/02/18 23:14:45 neilc Exp $ -->

<chapter id="ddl">

<title>Data Definition</title>

</indexterm>

<para>

To create a table, you use the aptly named <command>CREATE

TABLE</command> command. In this command you specify at least a

name for the new table, the names of the columns and the data type

of each column. For example:

To create a table, you use the aptly named <xref

linkend="sql-createtable" endterm="sql-createtable-title"> command.

In this command you specify at least a name for the new table, the

names of the columns and the data type of each column. For

example:

<programlisting>

CREATE TABLE my_first_table (

first_column text,

</indexterm>

<para>

If you no longer need a table, you can remove it using the

<command>DROP TABLE</command> command. For example:

If you no longer need a table, you can remove it using the <xref

linkend="sql-droptable" endterm="sql-droptable-title"> command.

For example:

<programlisting>

DROP TABLE my_first_table;

DROP TABLE products;

</indexterm>

<para>

To create a schema, use thecommand <command>CREATE

SCHEMA</command>. Give the schema a name of your choice. For

example:

To create a schema, use the<xref linkend="sql-createschema"

endterm="sql-createschema-title"> command. Give the schema a name

of your choice. Forexample:

<programlisting>

CREATE SCHEMA myschema;

</programlisting>

<!-- $PostgreSQL: pgsql/doc/src/sgml/dml.sgml,v 1.12 2005/03/17 20:24:34 momjian Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/dml.sgml,v 1.13 2006/02/18 23:14:45 neilc Exp $ -->

<chapter id="dml">

<title>Data Manipulation</title>

<para>

To create a new row, use the <xref linkend="sql-insert"

xreflabel="sql-insert-title"> command. The command requires the

endterm="sql-insert-title"> command. The command requires the

table name and a value for each of the columns of the table. For

example, consider the products table from <xref linkend="ddl">:

<programlisting>

<tip>

<para>

To do <quote>bulk loads</quote>, that is,inserting a lot of data,

take a look atthe <xref linkend="sql-copy"

endterm="sql-copy-title"> command.It is not as flexible as the

<xref linkend="sql-insert"endterm="sql-insert-title">command,

but is more efficient. Referto <xref linkend="populate"> for more

information on improvingbulk loading performance.

Wheninserting a lot of data at the same time, considering using

the <xref linkend="sql-copy" endterm="sql-copy-title"> command.

It is not as flexible as the <xref linkend="sql-insert"

endterm="sql-insert-title"> command,but is more efficient. Refer

to <xref linkend="populate"> for moreinformation on improving

bulk loading performance.

</para>

</tip>

</sect1>

</para>

<para>

You use the <xref linkend="sql-delete"

xreflabel="sql-delete-title"> command to remove rows; the syntax is

very similar to the <command>UPDATE</command> command. For

instance, to remove all rows from the products table that have a

price of 10, use

You use the <xref linkend="sql-delete" endterm="sql-delete-title">

command to remove rows; the syntax is very similar to the

<command>UPDATE</command> command. For instance, to remove all

rows from the products table that have a price of 10, use

<programlisting>

DELETE FROM products WHERE price = 10;

</programlisting>

<!--

$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.86 2006/02/12 06:37:05 tgl Exp $

$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.87 2006/02/18 23:14:45 neilc Exp $

-->

<chapter id="plpgsql">

allow users to define set-returning functions

that do not have this limitation. Currently, the point at

which data begins being written to disk is controlled by the

<xref linkend="guc-work-mem" xreflabel="work_mem">

<xref linkend="guc-work-mem">

configuration variable. Administrators who have sufficient

memory to store larger result sets in memory should consider

increasing this parameter.

<para>

<productname>PostgreSQL</> gives you two function creation

modifiers to optimize execution: <quote>volatility</> (whether the

function always returns the same result when given the same

arguments) and <quote>strictness</quote> (whether the

function returns null if any argument is null). Consult the

<xref linkend="sql-createfunction"> reference page for details.

modifiers to optimize execution: <quote>volatility</> (whether

the function always returns the same result when given the same

arguments) and <quote>strictness</quote> (whether the function

returns null if any argument is null). Consult the <xref

linkend="sql-createfunction" endterm="sql-createfunction-title">

reference page for details.

</para>

<para>

<!-- $PostgreSQL: pgsql/doc/src/sgml/queries.sgml,v 1.34 2005/05/08 03:08:05 momjian Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/queries.sgml,v 1.35 2006/02/18 23:14:45 neilc Exp $ -->

<chapter id="queries">

<title>Queries</title>

<para>

The process of retrieving or the command to retrieve data from a

database is called a <firstterm>query</firstterm>. In SQL the

<command>SELECT</command> command is used to specify queries. The

general syntax of the <command>SELECT</command> command is

<xref linkend="sql-select" endterm="sql-select-title"> command is

used to specify queries. The general syntax of the

<command>SELECT</command> command is

<synopsis>

SELECT <replaceable>select_list</replaceable> FROM <replaceable>table_expression</replaceable> <optional><replaceable>sort_specification</replaceable></optional>

</synopsis>

<synopsis>

SELECT <replaceable>select_list</replaceable>

FROM <replaceable>table_expression</replaceable>

<optional>LIMIT { <replaceable>number</replaceable> | ALL }</optional> <optional>OFFSET <replaceable>number</replaceable></optional>

<optional>LIMIT { <replaceable>number</replaceable> | ALL }</optional> <optional>OFFSET <replaceable>number</replaceable></optional>

</synopsis>

</para>

<!--

$PostgreSQL: pgsql/doc/src/sgml/ref/alter_function.sgml,v 1.10 2005/11/01 21:09:50 tgl Exp $

$PostgreSQL: pgsql/doc/src/sgml/ref/alter_function.sgml,v 1.11 2006/02/18 23:14:45 neilc Exp $

PostgreSQL documentation

-->

null. <literal>RETURNS NULL ON NULL INPUT</literal> or

<literal>STRICT</literal> changes the function so that it is not

invoked if any of its arguments are null; instead, a null result

is assumed automatically. See <xref

linkend="sql-createfunction"> for more information.

is assumed automatically. See <xref linkend="sql-createfunction"

endterm="sql-createfunction-title"> for more information.

</para>

</listitem>

</varlistentry>

<listitem>

<para>

Change the volatility of the function to the specified

setting. See <xref linkend="sql-createfunction"> for details.

Change the volatility of the function to the specified setting.

See <xref linkend="sql-createfunction"

endterm="sql-createfunction-title"> for details.

</para>

</listitem>

</varlistentry>

<para>

Change whether the function is a security definer or not. The

key word <literal>EXTERNAL</literal> is ignored for SQL

conformance. See <xref linkend="sql-createfunction"> for more

information about this capability.

conformance. See <xref linkend="sql-createfunction"

endterm="sql-createfunction-title"> for more information about

this capability.

</para>

</listitem>

</varlistentry>