Good morning and thank you for the replies.
I've ended up with the following DELETE USING (in order to delete reviews coming from different user id, but same IP address in the last 24 hours):
DELETE FROM words_reviews r
USING words_users u
WHERE r.uid = u.uid
AND r.uid = in_uid
AND AGE(r.updated) < INTERVAL '1 day'
AND u.ip = (SELECT ip FROM words_users WHERE uid = in_author);
Regards
Alex
PS: Here is my custom function:
CREATE OR REPLACE FUNCTION words_review_user(
in_uid integer, /* the player in_uid... */
in_author integer, /* ... is reviewed by player in_author */
in_nice integer,
in_review varchar
) RETURNS void AS
$func$
BEGIN
DELETE FROM words_reviews r
USING words_users u
WHERE r.uid = u.uid
AND r.uid = in_uid
AND AGE(r.updated) < INTERVAL '1 day'
AND u.ip = (SELECT ip FROM words_users WHERE uid = in_author);
UPDATE words_reviews SET
author = in_author,
nice = in_nice,
review = in_review,
updated = CURRENT_TIMESTAMP
WHERE uid = in_uid AND author = in_author;
IF NOT FOUND THEN
INSERT INTO words_reviews (
uid,
author,
nice,
review,
updated
) VALUES (
in_uid,
in_author,
in_nice,
in_review,
CURRENT_TIMESTAMP
);
END IF;
END
$func$ LANGUAGE plpgsql;
And here are the tables in question:
CREATE TABLE words_reviews (
uid integer NOT NULL CHECK (uid <> author) REFERENCES words_users ON DELETE CASCADE,
author integer NOT NULL REFERENCES words_users(uid) ON DELETE CASCADE,
nice integer NOT NULL CHECK (nice = 0 OR nice = 1),
review varchar(255),
updated timestamptz NOT NULL,
PRIMARY KEY(uid, author)
);
CREATE TABLE words_users (
uid SERIAL PRIMARY KEY,
ip inet NOT NULL,
..........
);
