Re: [GENERAL] DELETE and JOIN

[Alexander Farber]

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