Re: DELETE and JOIN

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(dot)klaver(at)aklaver(dot)com