Search Postgresql Archives

Re: DELETE and JOIN

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux