Re: Switching from MySQL: ON DUPLICATE KEY UPDATE, plpgsql function

On Sat, Jun 27, 2009 at 08:23:26PM -0400, APseudoUtopia wrote:
- 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";

So regardless of other design issues. (i.e., assuming what you have was working
in MySQL).

Wouldn't you just be looking for something like:

BEGIN;
EXECUTE 'insert into forums_readposts values ('...')';
EXCEPTION when unique_violation THEN
EXECUTE 'update forums_readposts set lastpostread = '...' ';
END;

The logic as i read your post is. If the user's never done a "catchup" operation
before, this will create the record. If he has, then it will update this record
to reflect the new transid.

Dave