Switching from MySQL: ON DUPLICATE KEY UPDATE, plpgsql function

Поиск
Список
Период
Сортировка
От APseudoUtopia
Тема Switching from MySQL: ON DUPLICATE KEY UPDATE, plpgsql function
Дата
Msg-id 27ade5280906271723s2621198eo66c944065e710fc4@mail.gmail.com
обсуждение исходный текст
Ответы Re: Switching from MySQL: ON DUPLICATE KEY UPDATE, plpgsql function  (justin <justin@emproshunts.com>)
Re: Switching from MySQL: ON DUPLICATE KEY UPDATE, plpgsql function  (David Kerr <dmk@mr-paradox.net>)
Список pgsql-general
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.

В списке pgsql-general по дате отправления:

Предыдущее
От: Scott Marlowe
Дата:
Сообщение: Re: Free OLAP software for Postgres databas
Следующее
От: "V S P"
Дата:
Сообщение: [Q] sequence and index name limits in 8.4