Re: [HACKERS] MERGE-esque Functionallity (was: Adding MERGE to the TODO list (resend with subject))

[Mike Rylander <miker@purplefrog.com>]

On Tuesday 11 May 2004 09:44 am, Bruce Momjian wrote:
[snip]
> > > > Bruce Momjian kirjutas E, 10.05.2004 kell 06:58:
> > > > > Added to TODO:
> > > > >
> > > > > 	* Add MERGE command that does UPDATE, or on failure, INSERT
> > > >
[snip]

Hello all.

I have been lurking here for a bit and the MERGE topic (on [HACKERS]) caught 
my eye, so I had a go at implementing a basic version of MERGE-on-INSERT in 
pl/pgsql.  It is attached below, and any comments are welcome.  I find it 
useful on "status" type tables, though it is not very nice when there are 
many clients (table locking to avoid race conditions).

Hope someone will find it useful!

-miker








-- 
-- Merge on INSERT functionallity for Postgres 7.3+
-- 
-- miker@purplefrog.com / 5-14-04
-- 
-- CAVEAT EMPTOR: Uses table locks to avoid concurrency issues,
-- 		  so it WILL slow down heavily loaded tables.
-- 		  This effecivly puts the table into
-- 		  TRANSACTION ISOLATION LEVEL SERIALIZABLE mode.
-- 

CREATE OR REPLACE FUNCTION add_merge_on_insert (
	TEXT,  -- table name
	TEXT,  -- key column
	TEXT[] -- column list to update on deduplication
 ) RETURNS TEXT
   RETURNS NULL ON NULL INPUT
   SECURITY INVOKER
   LANGUAGE 'plpgsql'
   AS '

DECLARE
	tablename	ALIAS FOR $1;
	keycol		ALIAS FOR $2;
	updatecols	ALIAS FOR $3;
	trig		TEXT;
	arraydims	TEXT;

BEGIN
	trig := \'
		CREATE FUNCTION "\' || tablename || \'_merge_on_insert_proc" () RETURNS TRIGGER AS \'\'
		DECLARE
			orig \' || quote_ident(tablename) || \'%ROWTYPE;
		BEGIN
			LOCK TABLE \' || quote_ident(tablename) || \' IN ROW EXCLUSIVE MODE;

			SELECT INTO orig * FROM  \' || quote_ident(tablename) || \' WHERE \' || quote_ident(keycol)    || \' = NEW.\' || quote_ident(keycol) || \';

			IF NOT FOUND THEN
				RETURN NEW;
			END IF;

			UPDATE \' || quote_ident(tablename) || \' SET \'; 

	arraydims := array_dims(updatecols);
	FOR i IN 1 .. (substring(arraydims from (position(\':\' in arraydims) + 1 ) for ( position(\']\' in arraydims) - (position(\':\' in arraydims) + 1 ) )))::INT LOOP
		trig := trig || quote_ident(updatecols[i]) || \' = COALESCE( NEW.\' || quote_ident(updatecols[i]) || \', orig.\' || quote_ident(updatecols[i]) || \'), \';
	END LOOP;

	trig := substring( trig from 0 for (character_length(trig) - 1));

	trig := trig || \' WHERE \' || quote_ident(keycol)    || \' = NEW.\' || quote_ident(keycol) || \';
			RETURN NULL;
		END;
		\'\' LANGUAGE \'\'plpgsql\'\';
	\';

	EXECUTE trig;
	EXECUTE \'
		CREATE TRIGGER "\' || tablename || \'_merge_on_insert_trig" BEFORE INSERT
			ON \' || quote_ident(tablename) || \' FOR EACH ROW
			EXECUTE PROCEDURE "\' || tablename || \'_merge_on_insert_proc" ();
	\';

	RETURN \'FUNCTION \' || tablename || \'_merge_on_insert_proc (); TRIGGER \' || tablename || \'_merge_on_insert_trig;\';
END;

';

CREATE OR REPLACE FUNCTION remove_merge_on_insert (
	TEXT  -- table name
 ) RETURNS TEXT
   RETURNS NULL ON NULL INPUT
   SECURITY INVOKER
   LANGUAGE 'plpgsql'
   AS '

BEGIN
	EXECUTE \'DROP FUNCTION "\' || $1 || \'_merge_on_insert_proc" () CASCADE;\';
	RETURN \'FUNCTION \' || $1 || \'_merge_on_insert_proc (); TRIGGER \' || $1 || \'_merge_on_insert_trig;\';
END;

';

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match