This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a demonstration on how to write merge statement using CTE of PostgreSQL.
PostgreSQL 9.1 has a new feature to write CTE and to use the CTE you can also merge INSERT/UPDATE in an expression.
The merge statement is always a discussion point for Database Developers of MySQL/PostgreSQL.
Below is a full demonstration to insert a record if not exist and update a record if they exist.
Let’s first create a sample table:
|
1 2 3 4 5 |
CREATE TABLE tbl_TestUpsert ( Rno INTEGER ,Name VARCHAR(100) ); |
Now create a Stored Function:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
CREATE OR REPLACE FUNCTION fn_InserUpdateMergeDemo ( InputRno INTEGER ,InputName VARCHAR(100) ) RETURNS integer AS $BODY$ DECLARE BEGIN WITH to_be_upserted (Rno,Name) AS (VALUES(InputRno,InputName)) ,updated AS ( UPDATE tbl_TestUpsert SET Rno=InputRno,Name=InputName FROM to_be_upserted WHERE tbl_TestUpsert.Rno = to_be_upserted.Rno RETURNING tbl_TestUpsert.Rno ) INSERT INTO tbl_TestUpsert(Rno,Name) SELECT Rno,Name FROM to_be_upserted WHERE Rno NOT IN (SELECT Rno FROM updated); RETURN 0; EXCEPTION WHEN OTHERS THEN RAISE; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; |
As you can see in the above function that CTE is created with two input parameters and merge INSERT/UPDATE using an expression.
Now call this function with sample data:
|
1 2 3 4 |
SELECT *FROM fn_InserUpdateMergeDemo(1,'Anvesh'); SELECT *FROM fn_InserUpdateMergeDemo(2,'Neevan'); SELECT *FROM fn_InserUpdateMergeDemo(3,'Ronika'); SELECT *FROM fn_InserUpdateMergeDemo(2,'Roy'); |
You can find the two records have same rno column and “Neevan” updated by “Roy”.
Check this result:
