Re: [GENERAL] DELETE and JOIN
От | Adrian Klaver |
---|---|
Тема | Re: [GENERAL] DELETE and JOIN |
Дата | |
Msg-id | 175ba9da-e534-6933-4cf2-f78cf781a014@aklaver.com обсуждение исходный текст |
Ответ на | [GENERAL] DELETE and JOIN (Alexander Farber <alexander.farber@gmail.com>) |
Список | pgsql-general |
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@aklaver.com
В списке pgsql-general по дате отправления: