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

From: justin <justin(at)emproshunts(dot)com>
To: "APseudoUtopia" <apseudoutopia(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Switching from MySQL: ON DUPLICATE KEY UPDATE, plpgsql function
Date: 2009-06-28 01:13:23
Message-ID: 4A46C3B3.5060405@emproshunts.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
APseudoUtopia wrote:
<blockquote
cite="mid:27ade5280906271723s2621198eo66c944065e710fc4(at)mail(dot)gmail(dot)com"
type="cite">
<pre wrap="">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.  I would add a field in the user table called 
last_login type timestamp  then do a select from the forums table to
generate this table where last_login &lt;= FormTimeStamp .<br>
<br>
I don't see the point having this table when a view would work better. 
<br>
<br>
<br>
<br>
<blockquote
cite="mid:27ade5280906271723s2621198eo66c944065e710fc4(at)mail(dot)gmail(dot)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 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
</pre>
</blockquote>
<br>
<blockquote
cite="mid:27ade5280906271723s2621198eo66c944065e710fc4(at)mail(dot)gmail(dot)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 to try 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 see if
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 function waiting 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" =
(SELECT lastpost 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(at)mail(dot)gmail(dot)com"
type="cite">
<pre wrap="">
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 think this website tracks the last time the user logged
in correct???  This is going to create allot of over head maintaining
this table when a simple select statement will work so much better if i
understand what you are doing.....<br>
<br>
<br>
</body>
</html>

Attachment Content-Type Size
unknown_filename text/html 4.2 KB

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martin Gainty 2009-06-28 01:34:27 Re: Switching from MySQL: ON DUPLICATE KEY UPDATE, plpgsql function
Previous Message V S P 2009-06-28 01:09:03 [Q] sequence and index name limits in 8.4