DELETE and JOIN

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: DELETE and JOIN
Date: 2017-03-13 16:39:09
Message-ID: CAADeyWhjUZ4RVXAwbMXSF=cxqNgw+nb14FExrq1RMCT8UnFzow@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2017-03-13 16:53:36 Re: DELETE and JOIN
Previous Message Adrian Klaver 2017-03-13 16:27:35 Re: createuser: How to specify a database to connect to