Hello hello,
Here's a rebased and updated patch for $SUBJECT for the September commit
fest.
.m
*** a/doc/src/sgml/ref/insert.sgml
--- b/doc/src/sgml/ref/insert.sgml
***************
*** 22,33 **** PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
[ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
! INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ AS <replaceable class="parameter">alias</replaceable> ] [ ( <replaceable class="PARAMETER">column_name</replaceable> [, ...] ) ]
! { DEFAULT VALUES | VALUES ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) [, ...] | <replaceable class="PARAMETER">query</replaceable> }
[ ON CONFLICT [ <replaceable class="parameter">conflict_target</replaceable> ] <replaceable class="parameter">conflict_action</replaceable> ]
[ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
! <phrase>where <replaceable class="parameter">conflict_target</replaceable> can be one of:</phrase>
( { <replaceable class="parameter">index_column_name</replaceable> | ( <replaceable class="parameter">index_expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [, ...] ) [ WHERE <replaceable class="PARAMETER">index_predicate</replaceable> ]
ON CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable>
--- 22,42 ----
<refsynopsisdiv>
<synopsis>
[ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
! INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ AS <replaceable class="parameter">alias</replaceable> ]
! {
! [ column_list ] VALUES ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) [, ...] |
! [ column_list ] <replaceable class="PARAMETER">query</replaceable> |
! DEFAULT VALUES |
! SET <replaceable class="PARAMETER">column_name</replaceable> = { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...]
! }
[ ON CONFLICT [ <replaceable class="parameter">conflict_target</replaceable> ] <replaceable class="parameter">conflict_action</replaceable> ]
[ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
! <phrase>where <replaceable class="parameter">column_list</replaceable> is:</phrase>
!
! ( <replaceable class="PARAMETER">column_name</replaceable> [, ...] )
!
! <phrase>and <replaceable class="parameter">conflict_target</replaceable> can be one of:</phrase>
( { <replaceable class="parameter">index_column_name</replaceable> | ( <replaceable class="parameter">index_expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [, ...] ) [ WHERE <replaceable class="PARAMETER">index_predicate</replaceable> ]
ON CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable>
***************
*** 53,65 **** INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ AS <replac
</para>
<para>
! The target column names can be listed in any order. If no list of
! column names is given at all, the default is all the columns of the
! table in their declared order; or the first <replaceable>N</> column
! names, if there are only <replaceable>N</> columns supplied by the
! <literal>VALUES</> clause or <replaceable>query</>. The values
! supplied by the <literal>VALUES</> clause or <replaceable>query</> are
! associated with the explicit or implicit column list left-to-right.
</para>
<para>
--- 62,87 ----
</para>
<para>
! The target column names in a <replaceable>column_list</> can be
! listed in any order. If no <replaceable>column_list</> is given at
! all (and the <literal>SET</> syntax is not used), the default is all
! the columns of the table in their declared order; or the first
! <replaceable>N</> column names, if there are only <replaceable>N</>
! columns supplied by the <literal>VALUES</> clause or
! <replaceable>query</>. The values supplied by the <literal>VALUES</>
! clause or <replaceable>query</> are associated with the explicit or
! implicit column list left-to-right.
! </para>
!
! <para>
! Instead of a <replaceable>column_list</> and a <literal>VALUES</>
! clause, a <literal>SET</> clause similar to that of an
! <command>UPDATE</command> can be used instead. The advantage of the
! <literal>SET</> clause is that instead of matching the elements in
! the two lists by ordinal position, the column name and the
! expression to assign to that column are visually next to each other.
! This can make long column assignment lists significantly more
! readable.
</para>
<para>
***************
*** 690,702 **** INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
<para>
<command>INSERT</command> conforms to the SQL standard, except that
the <literal>RETURNING</> clause is a
! <productname>PostgreSQL</productname> extension, as is the ability
! to use <literal>WITH</> with <command>INSERT</>, and the ability to
! specify an alternative action with <literal>ON CONFLICT</>.
! Also, the case in
! which a column name list is omitted, but not all the columns are
! filled from the <literal>VALUES</> clause or <replaceable>query</>,
! is disallowed by the standard.
</para>
<para>
--- 712,724 ----
<para>
<command>INSERT</command> conforms to the SQL standard, except that
the <literal>RETURNING</> clause is a
! <productname>PostgreSQL</productname> extension, as is the
! <literal>SET</> clause when used instead of a VALUES clause, the
! ability to use <literal>WITH</> with <command>INSERT</>, and the
! ability to specify an alternative action with <literal>ON
! CONFLICT</>. Also, the case in which a column name list is omitted,
! but not all the columns are filled from the <literal>VALUES</> clause
! or <replaceable>query</>, is disallowed by the standard.
</para>
<para>
*** a/src/backend/parser/analyze.c
--- b/src/backend/parser/analyze.c
***************
*** 467,474 **** transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
stmt->onConflictClause->action == ONCONFLICT_UPDATE);
/*
! * We have three cases to deal with: DEFAULT VALUES (selectStmt == NULL),
! * VALUES list, or general SELECT input. We special-case VALUES, both for
* efficiency and so we can handle DEFAULT specifications.
*
* The grammar allows attaching ORDER BY, LIMIT, FOR UPDATE, or WITH to a
--- 467,475 ----
stmt->onConflictClause->action == ONCONFLICT_UPDATE);
/*
! * We have four cases to deal with: DEFAULT VALUES (selectStmt == NULL and
! * cols == NIL), SET syntax (selectStmt == NULL but cols != NIL), VALUES
! * list, or general SELECT input. We special-case VALUES, both for
* efficiency and so we can handle DEFAULT specifications.
*
* The grammar allows attaching ORDER BY, LIMIT, FOR UPDATE, or WITH to a
***************
*** 523,529 **** transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
/*
* Determine which variant of INSERT we have.
*/
! if (selectStmt == NULL)
{
/*
* We have INSERT ... DEFAULT VALUES. We can handle this case by
--- 524,530 ----
/*
* Determine which variant of INSERT we have.
*/
! if (selectStmt == NULL && stmt->cols == NIL)
{
/*
* We have INSERT ... DEFAULT VALUES. We can handle this case by
***************
*** 532,537 **** transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
--- 533,557 ----
*/
exprList = NIL;
}
+ else if (selectStmt == NULL)
+ {
+ /*
+ * INSERT INTO ... SET syntax.
+ */
+ Assert(stmt->cols != NIL);
+
+ stmt->cols = transformUpdateTargetList(pstate, stmt->cols);
+
+ exprList = NIL;
+ foreach(lc, stmt->cols)
+ {
+ TargetEntry *rt = (TargetEntry *) lfirst(lc);
+
+ Assert(IsA(rt, TargetEntry));
+
+ exprList = lappend(exprList, rt->expr);
+ }
+ }
else if (isGeneralSelect)
{
/*
***************
*** 2179,2185 **** transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
/*
* transformUpdateTargetList -
! * handle SET clause in UPDATE/INSERT ... ON CONFLICT UPDATE
*/
static List *
transformUpdateTargetList(ParseState *pstate, List *origTlist)
--- 2199,2206 ----
/*
* transformUpdateTargetList -
! * handle SET clause in UPDATE / INSERT ... ON CONFLICT UPDATE / INSERT INTO
! * ... SET
*/
static List *
transformUpdateTargetList(ParseState *pstate, List *origTlist)
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 362,368 **** static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
qualified_name_list any_name any_name_list type_name_list
any_operator expr_list attrs
target_list opt_target_list insert_column_list set_target_list
! set_clause_list set_clause multiple_set_clause
ctext_expr_list ctext_row def_list operator_def_list indirection opt_indirection
reloption_list group_clause TriggerFuncArgs select_limit
opt_select_limit opclass_item_list opclass_drop_list
--- 362,368 ----
qualified_name_list any_name any_name_list type_name_list
any_operator expr_list attrs
target_list opt_target_list insert_column_list set_target_list
! insert_set_clause_list set_clause_list set_clause multiple_set_clause
ctext_expr_list ctext_row def_list operator_def_list indirection opt_indirection
reloption_list group_clause TriggerFuncArgs select_limit
opt_select_limit opclass_item_list opclass_drop_list
***************
*** 9661,9666 **** insert_rest:
--- 9661,9672 ----
$$->cols = $2;
$$->selectStmt = $4;
}
+ | SET insert_set_clause_list
+ {
+ $$ = makeNode(InsertStmt);
+ $$->cols = $2;
+ $$->selectStmt = NULL;
+ }
| DEFAULT VALUES
{
$$ = makeNode(InsertStmt);
***************
*** 9687,9692 **** insert_column_item:
--- 9693,9709 ----
}
;
+ /*
+ * This is different from set_clause_list used in UPDATE because the SelectStmt
+ * syntax already does everything you might want to do in an in INSERT.
+ */
+ insert_set_clause_list:
+ single_set_clause
+ { $$ = list_make1($1); }
+ | insert_set_clause_list ',' single_set_clause
+ { $$ = lappend($1,$3); }
+ ;
+
opt_on_conflict:
ON CONFLICT opt_conf_expr DO UPDATE SET set_clause_list where_clause
{
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
***************
*** 406,414 **** typedef struct A_ArrayExpr
* 'AS ColumnLabel' clause, or NULL if there was none, and 'val' is the
* value expression itself. The 'indirection' field is not used.
*
! * INSERT uses ResTarget in its target-column-names list. Here, 'name' is
! * the name of the destination column, 'indirection' stores any subscripts
! * attached to the destination, and 'val' is not used.
*
* In an UPDATE target list, 'name' is the name of the destination column,
* 'indirection' stores any subscripts attached to the destination, and
--- 406,416 ----
* 'AS ColumnLabel' clause, or NULL if there was none, and 'val' is the
* value expression itself. The 'indirection' field is not used.
*
! * INSERT uses ResTarget in its "cols" list. Here, 'name' is the name of the
! * destination column, 'indirection' stores any subscripts attached to the
! * destination. 'val' is only used if "cols" is from an INSERT ... SET syntax
! * statement; otherwise "cols" only represents the column name list, and thus
! * 'val' is unused.
*
* In an UPDATE target list, 'name' is the name of the destination column,
* 'indirection' stores any subscripts attached to the destination, and
*** a/src/test/regress/expected/insert.out
--- b/src/test/regress/expected/insert.out
***************
*** 80,85 **** select col1, col2, char_length(col3) from inserttest;
--- 80,177 ----
30 | 50 | 10000
(8 rows)
+ --
+ -- insert with SET syntax
+ --
+ truncate inserttest;
+ create type inserttestcomplextype as (f1 int, f2 int);
+ alter table inserttest add column col4 int[];
+ alter table inserttest add column col5 inserttestcomplextype;
+ -- these all fail
+ insert into inserttest set
+ colnonexistent = 1;
+ ERROR: column "colnonexistent" of relation "inserttest" does not exist
+ LINE 2: colnonexistent = 1;
+ ^
+ insert into inserttest set
+ col1 = col1;
+ ERROR: column "col1" does not exist
+ LINE 2: col1 = col1;
+ ^
+ HINT: There is a column named "col1" in table "inserttest", but it cannot be referenced from this part of the query.
+ insert into inserttest set
+ col4[1] = 1,
+ col4 = '{}';
+ ERROR: column "col4" specified more than once
+ LINE 3: col4 = '{}';
+ ^
+ insert into inserttest set
+ col5.nonexistent = 1;
+ ERROR: cannot assign to field "nonexistent" of column "col5" because there is no such column in data type inserttestcomplextype
+ LINE 2: col5.nonexistent = 1;
+ ^
+ insert into inserttest set
+ col5.f1 = 1,
+ col5 = '()';
+ ERROR: column "col5" specified more than once
+ LINE 3: col5 = '()';
+ ^
+ insert into inserttest set
+ col1 = 1,
+ col1 = 1;
+ ERROR: column "col1" specified more than once
+ LINE 3: col1 = 1;
+ ^
+ select * from inserttest;
+ col1 | col2 | col3 | col4 | col5
+ ------+------+------+------+------
+ (0 rows)
+
+ truncate table inserttest;
+ -- defaults
+ insert into inserttest set
+ col1 = default,
+ col2 = default,
+ col3 = default;
+ ERROR: null value in column "col2" violates not-null constraint
+ DETAIL: Failing row contains (null, null, testing, null, null).
+ insert into inserttest set
+ col2 = 3,
+ col3 = default;
+ insert into inserttest set
+ col1 = default,
+ col2 = 5,
+ col3 = default;
+ select * from inserttest;
+ col1 | col2 | col3 | col4 | col5
+ ------+------+---------+------+------
+ | 3 | testing | |
+ | 5 | testing | |
+ (2 rows)
+
+ truncate table inserttest;
+ -- more complex cases
+ insert into inserttest set
+ col2 = (select count(*))::int;
+ insert into inserttest set
+ col2 = 1,
+ col4[1] = 1,
+ -- weird, but accepted
+ col4[1] = 2;
+ insert into inserttest set
+ col2 = 1,
+ col5.f1 = 1,
+ -- weird, but accepted
+ col5.f1 = 2;
+ select * from inserttest;
+ col1 | col2 | col3 | col4 | col5
+ ------+------+---------+------+------
+ | 1 | testing | |
+ | 1 | testing | {2} |
+ | 1 | testing | | (2,)
+ (3 rows)
+
+ truncate table inserttest;
drop table inserttest;
--
-- check indirection (field/array assignment), cf bug #14265
***************
*** 160,162 **** Rules:
--- 252,255 ----
drop table inserttest2;
drop table inserttest;
drop type insert_test_type;
+ drop type inserttestcomplextype;
*** a/src/test/regress/sql/insert.sql
--- b/src/test/regress/sql/insert.sql
***************
*** 35,40 **** insert into inserttest values(30, 50, repeat('x', 10000));
--- 35,102 ----
select col1, col2, char_length(col3) from inserttest;
+ --
+ -- insert with SET syntax
+ --
+ truncate inserttest;
+
+ create type inserttestcomplextype as (f1 int, f2 int);
+ alter table inserttest add column col4 int[];
+ alter table inserttest add column col5 inserttestcomplextype;
+
+ -- these all fail
+ insert into inserttest set
+ colnonexistent = 1;
+ insert into inserttest set
+ col1 = col1;
+ insert into inserttest set
+ col4[1] = 1,
+ col4 = '{}';
+ insert into inserttest set
+ col5.nonexistent = 1;
+ insert into inserttest set
+ col5.f1 = 1,
+ col5 = '()';
+ insert into inserttest set
+ col1 = 1,
+ col1 = 1;
+
+ select * from inserttest;
+ truncate table inserttest;
+
+ -- defaults
+ insert into inserttest set
+ col1 = default,
+ col2 = default,
+ col3 = default;
+ insert into inserttest set
+ col2 = 3,
+ col3 = default;
+ insert into inserttest set
+ col1 = default,
+ col2 = 5,
+ col3 = default;
+
+ select * from inserttest;
+ truncate table inserttest;
+
+ -- more complex cases
+ insert into inserttest set
+ col2 = (select count(*))::int;
+ insert into inserttest set
+ col2 = 1,
+ col4[1] = 1,
+ -- weird, but accepted
+ col4[1] = 2;
+ insert into inserttest set
+ col2 = 1,
+ col5.f1 = 1,
+ -- weird, but accepted
+ col5.f1 = 2;
+
+ select * from inserttest;
+ truncate table inserttest;
+
drop table inserttest;
--
***************
*** 84,86 **** create rule irule3 as on insert to inserttest2 do also
--- 146,149 ----
drop table inserttest2;
drop table inserttest;
drop type insert_test_type;
+ drop type inserttestcomplextype;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
