DELETE and JOIN

[Alexander Farber <alexander.farber@xxxxxxxxx>]

Good evening,

In a 9.5 database I would like players to rate each other and save the reviews in the table:

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

while user names and IP addresses are saved in the other database:

CREATE TABLE words_users (
        uid SERIAL PRIMARY KEY,
        ip inet NOT NULL,
        ..........
);

However, before saving a review, I would like to delete all previous reviews coming from the same IP in the past 24 hours:

CREATE OR REPLACE FUNCTION words_review_user(
        in_uid integer,                /* this user is being rated */
        in_author integer,           /* by the in_author user */
        in_nice integer,
        in_review varchar
        ) RETURNS void AS
$func$
DECLARE
        _author_rep integer;
        _author_ip integer;
BEGIN

        /* find the current IP address of the author */

        SELECT  ip
        INTO    _author_ip
        FROM    words_users
        WHERE   uid = in_author;

        /* try to prevent review fraud - how to improve this query please? */

        DELETE  FROM words_reviews
        WHERE   uid = in_uid
        AND     AGE(updated) < INTERVAL '1 day'
        AND     EXISTS (
                SELECT 1
                FROM words_reviews r INNER JOIN words_users u USING(uid)
                WHERE u.ip = u._author_ip
                AND r.author = 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 (
                        author,
                        nice,
                        review,
                        updated
                ) VALUES (
                        in_author,
                        in_nice,
                        in_review,
                        CURRENT_TIMESTAMP
                );
        END IF;

END
$func$ LANGUAGE plpgsql;

I have the feeling that the _author_ip variable is not really necessary and I could use some kind of "DELETE JOIN" here, but can not figure it out.

Please advise a better query if possible

Best regards

Alex