Re: Switching from MySQL: ON DUPLICATE KEY UPDATE, plpgsql function
От | justin |
---|---|
Тема | Re: Switching from MySQL: ON DUPLICATE KEY UPDATE, plpgsql function |
Дата | |
Msg-id | 4A46C3B3.5060405@emproshunts.com обсуждение исходный текст |
Ответ на | Switching from MySQL: ON DUPLICATE KEY UPDATE, plpgsql function (APseudoUtopia <apseudoutopia@gmail.com>) |
Ответы |
Re: Switching from MySQL: ON DUPLICATE KEY UPDATE,
plpgsql function
(Martin Gainty <mgainty@hotmail.com>)
Re: Switching from MySQL: ON DUPLICATE KEY UPDATE, plpgsql function (Greg Stark <gsstark@mit.edu>) Re: Switching from MySQL: ON DUPLICATE KEY UPDATE, plpgsql function (APseudoUtopia <apseudoutopia@gmail.com>) |
Список | pgsql-general |
APseudoUtopia wrote: <blockquote cite="mid:27ade5280906271723s2621198eo66c944065e710fc4@mail.gmail.com" type="cite"><prewrap="">Hey list, 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: </pre></blockquote> Wouldn't a view be better than having a table that is deleted and updated all the time. Iwould add a field in the user table called last_login type timestamp then do a select from the forums table to generatethis table where last_login <= FormTimeStamp .<br /><br /> I don't see the point having this table when a viewwould work better. <br /><br /><br /><br /><blockquote cite="mid:27ade5280906271723s2621198eo66c944065e710fc4@mail.gmail.com"type="cite"><pre wrap=""> 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 subqueryor 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 </pre></blockquote><br /><blockquotecite="mid:27ade5280906271723s2621198eo66c944065e710fc4@mail.gmail.com" type="cite"><pre wrap=""> -- 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 totry the update again END; END LOOP;END; $FuncTag$ LANGUAGE plpgsql; ----------------------------- </pre></blockquote><br /> if you want to do something like this either do a test first to seeif the key is present in the table, update or do an insert like this<br /> There is no reason to do a loop in the functionwaiting for a lock to clear. Postgresql Locks do not work like MySQL. <br /><pre wrap="">CREATE FUNCTION FORUM_CATCH_UP_ALL(pUserID INTEGER) RETURNS VOID AS $FuncTag$BEGIN</pre> select lastpostread from forums_readposts where userid = pUserId;<br /> if ( found() ) then<br /> UPDATE forums_readposts SET "lastpostread" = (SELECTlastpost FROM forums_topics WHERE blah blah) ; --its helpful to post the entire function ;<br /> else<br /> INSERT INTO forums_readposts ( userid, threadid,<br /> lastpostread) (SELECT $1, id, lastpost FROM forums_topics)<br /> WHERE userid= pUserID;<br /> end;<br /><pre wrap=""> END; $FuncTag$ LANGUAGE plpgsql;</pre><br /><blockquote cite="mid:27ade5280906271723s2621198eo66c944065e710fc4@mail.gmail.com" type="cite"><prewrap=""> 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. </pre></blockquote><br /> Creating a view would work better and than creating a table to track this. I would thinkthis website tracks the last time the user logged in correct??? This is going to create allot of over head maintainingthis table when a simple select statement will work so much better if i understand what you are doing.....<br/><br /><br />
В списке pgsql-general по дате отправления:
Следующее
От: Martin GaintyДата:
Сообщение: Re: Switching from MySQL: ON DUPLICATE KEY UPDATE, plpgsql function