Обсуждение: [GENERAL] DELETE and JOIN

Alexander Farber <alexander.farber@gmail.com> writes:
> ...
> However, before saving a review, I would like to delete all previous
> reviews coming from the same IP in the past 24 hours:
> ...
> 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.

Sure, see the USING clause in DELETE.  Although your example seems a
bit confused, since you're not actually referring to _author_ip anywhere.
And if you meant "_author_ip" where you wrote "u._author_ip", that's in
a sub-SELECT, where you could just add a join to words_users without
needing any nonstandard DELETE syntax.

            regards, tom lane

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