Message-ID: <44BC661F.80609@joeconway.com>
Date: Mon, 17 Jul 2006 21:39:59 -0700
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.7.13) Gecko/20060501 Fedora/1.7.13-1.1.fc5
MIME-Version: 1.0
CC: Andrew Dunstan <andrew@dunslane.net>, Bernd Helmle <mailings@oopsware.de>,
	Peter Eisentraut <peter_e@gmx.net>,
	"Patches (PostgreSQL)" <pgsql-patches@postgresql.org>,
	Susanne Ebrecht <susanne.ebrecht@credativ.de>
Subject: Re: [HACKERS] 8.2 features?
References: <44B69778.5050104@dunslane.net>
	<50461.84.63.113.55.1152832991.squirrel@pezone.net>
	<ECCAAC0A3182895D3D12B0E8@[172.26.14.247]>
	<44B7ADFC.90205@dunslane.net> <44BC0432.3040509@joeconway.com>
In-Reply-To: <44BC0432.3040509@joeconway.com>
Content-Type: multipart/mixed; boundary="------------000303030705030500020107"

This is a multi-part message in MIME format.
--------------000303030705030500020107
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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

--------------000303030705030500020107
Content-Type: text/x-patch;
 name="multi-insert.diff"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="multi-insert.diff"

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:

--------------000303030705030500020107--