Combining INSERT with DELETE RETURNING

[Alexander Farber <alexander.farber@xxxxxxxxx>]

Good afternoon,

the doc https://www.postgresql.org/docs/9.6/static/sql-delete.html states:

"The syntax of the RETURNING list is identical to that of the output list of SELECT."

So I am trying to rewrite the INSERT .. ON CONFLICT DO NOTHING followed by a DELETE:

                INSERT INTO words_reviews (
                        uid,
                        author,
                        nice,
                        review,
                        updated
                ) SELECT
                        uid,
                        out_uid,        -- change to out_uid
                        nice,
                        review,
                        updated
                FROM words_reviews
                WHERE author <> out_uid
                AND author = ANY(_uids)
                ON CONFLICT DO NOTHING;

                DELETE FROM words_reviews
                WHERE author <> out_uid
                AND author = ANY(_uids);

into a single statement:

                INSERT INTO words_reviews (
                        uid,
                        author,
                        nice,
                        review,
                        updated
                ) VALUES (
                DELETE FROM words_reviews
                WHERE author <> out_uid
                AND author = ANY(_uids)
                RETURNING
                        uid,
                        out_uid,        -- change to out_uid
                        nice,
                        review,
                        updated
                )
                ON CONFLICT DO NOTHING;

but get the syntax error:

words=> \i words_merge_users.sql
psql:words_merge_users.sql:218: ERROR:  syntax error at or near "FROM"
LINE 131:                 DELETE FROM words_reviews
                                 ^

What am I doing wrong this time please?

Thank you
Alex

P.S. Below is my custom function in its entirety + table descriptions:

CREATE TABLE words_users (
        uid SERIAL PRIMARY KEY,

        created timestamptz NOT NULL,
        visited timestamptz NOT NULL,
        ip inet NOT NULL,

        fcm     varchar(255),
        apns    varchar(255),
        motto   varchar(255),

        vip_until   timestamptz,
        grand_until timestamptz,

        banned_until timestamptz,
        banned_reason varchar(255) CHECK (LENGTH(banned_reason) > 0),

        elo    integer NOT NULL CHECK (elo >= 0),
        medals integer NOT NULL CHECK (medals >= 0),
        coins  integer NOT NULL
);

CREATE TABLE words_social (
        sid varchar(255) NOT NULL,

        social integer NOT NULL CHECK (0 <= social AND social <= 6),
        female integer NOT NULL CHECK (female = 0 OR female = 1),
        given  varchar(255) NOT NULL CHECK (given ~ '\S'),
        family varchar(255),
        photo  varchar(255) CHECK (photo ~* '^https?://...'),
        place  varchar(255),
        stamp  integer NOT NULL,

        uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
        PRIMARY KEY(sid, social)
);

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)
);

CREATE OR REPLACE FUNCTION words_merge_users(
                in_users jsonb,
                in_ip inet,
                OUT out_uid integer,
                OUT out_vip timestamptz,
                OUT out_grand timestamptz,
                OUT out_banned timestamptz,
                OUT out_reason varchar
        ) RETURNS RECORD AS
$func$
DECLARE
        _user          jsonb;
        _uids          integer[];
        -- the variables below are used to temporary save new user stats
        _created       timestamptz;
        _elo           integer;
        _medals        integer;
        _coins         integer;
