From: Mike Rylander <miker@purplefrog.com>
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Cc: pgsql-general@postgresql.org
Cc: postgres SQL <pgsql-sql@postgresql.org>
Subject: Re: [HACKERS] MERGE-esque Functionallity (was: Adding MERGE to the TODO list (resend with subject))
Date: Fri, 14 May 2004 14:04:20 -0400
Message-ID: <200405141402.07429.miker@purplefrog.com> (raw)
In-Reply-To: <200405111344.i4BDiQj06463@candle.pha.pa.us>
References: <200405111344.i4BDiQj06463@candle.pha.pa.us>
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
Attachments:merge_on_insert.sql (text/x-sql, 2.4K) download
--
-- 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;
';
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: miker@purplefrog.com, pgsql-hackers@postgresql.org, pgsql-general@postgresql.org
Subject: Re: [HACKERS] MERGE-esque Functionallity (was: Adding MERGE to the TODO list (resend with subject))
In-Reply-To: <200405141402.07429.miker@purplefrog.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox