
gSoC2010 projec ADD MERGE COMMAND. 
https://wiki.postgresql.org/wiki/Add_MERGE_command_GSoC_2010

By Zhai Boxuan
and   
Mantor: Heikki Linnakangas

The first submission! 


The target of this project is to add the "MERGE INTO ... " command into the postgres system.

 
In this first submission, we have finished the part of PARSER,ANALYZER, and REWRITER. Generally, this edition can accept the MERGE command and transform it into a structure of "MergeStmt" which contains the information of source table, target table, match conditions. The MERGE actions are transformed into a list of "MergeConditionAction" structure. 

In the analyze.c file, we mainly add two functions to build "Query" for the "MergeStmt". The top level query is created as that of a SELECT query: 
"SELECT * FROM <source_rel> LEFT JOIN <target_rel> ON <match_condition>;"

Here we set the "match condition" as the join qualification. But the left join type will scan both the matched and non-matched tuples. And there is a "target relation" in the Range Table (RT) which is not exist in a normal SELECT query. 

Each of the merge actions is transformed into a Query structure of the corresponding command type. They all share the same RT list of the top level query. We designed it in this way in order to save the cost of space and process.  
 
All these action queries are put in a list field "Query->mergeActQry". This is a newly added field. We don't want to implement these action queries as the sub queries of the top-level query. The action queries bears very different features against the transitional sub-queries in SQL. They are not a part of the FROM list of JOIN TREE. The are even not SELECT queries, and should never be pulled up in planner. 

The whole query is then passed to Rewrite. Here, we reuse the QueryRewrite() function to handle the action queries.

The planner is not changed yet, but it can accept all the queries of MERGE command. And the whole process stops when runs in executor. 

---------------------
You can try the following test commands. 

create table Stock (Stid int, bal int);
create table Sale (Said int, Prog int);

insert into Stock values(10, 12);
insert into Stock values(20, 190);


insert into Sale values(10, 8000);
insert into Sale values(40, 208);

MERGE INTO Stock USING Sale ON Stock.Stid = Sale.Said
WHEN NOT MATCHED THEN INSERT (stid, bal) values (sale.said , Sale.prog)
WHEN MATCHED THEN UPDATE set bal = bal + prog;

---------------------
Our implementation is based on the Psql 8.4.3. And we compile the files under a WINDOWS 7 + MinGW +Msys envriorment. 
This submission only contains the files in /src/backend and /src/include (for save the patch size). The other files are all the same as that of the official psql 8.4.3 pachage. In detail, we have made the following modification.  

------------------------------------------------------
parsenodes.h
typedef struct MergeStmt;
typedef struct MergeConditionAction;

typedef UpdateStmt MergeUpdate;
typedef DeleteStmt MergeDelete;
typedef InsertStmt MergeInsert;

add
List 		*mergeActQry; // the list of all the merge actions. used only for merge query statment
in Query node.
--------------------------------------------------
Nodes.h

add the node tags
T_MergeStmt
T_MergeConditionAction,
	T_MergeUpdate, 
	T_MergeDelete,  
	T_MergeInsert,


add command type
CMD_MERGE
----------------------------------------------
kwlist.h

add keywords
PG_KEYWORD("merge", MERGE, UNRESERVED_KEYWORD)
PG_KEYWORD("matched", MATCHED, UNRESERVED_KEYWORD)
----------------------------------------------------
copyfuncs.c
_copyMergeStmt(MergeStmt *from);
_copyMergeConditionAction(MergeConditionAction *from);

add 
	COPY_NODE_FIELD(mergeActQry); //merge actions 

in function _copyQuery()


add 
case T_MergeStmt:
case T_MergeConditionAction:
in copyObject() function;
----------------------------------------------------
outfuncs.c


case T_MergeStmt:
				_outMergeStmt(str, obj);
				break;
			case T_MergeConditionAction:
				_outMergeConditionAction(str,obj);
				break;

and 
_outDeleteStmt()

add one line in _outQuery();
WRITE_NODE_FIELD(mergeActQry);

----------------------------------------------------
Equalfuncs.c

add one line in _equalQuery();
COMPARE_NODE_FIELD(mergeActQry);



------------------------------------------------------
gram.y

add the command related to merge

MERGE INTO target USING source ....

source is a list ? or a single table_ref??? 

-----------------------------------------------
utility.c

add
case T_MergeStmt:
			tag = "MERGE";
			break;

in CreateCommandTag() function;

add 
case T_MergeStmt:

in GetCommandLogLevel() function;


-------------------------------------------------
parse_clause.h


add a reference:
extern void setTargetTableLock(ParseState *pstate, RangeVar *relation);

------------------------------------------------------
parse_clause.c

add a function:
void setTargetTableLock(ParseState *pstate, RangeVar *relation);

------------------------------------------------------

parser/analyze.c

add functions:

transformMergeActions() and transformMergeStmt()

modified functions:

tranformStmt(), transformDeleteStmt(), transformInsertStmt() and transformUpdateStmt()
for the new node types of T_MergeInsert, T_MergeUpdate, T_MergeDelete;

--------------------------------------------------
rewriteHandler.c

modified function QueryRewrite()
add a case for the merge command;

-----------------------------------------------
