Index: doc/src/sgml/ref/create_table_as.sgml
===================================================================
RCS file: /home/neilc/private-cvsroot/pgsql-server/doc/src/sgml/ref/create_table_as.sgml,v
retrieving revision 1.23
diff -c -r1.23 create_table_as.sgml
*** doc/src/sgml/ref/create_table_as.sgml 24 Aug 2004 00:06:51 -0000 1.23
--- doc/src/sgml/ref/create_table_as.sgml 22 Sep 2004 08:41:44 -0000
***************
*** 21,27 ****
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name [ (column_name [, ...] ) ] [ [ WITH | WITHOUT ] OIDS ]
! AS query
--- 21,27 ----
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name [ (column_name [, ...] ) ] [ [ WITH | WITHOUT ] OIDS ]
! AS query [ WITH [ NO ] DATA ]
***************
*** 29,37 ****
Description
! CREATE TABLE AS creates a table and fills it
! with data computed by a SELECT command or an
! EXECUTE that runs a prepared
SELECT command. The table columns have the
names and data types associated with the output columns of the
SELECT (except that you can override the column
--- 29,37 ----
Description
! CREATE TABLE AS creates a table and optionally
! fills it with data computed by a SELECT command
! or an EXECUTE that runs a prepared
SELECT command. The table columns have the
names and data types associated with the output columns of the
SELECT (except that you can override the column
***************
*** 125,130 ****
--- 125,145 ----
+
+
+ WITH DATA
+ WITH NO DATA
+
+
+ This optional clause specifies whether the table created by
+ CREATE TABLE AS should contain any data. If
+ WITH NO DATA is specified, the schema of the
+ new table is created, but no rows are inserted into it. If this
+ clause is not specified, WITH DATA is the
+ default.
+
+
+
***************
*** 166,176 ****
Compatibility
! This command is modeled after an Oracle
! feature. There is no command with equivalent functionality in
! the SQL standard. However, a combination of CREATE
! TABLE and INSERT ... SELECT can
! accomplish the same thing with little more effort.
--- 181,214 ----
Compatibility
! CREATE TABLE AS is specified by the SQL2003
! standard. There are some small differences between the definition
! of the command in SQL2003 and its implementation in
! PostgreSQL:
!
!
!
!
! The WITH DATA clause is mandatory in the
! standard, whereas it is optional in PostgreSQL.
!
!
!
!
!
! The standard requires parentheses around the subquery clause; in
! PostgreSQL, these parentheses are
! optional.
!
!
!
!
!
! The standard defines an ON COMMIT clause;
! this is not currently implemented by PostgreSQL.
!
!
!
Index: src/backend/parser/gram.y
===================================================================
RCS file: /home/neilc/private-cvsroot/pgsql-server/src/backend/parser/gram.y,v
retrieving revision 2.475
diff -c -r2.475 gram.y
*** src/backend/parser/gram.y 29 Aug 2004 04:12:35 -0000 2.475
--- src/backend/parser/gram.y 22 Sep 2004 08:41:48 -0000
***************
*** 321,327 ****
%type constraints_set_list
%type constraints_set_mode
%type OptTableSpace OptConsTableSpace OptTableSpaceOwner
!
/*
* If you make any token changes, update the keyword table in
--- 321,327 ----
%type constraints_set_list
%type constraints_set_mode
%type OptTableSpace OptConsTableSpace OptTableSpaceOwner
! %type OptWithData
/*
* If you make any token changes, update the keyword table in
***************
*** 344,350 ****
CREATEUSER CROSS CSV CURRENT_DATE CURRENT_TIME
CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE
! DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS
DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS
DESC DISTINCT DO DOMAIN_P DOUBLE_P DROP
--- 344,350 ----
CREATEUSER CROSS CSV CURRENT_DATE CURRENT_TIME
CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE
! DATA_P DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS
DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS
DESC DISTINCT DO DOMAIN_P DOUBLE_P DROP
***************
*** 415,421 ****
* list and so can never be entered directly. The filter in parser.c
* creates these tokens when required.
*/
! %token UNIONJOIN
/* Special token types, not actually keywords - see the "lex" file */
%token IDENT FCONST SCONST BCONST XCONST Op
--- 415,421 ----
* list and so can never be entered directly. The filter in parser.c
* creates these tokens when required.
*/
! %token UNIONJOIN WITH_DATA WITH_NO
/* Special token types, not actually keywords - see the "lex" file */
%token IDENT FCONST SCONST BCONST XCONST Op
***************
*** 1961,1967 ****
*/
CreateAsStmt:
! CREATE OptTemp TABLE qualified_name OptCreateAs WithOidsAs SelectStmt
{
/*
* When the SelectStmt is a set-operation tree, we must
--- 1961,1967 ----
*/
CreateAsStmt:
! CREATE OptTemp TABLE qualified_name OptCreateAs WithOidsAs SelectStmt OptWithData
{
/*
* When the SelectStmt is a set-operation tree, we must
***************
*** 1979,1984 ****
--- 1979,1998 ----
n->into = $4;
n->intoColNames = $5;
n->intoHasOids = $6;
+ /*
+ * If "WITH NO DATA" was specified, just create
+ * the table's schema. The kludge we use to
+ * achieve this is to add a "LIMIT 0" clause to
+ * the query.
+ */
+ if ($8 == FALSE)
+ {
+ SelectStmt *stmt = (SelectStmt *) $7;
+ if (stmt->limitCount)
+ pfree(stmt->limitCount);
+ stmt->limitCount = makeIntConst(0);
+ }
+
$$ = $7;
}
;
***************
*** 1995,2000 ****
--- 2009,2025 ----
| AS { $$ = DEFAULT_OIDS; }
;
+ /*
+ * In order to avoid shift/reduce conflicts, we need to play some
+ * games in the lexer (see parser.c) to merge "WITH DATA" and "WITH
+ * NO" into single tokens.
+ */
+ OptWithData:
+ WITH_DATA { $$ = TRUE; }
+ | WITH_NO DATA_P { $$ = FALSE; }
+ | /* EMPTY */ { $$ = TRUE; }
+ ;
+
OptCreateAs:
'(' CreateAsList ')' { $$ = $2; }
| /*EMPTY*/ { $$ = NIL; }
***************
*** 7681,7686 ****
--- 7706,7712 ----
| CSV
| CURSOR
| CYCLE
+ | DATA_P
| DATABASE
| DAY_P
| DEALLOCATE
Index: src/backend/parser/keywords.c
===================================================================
RCS file: /home/neilc/private-cvsroot/pgsql-server/src/backend/parser/keywords.c,v
retrieving revision 1.153
diff -c -r1.153 keywords.c
*** src/backend/parser/keywords.c 29 Aug 2004 04:12:40 -0000 1.153
--- src/backend/parser/keywords.c 22 Sep 2004 08:41:48 -0000
***************
*** 97,102 ****
--- 97,103 ----
{"current_user", CURRENT_USER},
{"cursor", CURSOR},
{"cycle", CYCLE},
+ {"data", DATA_P},
{"database", DATABASE},
{"day", DAY_P},
{"deallocate", DEALLOCATE},
Index: src/backend/parser/parser.c
===================================================================
RCS file: /home/neilc/private-cvsroot/pgsql-server/src/backend/parser/parser.c,v
retrieving revision 1.62
diff -c -r1.62 parser.c
*** src/backend/parser/parser.c 29 Aug 2004 04:12:42 -0000 1.62
--- src/backend/parser/parser.c 22 Sep 2004 08:41:48 -0000
***************
*** 98,104 ****
else
have_lookahead = true;
break;
!
default:
break;
}
--- 98,112 ----
else
have_lookahead = true;
break;
! case WITH:
! /* Look ahead to see if we are doing WITH DATA or WITH NO DATA */
! lookahead_token = base_yylex();
! if(lookahead_token == DATA_P)
! cur_token = WITH_DATA;
! else if(lookahead_token == NO)
! cur_token = WITH_NO;
! else
! have_lookahead = true;
default:
break;
}
Index: src/test/regress/expected/select_into.out
===================================================================
RCS file: /home/neilc/private-cvsroot/pgsql-server/src/test/regress/expected/select_into.out,v
retrieving revision 1.5
diff -c -r1.5 select_into.out
*** src/test/regress/expected/select_into.out 4 Jun 2000 17:52:53 -0000 1.5
--- src/test/regress/expected/select_into.out 22 Sep 2004 08:41:49 -0000
***************
*** 11,13 ****
--- 11,45 ----
FROM onek2
WHERE onek2.unique1 < 2;
DROP TABLE tmp1;
+ --
+ -- CREATE TABLE AS (tested here since it is closely related
+ -- to SELECT INTO)
+ --
+ CREATE TABLE with_data AS SELECT * FROM onek WITH DATA;
+ CREATE TABLE with_no_data AS SELECT * FROM onek WITH NO DATA;
+ SELECT COUNT(*) FROM with_data;
+ count
+ -------
+ 1000
+ (1 row)
+
+ SELECT COUNT(*) FROM with_no_data;
+ count
+ -------
+ 0
+ (1 row)
+
+ DROP TABLE with_data;
+ DROP TABLE with_no_data;
+ CREATE TABLE with_no_data AS
+ SELECT * FROM onek UNION
+ SELECT * FROM onek2
+ ORDER BY 1 LIMIT 100
+ WITH NO DATA;
+ SELECT COUNT(*) FROM with_no_data;
+ count
+ -------
+ 0
+ (1 row)
+
+ DROP TABLE with_no_data;
Index: src/test/regress/sql/select_into.sql
===================================================================
RCS file: /home/neilc/private-cvsroot/pgsql-server/src/test/regress/sql/select_into.sql,v
retrieving revision 1.4
diff -c -r1.4 select_into.sql
*** src/test/regress/sql/select_into.sql 4 Jun 2000 17:52:54 -0000 1.4
--- src/test/regress/sql/select_into.sql 22 Sep 2004 08:41:49 -0000
***************
*** 16,18 ****
--- 16,39 ----
DROP TABLE tmp1;
+ --
+ -- CREATE TABLE AS (tested here since it is closely related
+ -- to SELECT INTO)
+ --
+ CREATE TABLE with_data AS SELECT * FROM onek WITH DATA;
+ CREATE TABLE with_no_data AS SELECT * FROM onek WITH NO DATA;
+
+ SELECT COUNT(*) FROM with_data;
+ SELECT COUNT(*) FROM with_no_data;
+
+ DROP TABLE with_data;
+ DROP TABLE with_no_data;
+
+ CREATE TABLE with_no_data AS
+ SELECT * FROM onek UNION
+ SELECT * FROM onek2
+ ORDER BY 1 LIMIT 100
+ WITH NO DATA;
+
+ SELECT COUNT(*) FROM with_no_data;
+ DROP TABLE with_no_data;
\ No newline at end of file