On 03/13/2017 09:39 AM, Alexander Farber wrote:
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.
The USING clause?:
https://www.postgresql.org/docs/9.5/static/sql-delete.html
"PostgreSQL lets you reference columns of other tables in the WHERE
condition by specifying the other tables in the USING clause. For
example, to delete all films produced by a given producer, one can do:
DELETE FROM films USING producers
WHERE producer_id = producers.id AND producers.name = 'foo';
"
Please advise a better query if possible
Best regards
Alex
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
