Joe Conway wrote:


. 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

Reply via email to