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+
--
-- [EMAIL PROTECTED] / 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
