Switching from MySQL: ON DUPLICATE KEY UPDATE, plpgsql function

[APseudoUtopia <apseudoutopia@xxxxxxxxx>]

Hey list,

I'm migrating my site away from MySQL to PostgreSQL. So far, it's been
going great. However, there's one problem I've been having trouble
solving.

I have a query which allows users to "Catch up" on read posts on the
forum. It works by either updating or inserting the "last post read"
number from every forum thread into the readposts table (for that
userid and threadid combination, of course). Here's the table
structure:

CREATE TABLE "forums_readposts" (
 "userid"    INTEGER NOT NULL REFERENCES "users_main" ("id") ON DELETE CASCADE,
 "threadid"    INTEGER NOT NULL REFERENCES "forums_topics" ("id") ON
DELETE CASCADE,
 "lastpostread"   INTEGER NOT NULL CHECK ("lastpostread" >= 0),
 PRIMARY KEY ("userid", "threadid")
);

Here's the original MySQL query that I have (db_string is a php
function that escapes the string):

INSERT INTO "forums_readposts" ("userid", "threadid", "lastpostread")
SELECT ' . db_string($_SESSION['UserInfo']['id']) . ', "id",
"lastpost" FROM "forums_topics" ON DUPLICATE KEY UPDATE "lastpostread"
= "lastpost";

Obviously this will not work with PostgreSQL. I've googled around a
bit and I decided to create a plpgsql function to handle the task. I
don't have much done, but here's what I have:

-----------------------------
CREATE FUNCTION FORUM_CATCH_UP_ALL (INTEGER) RETURNS VOID AS
$FuncTag$
	BEGIN
		LOOP
			-- Try to update the record
			-- This query is broken. I'm not sure how to do the subquery or
whatever I need to do. Maybe FROM? Another loop?
			-- UPDATE "forums_readposts" SET "lastpostread" = (SELECT
"lastpost" FROM "forums_topics" WHERE blah blah
			IF found THEN
				RETURN;
			END IF;
			-- Not there, try to insert the key
			-- If someone else inserts the same key concurrently,
			-- We could get a unique-key failure
			BEGIN
				INSERT INTO "forums_readposts" ("userid", "threadid",
"lastpostread") (SELECT $1, "id", "lastpost" FROM "forums_topics")
WHERE "userid" = $1;
				RETURN;
			EXCEPTION WHEN unique_violation THEN
				-- Do nothing, and loop to try the update again
			END;
		END LOOP;
	END;
$FuncTag$
LANGUAGE plpgsql;
-----------------------------

I got the structure from the example in the postgresql documentation.
Hopefully it's a step in the right direction.

If anyone can point me in the direction to take another step in, I'd
really appreciate it.

Thanks.

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general