Joe Conway wrote:
Advertising
. multiple values clauses for INSERT
The best way might be to fabricate a selectStmt equiv to
"SELECT <targetlist> UNION ALL SELECT <targetlist>...",
but that still feels like a hack.
Here is a patch pursuant to my earlier post. It has the advantage of
being fairly simple and noninvasive.
The major downside is that somewhere between 9000 and 10000
VALUES-targetlists produces "ERROR: stack depth limit exceeded".
Perhaps for the typical use-case this is sufficient though.
I'm open to better ideas, comments, objections...
Thanks,
Joe
Index: src/backend/parser/gram.y
===================================================================
RCS file: /cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.551
diff -c -r2.551 gram.y
*** src/backend/parser/gram.y 3 Jul 2006 22:45:39 -0000 2.551
--- src/backend/parser/gram.y 18 Jul 2006 04:19:45 -0000
***************
*** 238,251 ****
qualified_name_list any_name any_name_list
any_operator expr_list attrs
target_list update_target_list insert_column_list
! insert_target_list def_list indirection opt_indirection
! group_clause TriggerFuncArgs select_limit
! opt_select_limit opclass_item_list
! transaction_mode_list_or_empty
TableFuncElementList
prep_type_clause prep_type_list
execute_param_clause using_clause
%type <range> into_clause OptTempTableName
%type <defelt> createfunc_opt_item common_func_opt_item
--- 238,253 ----
qualified_name_list any_name any_name_list
any_operator expr_list attrs
target_list update_target_list insert_column_list
! insert_target_els
! def_list indirection opt_indirection group_clause
! TriggerFuncArgs select_limit opt_select_limit
! opclass_item_list transaction_mode_list_or_empty
TableFuncElementList
prep_type_clause prep_type_list
execute_param_clause using_clause
+ %type <node> insert_target_list insert_target_lists
+
%type <range> into_clause OptTempTableName
%type <defelt> createfunc_opt_item common_func_opt_item
***************
*** 5349,5360 ****
;
insert_rest:
! VALUES '(' insert_target_list ')'
{
$$ = makeNode(InsertStmt);
$$->cols = NIL;
! $$->targetList = $3;
! $$->selectStmt = NULL;
}
| DEFAULT VALUES
{
--- 5351,5370 ----
;
insert_rest:
! VALUES insert_target_lists
{
$$ = makeNode(InsertStmt);
$$->cols = NIL;
! if (((SelectStmt *) $2)->op == SETOP_UNION)
! {
! $$->targetList = NIL;
! $$->selectStmt = $2;
! }
! else
! {
! $$->targetList = ((SelectStmt *) $2)->targetList;
! $$->selectStmt = NULL;
! }
}
| DEFAULT VALUES
{
***************
*** 5370,5381 ****
$$->targetList = NIL;
$$->selectStmt = $1;
}
! | '(' insert_column_list ')' VALUES '(' insert_target_list ')'
{
$$ = makeNode(InsertStmt);
$$->cols = $2;
! $$->targetList = $6;
! $$->selectStmt = NULL;
}
| '(' insert_column_list ')' SelectStmt
{
--- 5380,5399 ----
$$->targetList = NIL;
$$->selectStmt = $1;
}
! | '(' insert_column_list ')' VALUES insert_target_lists
{
$$ = makeNode(InsertStmt);
$$->cols = $2;
! if (((SelectStmt *) $5)->op == SETOP_UNION)
! {
! $$->targetList = NIL;
! $$->selectStmt = $5;
! }
! else
! {
! $$->targetList = ((SelectStmt *) $5)->targetList;
! $$->selectStmt = NULL;
! }
}
| '(' insert_column_list ')' SelectStmt
{
***************
*** 8189,8197 ****
;
insert_target_list:
! insert_target_el { $$ = list_make1($1); }
! | insert_target_list ',' insert_target_el { $$ = lappend($1, $3); }
;
insert_target_el:
--- 8207,8235 ----
;
+ insert_target_lists:
+ insert_target_list
+ {
+ $$ = $1;
+ }
+ | insert_target_lists ',' insert_target_list
+ {
+ $$ = makeSetOp(SETOP_UNION, TRUE, $1, $3);
+ }
+ ;
+
insert_target_list:
! '(' insert_target_els ')'
! {
! SelectStmt *n = makeNode(SelectStmt);
! n->targetList = $2;
! $$ = (Node *) n;
! }
! ;
!
! insert_target_els:
! insert_target_el { $$ = list_make1($1); }
! | insert_target_els ',' insert_target_el { $$ = lappend($1, $3); }
;
insert_target_el:
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
