Skip to content

Commit 71e9f3b

Browse files
peterepetere
committed
Change EXECUTE INTO to CREATE TABLE AS EXECUTE.
1 parent df08f5c commit 71e9f3b

File tree

6 files changed

+52
-51
lines changed

6 files changed

+52
-51
lines changed

‎doc/src/sgml/ref/create_table_as.sgml

Lines changed: 22 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_table_as.sgml,v 1.13 2003/05/04 00:03:55 tgl Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_table_as.sgml,v 1.14 2003/07/01 00:04:31 petere Exp $
33
PostgreSQL documentation
44
-->
55

@@ -26,11 +26,12 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable>table_name
2626

2727
<para>
2828
<command>CREATE TABLE AS</command> creates a table and fills it
29-
with data computed by a <command>SELECT</command> command. The
30-
table columns have the names and data types associated with the
31-
output columns of the <command>SELECT</command> (except that you
32-
can override the column names by giving an explicit list of new
33-
column names).
29+
with data computed by a <command>SELECT</command> command or an
30+
<command>EXECUTE</command> that runs a prepared
31+
<command>SELECT</command> command. The table columns have the
32+
names and data types associated with the output columns of the
33+
<command>SELECT</command> (except that you can override the column
34+
names by giving an explicit list of new column names).
3435
</para>
3536

3637
<para>
@@ -73,7 +74,9 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable>table_name
7374
<para>
7475
The name of a column in the new table. If column names are not
7576
provided, they are taken from the output column names of the
76-
query.
77+
query. If the table is created out of an
78+
<command>EXECUTE</command> command, a column name list can
79+
currently not be specified.
7780
</para>
7881
</listitem>
7982
</varlistentry>
@@ -82,10 +85,12 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable>table_name
8285
<term><replaceable>query</replaceable></term>
8386
<listitem>
8487
<para>
85-
A query statement (that is, a <command>SELECT</command>
86-
command). Refer to
87-
<xref linkend="sql-select" endterm="sql-select-title">
88-
for a description of the allowed syntax.
88+
A query statement (that is, a <command>SELECT</command> command
89+
or an <command>EXECUTE</command> command that runs a prepared
90+
<command>SELECT</command> command). Refer to <xref
91+
linkend="sql-select" endterm="sql-select-title"> or <xref
92+
linkend="sql-execute" endterm="sql-execute-title">,
93+
respectively, for a description of the allowed syntax.
8994
</para>
9095
</listitem>
9196
</varlistentry>
@@ -96,9 +101,11 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable>table_name
96101
<title>Diagnostics</title>
97102

98103
<para>
99-
Refer to <xref linkend="sql-createtable" endterm="sql-createtable-title"> and
100-
<xref linkend="sql-select" endterm="sql-select-title">
101-
for a summary of possible output messages.
104+
Refer to <xref linkend="sql-createtable"
105+
endterm="sql-createtable-title">, <xref linkend="sql-select"
106+
endterm="sql-select-title">, and <xref linkend="sql-execute"
107+
endterm="sql-execute-title"> for a summary of possible output
108+
messages.
102109
</para>
103110
</refsect1>
104111

@@ -131,6 +138,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable>table_name
131138
<simplelist type="inline">
132139
<member><xref linkend="sql-createtable" endterm="sql-createtable-title"></member>
133140
<member><xref linkend="sql-createview" endterm="sql-createview-title"></member>
141+
<member><xref linkend="sql-execute" endterm="sql-execute-title"></member>
134142
<member><xref linkend="sql-select" endterm="sql-select-title"></member>
135143
<member><xref linkend="sql-selectinto" endterm="sql-selectinto-title"></member>
136144
</simplelist>

‎doc/src/sgml/ref/execute.sgml

Lines changed: 2 additions & 21 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/execute.sgml,v 1.4 2003/04/26 23:56:51 petere Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/execute.sgml,v 1.5 2003/07/01 00:04:31 petere Exp $
33
PostgreSQL documentation
44
-->
55

@@ -16,7 +16,7 @@ PostgreSQL documentation
1616

1717
<refsynopsisdiv>
1818
<synopsis>
19-
EXECUTE <replaceable class="PARAMETER">plan_name</replaceable> [ (<replaceable class="PARAMETER">parameter</replaceable> [, ...] ) ] [ INTO [ TEMPORARY | TEMP ] <replaceable class="PARAMETER">table</replaceable> ]
19+
EXECUTE <replaceable class="PARAMETER">plan_name</replaceable> [ (<replaceable class="PARAMETER">parameter</replaceable> [, ...] ) ]
2020
</synopsis>
2121
</refsynopsisdiv>
2222

@@ -40,13 +40,6 @@ EXECUTE <replaceable class="PARAMETER">plan_name</replaceable> [ (<replaceable c
4040
name of a prepared statement must be unique within a database session.
4141
</para>
4242

43-
<para>
44-
Like <command>SELECT INTO</command>, <command>EXECUTE</command> can
45-
store the results of executing a query into a newly-created
46-
table, by specifying an <literal>INTO</> clause. For more information on this behavior,
47-
see <xref linkend="sql-selectinto" endterm="sql-selectinto-title">.
48-
</para>
49-
5043
<para>
5144
For more information on the creation and usage of prepared statements,
5245
see <xref linkend="sql-prepare" endterm="sql-prepare-title">.
@@ -78,18 +71,6 @@ EXECUTE <replaceable class="PARAMETER">plan_name</replaceable> [ (<replaceable c
7871
</para>
7972
</listitem>
8073
</varlistentry>
81-
82-
<varlistentry>
83-
<term><replaceable class="PARAMETER">table</replaceable></term>
84-
<listitem>
85-
<para>
86-
The name of the table in which to store the results of executing
87-
the statement (if it is a <command>SELECT</command>). If no
88-
table is specified, the results are returned to the client (as
89-
normal).
90-
</para>
91-
</listitem>
92-
</varlistentry>
9374
</variablelist>
9475
</refsect1>
9576

‎src/backend/commands/prepare.c

Lines changed: 8 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
* Copyright (c) 2002-2003, PostgreSQL Global Development Group
1111
*
1212
* IDENTIFICATION
13-
* $Header: /cvsroot/pgsql/src/backend/commands/prepare.c,v 1.18 2003/05/08 18:16:36 tgl Exp $
13+
* $Header: /cvsroot/pgsql/src/backend/commands/prepare.c,v 1.19 2003/07/01 00:04:31 petere Exp $
1414
*
1515
*-------------------------------------------------------------------------
1616
*/
@@ -140,10 +140,10 @@ ExecuteQuery(ExecuteStmt *stmt, DestReceiver *dest)
140140
portal = CreateNewPortal();
141141

142142
/*
143-
* For EXECUTE INTO, make a copy of the stored query so that we can
144-
* modify its destination (yech, but INTO has always been ugly).
145-
* For regular EXECUTE we can just use the stored query where it sits,
146-
* since the executor is read-only.
143+
* For CREATE TABLE / AS EXECUTE, make a copy of the stored query
144+
* so that we can modify its destination (yech, but this has
145+
* always been ugly). For regular EXECUTE we can just use the
146+
* stored query where it sits, since the executor is read-only.
147147
*/
148148
if (stmt->into)
149149
{
@@ -159,10 +159,10 @@ ExecuteQuery(ExecuteStmt *stmt, DestReceiver *dest)
159159
qcontext = PortalGetHeapMemory(portal);
160160

161161
if (length(query_list) != 1)
162-
elog(ERROR, "INTO clause specified for non-SELECT query");
162+
elog(ERROR, "prepared statement is not a SELECT");
163163
query = (Query *) lfirst(query_list);
164164
if (query->commandType != CMD_SELECT)
165-
elog(ERROR, "INTO clause specified for non-SELECT query");
165+
elog(ERROR, "prepared statement is not a SELECT");
166166
query->into = copyObject(stmt->into);
167167

168168
MemoryContextSwitchTo(oldContext);
@@ -519,7 +519,7 @@ ExplainExecuteQuery(ExplainStmt *stmt, TupOutputState *tstate)
519519
if (execstmt->into)
520520
{
521521
if (query->commandType != CMD_SELECT)
522-
elog(ERROR, "INTO clause specified for non-SELECT query");
522+
elog(ERROR, "prepared statement is not a SELECT");
523523

524524
/* Copy the query so we can modify it */
525525
query = copyObject(query);

‎src/backend/parser/gram.y

Lines changed: 16 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
*
1212
*
1313
* IDENTIFICATION
14-
* $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.423 2003/06/29 00:33:43 tgl Exp $
14+
* $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.424 2003/07/01 00:04:31 petere Exp $
1515
*
1616
* HISTORY
1717
* AUTHOR DATE MAJOR EVENT
@@ -4116,17 +4116,29 @@ PreparableStmt:
41164116

41174117
/*****************************************************************************
41184118
*
4119-
* QUERY:
4120-
* EXECUTE <plan_name> [(params, ...)] [INTO ...]
4119+
* EXECUTE <plan_name> [(params, ...)]
4120+
* CREATE TABLE <name> AS EXECUTE <plan_name> [(params, ...)]
41214121
*
41224122
*****************************************************************************/
41234123

4124-
ExecuteStmt: EXECUTE name execute_param_clause into_clause
4124+
ExecuteStmt: EXECUTE name execute_param_clause
41254125
{
41264126
ExecuteStmt *n = makeNode(ExecuteStmt);
41274127
n->name = $2;
41284128
n->params = $3;
4129+
n->into = NULL;
4130+
$$ = (Node *) n;
4131+
}
4132+
| CREATE OptTemp TABLE qualified_name OptCreateAs AS EXECUTE name execute_param_clause
4133+
{
4134+
ExecuteStmt *n = makeNode(ExecuteStmt);
4135+
n->name = $8;
4136+
n->params = $9;
4137+
$4->istemp = $2;
41294138
n->into = $4;
4139+
if ($5)
4140+
elog(ERROR, "column name list not allowed in CREATE TABLE / AS EXECUTE");
4141+
/* ... because it's not implemented, but it could be */
41304142
$$ = (Node *) n;
41314143
}
41324144
;

‎src/test/regress/expected/prepare.out

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -80,10 +80,10 @@ ERROR: Parameter $3 of type boolean cannot be coerced into the expected type do
8080
-- invalid type
8181
PREPARE q4(nonexistenttype) AS SELECT $1;
8282
ERROR: Type "nonexistenttype" does not exist
83-
-- execute into
83+
-- create table as execute
8484
PREPARE q5(int, text) AS
8585
SELECT * FROM tenk1 WHERE unique1 = $1 OR stringu1 = $2;
86-
EXECUTE q5(200, 'DTAAAA') INTO TEMPORARY q5_prep_results;
86+
CREATE TEMPORARY TABLE q5_prep_results AS EXECUTE q5(200, 'DTAAAA');
8787
SELECT * FROM q5_prep_results;
8888
unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4
8989
---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------

‎src/test/regress/sql/prepare.sql

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -38,8 +38,8 @@ EXECUTE q3(5::smallint, 10.5::float, false, 500::oid, 4::bigint, 'bytea');
3838
-- invalid type
3939
PREPARE q4(nonexistenttype) AS SELECT $1;
4040

41-
-- execute into
41+
-- create table as execute
4242
PREPARE q5(int, text) AS
4343
SELECT * FROM tenk1 WHERE unique1 = $1 OR stringu1 = $2;
44-
EXECUTE q5(200, 'DTAAAA') INTO TEMPORARY q5_prep_results;
44+
CREATE TEMPORARY TABLE q5_prep_results AS EXECUTE q5(200, 'DTAAAA');
4545
SELECT * FROM q5_prep_results;

0 commit comments

Comments
 (0)