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

contempating installing a insert,update trigger on forums_topics table something like

SET search_path = public;

CREATE TRIGGER "MyTableName_Trig"
AFTER INSERT OR DELETE OR UPDATE ON "forum_topics"
FOR EACH ROW EXECUTE PROCEDURE "FORUM_CATCH_UP_ALL" ();

HTH
Martin
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

---

Date: Sat, 27 Jun 2009 21:13:23 -0400
From: justin@emproshunts.com
To: apseudoutopia@gmail.com; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Switching from MySQL: ON DUPLICATE KEY UPDATE, plpgsql function

APseudoUtopia wrote:

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:
  

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 <= FormTimeStamp .

I don't see the point having this table when a view would work better. 

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;
-----------------------------
  

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
There is no reason to do a loop in the function waiting for a lock to clear.   Postgresql Locks do not work like MySQL. 

CREATE FUNCTION FORUM_CATCH_UP_ALL (pUserID INTEGER) RETURNS VOID AS
$FuncTag$
	BEGIN

                      select lastpostread  from forums_readposts  where userid = pUserId;
                      if (  found() ) then
                         UPDATE forums_readposts SET "lastpostread" = (SELECT lastpost FROM forums_topics WHERE blah blah) ; --its helpful to post the entire function ;
                      else
                         INSERT INTO forums_readposts ( userid, threadid,
                                    lastpostread) (SELECT $1, id, lastpost FROM forums_topics)
                                    WHERE userid = pUserID;
                      end;

	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.
  

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.....

---

Lauren found her dream laptop. Find the PC that’s right for you.