Skip to content

Instantly share code, notes, and snippets.

@touzoku
Last active December 10, 2015 13:49
Show Gist options
  • Save touzoku/4443619 to your computer and use it in GitHub Desktop.
Save touzoku/4443619 to your computer and use it in GitHub Desktop.
crawler
CREATE OR REPLACE FUNCTION new_peer(n character varying, r bigint, c bigint, tp integer, lp integer)
RETURNS bigint AS
$BODY$
DECLARE peerid bigint;
BEGIN
LOOP
-- first try to update the key
UPDATE peers INTO peerid SET lastpage=lp WHERE "name" = n returning id;
IF found THEN
-- SELECT id into peerid from peers where "name"=n;
RETURN peerid;
END IF;
-- not there, so try to insert the key
-- if someone else inserts the same key concurrently,
-- we could get a unique-key failure
BEGIN
INSERT INTO peers("name", "reputation", "capital", "totalpages", "lastpage") values(n, r, 0, tp, lp);
EXCEPTION WHEN unique_violation THEN
-- do nothing, and loop to try the UPDATE again
END;
END LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment