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

On Mon, Jun 29, 2009 at 2:26 PM, David Kerr<dmk(at)mr-paradox(dot)net> wrote:
> 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
>

Hmm, yeah. I'm new to PostgreSQL, and have little experience with
MySQL other than basic queries and joins. I didn't get to the part in
the docs that covers EXECUTE yet, haha. That looks like it would do
exactly what I want it to.

I appreciate it.