BEGIN
        -- in_users must be a JSON array with at least 1 element
        IF in_users IS NULL OR JSONB_ARRAY_LENGTH(in_users) = 0 THEN
                RAISE EXCEPTION 'Invalid users = %', in_users;
        END IF;

        _uids := (
                SELECT ARRAY_AGG(DISTINCT uid)
                FROM words_social
                JOIN JSONB_ARRAY_ELEMENTS(in_users) x
                        ON sid = x->>'sid'
                        AND social = (x->>'social')::int
        );

        IF _uids IS NULL THEN
                -- no users found -> create a new user
                INSERT INTO words_users (
                        created,
                        visited,
                        ip,
                        elo,
                        medals,
                        coins
                ) VALUES (
                        CURRENT_TIMESTAMP,
                        CURRENT_TIMESTAMP,
                        in_ip,
                        1500,
                        0,
                        0
                ) RETURNING uid INTO STRICT out_uid;

        ELSIF CARDINALITY(_uids) = 1 THEN
                -- just 1 user found -> update timestamp and IP address
                SELECT
                        uid,
                        vip_until,
                        grand_until,
                        banned_until,
                        banned_reason
                INTO STRICT
                        out_uid,
                        out_vip,
                        out_grand,
                        out_banned,
                        out_reason
                FROM words_users
                WHERE uid = _uids[1];

                UPDATE words_users SET
                        visited = CURRENT_TIMESTAMP,
                        ip      = in_ip
                WHERE uid = out_uid;
        ELSE      
                -- few users found -> merge their records to a single one
                SELECT
                        MIN(uid),
                        MIN(created),
                        CURRENT_TIMESTAMP + SUM(vip_until - CURRENT_TIMESTAMP),
                        CURRENT_TIMESTAMP + SUM(grand_until - CURRENT_TIMESTAMP),
                        MAX(banned_until),
                        AVG(elo),
                        SUM(medals),
                        SUM(coins)
                INTO STRICT
                        out_uid,
                        _created,
                        out_vip,
                        out_grand,
                        out_banned,
                        _elo,
                        _medals,
                        _coins
                FROM words_users
                WHERE uid = ANY(_uids);

                SELECT banned_reason
                INTO out_reason
                FROM words_users
                WHERE banned_until = out_banned
                LIMIT 1;

                -- try to copy as many reviews OF this user as possible
                INSERT INTO words_reviews (
                        uid,
                        author,
                        nice,
                        review,
                        updated
                ) SELECT
                        out_uid,        -- change to out_uid
                        author,
                        nice,
                        review,
                        updated
                FROM words_reviews
                WHERE uid <> out_uid
                AND uid = ANY(_uids)
                ON CONFLICT DO NOTHING;

                DELETE FROM words_reviews
                WHERE uid <> out_uid
                AND uid = ANY(_uids);

                -- try to copy as many reviews BY this user as possible
                INSERT INTO words_reviews (
                        uid,
                        author,
                        nice,
                        review,
                        updated
                ) SELECT
                        uid,
                        out_uid,        -- change to out_uid
                        nice,
                        review,
                        updated
                FROM words_reviews
                WHERE author <> out_uid
                AND author = ANY(_uids)
                ON CONFLICT DO NOTHING;

                DELETE FROM words_reviews
                WHERE author <> out_uid
                AND author = ANY(_uids);

                UPDATE words_social
                SET uid = out_uid
                WHERE uid = ANY(_uids);

                DELETE FROM words_users
                WHERE uid <> out_uid
                AND uid = ANY(_uids);

                UPDATE words_users SET
                        visited       = CURRENT_TIMESTAMP,
                        ip            = in_ip,
                        created       = _created,
                        vip_until     = out_vip,
                        grand_until   = out_grand,
                        banned_until  = out_banned,
                        banned_reason = out_reason,
                        elo           = _elo,
                        medals        = _medals,
                        coins         = _coins
                WHERE uid = out_uid;

                -- TODO merge playing stats here
        END IF;

        FOR _user IN SELECT * FROM JSONB_ARRAY_ELEMENTS(in_users)
        LOOP
                IF NOT words_valid_user((_user->>'social')::int,
                                         _user->>'sid',
                                         _user->>'auth') THEN
                        RAISE EXCEPTION 'Invalid user = %', _user;
                END IF;

                UPDATE words_social SET
                        social = (_user->>'social')::int,
                        female = (_user->>'female')::int,
                        given  =  _user->>'given',
                        family =  _user->>'family',
                        photo  =  _user->>'photo',
                        place  =  _user->>'place',
                        stamp  = (_user->>'stamp')::int,
                        uid    = out_uid                                            
                WHERE sid = _user->>'sid'
                AND social = (_user->>'social')::int;

                IF NOT FOUND THEN
                        INSERT INTO words_social (
                                sid,
                                social,
                                female,
                                given,
                                family,
                                photo,
                                place,
                                stamp,
                                uid
                        ) VALUES (
                                 _user->>'sid',
                                (_user->>'social')::int,
                                (_user->>'female')::int,
                                 _user->>'given',
                                 _user->>'family',
                                 _user->>'photo',
                                 _user->>'place',
                                (_user->>'stamp')::int,
                                out_uid
                        );
                END IF;
        END LOOP;
END
$func$ LANGUAGE plpgsql;