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;
