<!--

$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_table_as.sgml,v 1.13 2003/05/04 00:03:55 tgl Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_table_as.sgml,v 1.14 2003/07/01 00:04:31 petere Exp $

PostgreSQL documentation

-->

<para>

<command>CREATE TABLE AS</command> creates a table and fills it

with data computed by a <command>SELECT</command> command. The

table columns have the names and data types associated with the

output columns of the <command>SELECT</command> (except that you

can override the column names by giving an explicit list of new

column names).

with data computed by a <command>SELECT</command> command or an

<command>EXECUTE</command> that runs a prepared

<command>SELECT</command> command. The table columns have the

names and data types associated with the output columns of the

<command>SELECT</command> (except that you can override the column

names by giving an explicit list of new column names).

</para>

<para>

<para>

The name of a column in the new table. If column names are not

provided, they are taken from the output column names of the

query.

query. If the table is created out of an

<command>EXECUTE</command> command, a column name list can

currently not be specified.

</para>

</listitem>

</varlistentry>

<term><replaceable>query</replaceable></term>

<listitem>

<para>

A query statement (that is, a <command>SELECT</command>

command). Refer to

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

for a description of the allowed syntax.

A query statement (that is, a <command>SELECT</command> command

or an <command>EXECUTE</command> command that runs a prepared

<command>SELECT</command> command). Refer to <xref

linkend="sql-select" endterm="sql-select-title"> or <xref

linkend="sql-execute" endterm="sql-execute-title">,

respectively, for a description of the allowed syntax.

</para>

</listitem>

</varlistentry>

<title>Diagnostics</title>

<para>

Refer to <xref linkend="sql-createtable" endterm="sql-createtable-title"> and

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

for a summary of possible output messages.

Refer to <xref linkend="sql-createtable"

endterm="sql-createtable-title">, <xref linkend="sql-select"

endterm="sql-select-title">, and <xref linkend="sql-execute"

endterm="sql-execute-title"> for a summary of possible output

messages.

</para>

</refsect1>

<simplelist type="inline">

<member><xref linkend="sql-createtable" endterm="sql-createtable-title"></member>

<member><xref linkend="sql-createview" endterm="sql-createview-title"></member>

<member><xref linkend="sql-execute" endterm="sql-execute-title"></member>

<member><xref linkend="sql-select" endterm="sql-select-title"></member>

<member><xref linkend="sql-selectinto" endterm="sql-selectinto-title"></member>

</simplelist>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/ref/execute.sgml,v 1.4 2003/04/26 23:56:51 petere Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/ref/execute.sgml,v 1.5 2003/07/01 00:04:31 petere Exp $

PostgreSQL documentation

-->

<refsynopsisdiv>

<synopsis>

EXECUTE <replaceable class="PARAMETER">plan_name</replaceable> [ (<replaceable class="PARAMETER">parameter</replaceable> [, ...] ) ] [ INTO [ TEMPORARY | TEMP ] <replaceable class="PARAMETER">table</replaceable> ]

EXECUTE <replaceable class="PARAMETER">plan_name</replaceable> [ (<replaceable class="PARAMETER">parameter</replaceable> [, ...] ) ]

</synopsis>

</refsynopsisdiv>

name of a prepared statement must be unique within a database session.

</para>

<para>

Like <command>SELECT INTO</command>, <command>EXECUTE</command> can

store the results of executing a query into a newly-created

table, by specifying an <literal>INTO</> clause. For more information on this behavior,

see <xref linkend="sql-selectinto" endterm="sql-selectinto-title">.

</para>

<para>

For more information on the creation and usage of prepared statements,

see <xref linkend="sql-prepare" endterm="sql-prepare-title">.

</para>

</listitem>

</varlistentry>

<varlistentry>

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

<listitem>

<para>

The name of the table in which to store the results of executing

the statement (if it is a <command>SELECT</command>). If no

table is specified, the results are returned to the client (as

normal).

</para>

</listitem>

</varlistentry>

</variablelist>

</refsect1>

portal=CreateNewPortal();

if (stmt->into)

{

qcontext=PortalGetHeapMemory(portal);

if (length(query_list)!=1)

elog(ERROR,"INTO clause specified for non-SELECT query");

elog(ERROR,"prepared statement is not a SELECT");

query= (Query*)lfirst(query_list);

if (query->commandType!=CMD_SELECT)

elog(ERROR,"INTO clause specified for non-SELECT query");

elog(ERROR,"prepared statement is not a SELECT");

query->into=copyObject(stmt->into);

MemoryContextSwitchTo(oldContext);

if (execstmt->into)

{

if (query->commandType!=CMD_SELECT)

elog(ERROR,"INTO clause specified for non-SELECT query");

elog(ERROR,"prepared statement is not a SELECT");

query=copyObject(query);

ExecuteStmt: EXECUTE name execute_param_clause into_clause

ExecuteStmt: EXECUTE name execute_param_clause

{

ExecuteStmt *n =makeNode(ExecuteStmt);

n->name = $2;

n->params = $3;

n->into =NULL;

$$ = (Node *) n;

}

| CREATE OptTemp TABLE qualified_name OptCreateAs AS EXECUTE name execute_param_clause

{

ExecuteStmt *n =makeNode(ExecuteStmt);

n->name = $8;

n->params = $9;

$4->istemp = $2;

n->into = $4;

if ($5)

elog(ERROR,"column name list not allowed in CREATE TABLE / AS EXECUTE");

$$ = (Node *) n;

}

;

-- invalid type

PREPARE q4(nonexistenttype) AS SELECT $1;

ERROR: Type "nonexistenttype" does not exist

--execute into

--create table as execute

PREPARE q5(int, text) AS

SELECT * FROM tenk1 WHERE unique1 = $1 OR stringu1 = $2;

EXECUTE q5(200, 'DTAAAA') INTO TEMPORARY q5_prep_results;

CREATE TEMPORARY TABLE q5_prep_results ASEXECUTE q5(200, 'DTAAAA');

SELECT * FROM q5_prep_results;

unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4

---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------

PREPARE q4(nonexistenttype)ASSELECT $1;

PREPARE q5(int,text)AS

SELECT*FROM tenk1WHERE unique1= $1OR stringu1= $2;

EXECUTE q5(200,'DTAAAA') INTO TEMPORARY q5_prep_results;

CREATE TEMPORARY TABLE q5_prep_resultsASEXECUTE q5(200,'DTAAAA');

SELECT*FROM q5_prep_results;