In a game for Android users can login via Google+, Facebook, Twitter:
When the app connects to the PostgreSQL/PHP backend it sends a list of the social ids and I store them as sid
column in the social
table:
create table social (
sid varchar(255) not null,
auth char(32) not null,
social integer not null check (0 <= social and social <= 6),
given varchar(255) not null check (given ~ '\S'),
photo varchar(255) null check (photo ~* '^https?://...'),
stamp timestamp not null,
ip inet not null,
uid integer not null references users on delete cascade,
primary key(sid, social)
);
In the other table called users
I keep autoincremented user ids as uid
column and use it to track their games, achievements, player stats:
create table users (
uid serial primary key,
created timestamp not null,
stamp timestamp not null,
banned_until timestamp null,
banned_reason varchar(255) null
);
In the PHP login script I try to merge social accounts whenever possible -
by taking all received sids, finding the corresponding uids and then taking the lowest found uid and updating the records in the social
table to use that lowest uid.
This already works well in PHP, but now I am trying to move the merging functionality into a PostgreSQL stored function:
CREATE OR REPLACE FUNCTION merge_users(
IN in_users jsonb,
IN in_ip inet,
OUT out_uid integer) AS
$func$
DECLARE
user jsonb;
BEGIN
SELECT MIN(uid) INTO out_uid FROM social
WHERE sid IN (SELECT u->>'sid' FROM JSONB_ARRAY_ELEMENTS(in_users) u);
IF FOUND THEN
UPDATE words_social SET uid=out_uid
WHERE sid IN (SELECT u->>'sid' FROM JSONB_ARRAY_ELEMENTS(in_users) u);
ELSE
INSERT INTO words_users (created, stamp)
VALUES (current_timestamp, current_timestamp)
RETURNING uid INTO out_uid;
END IF;
FOR user IN SELECT * FROM JSONB_ARRAY_ELEMENTS(in_users)
LOOP
RAISE NOTICE 'user sid = %', user->>'sid';
UPDATE social SET
auth = user->'auth',
social = user->'social',
given = user->'given',
photo = user->'photo',
stamp = TO_TIMESTAMP(user->'stamp'),
ip = in_ip,
uid = out_uid
WHERE sid = user->>'sid';
IF NOT FOUND THEN
INSERT INTO social (
sid,
auth,
social,
given,
photo,
stamp,
ip,
uid
) VALUES (
user->'sid',
user->'auth',
user->'social',
user->'given',
user->'photo',
TO_TIMESTAMP(user->'stamp'),
in_ip,
out_uid
);
END IF;
END LOOP;
END
$func$ LANGUAGE plpgsql;
Unfortunately, the above code prints the syntax error message:
# select * from merge_users(
'[{"sid":"12345284239407942","auth":"ddddc1808197a1161bc22dc307accccc","social":3,"given":"Alexander1","family":"Farber","photo":"https:\/\/graph.facebook.com\/10154284239407942\/picture?type=large","place":"Bochum, Germany","female":0,"stamp":1450102770},{"sid":"54321284239407942","auth":"ddddc1808197a1161bc22dc307abbbbb","social":4,"given":"Alexander2","family":"Farber","photo":null,"place":"Bochum, Germany","female":0,"stamp":1450102800}]'::jsonb);
NOTICE: min uid = <NULL>
ERROR: operator does not exist: name ->> unknown
LINE 1: SELECT user->>'sid'
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY: SELECT user->>'sid'
CONTEXT: PL/pgSQL function words_merge_users(jsonb) line 15 at RAISE
I have tried appending ::jsonb
to u
and user
in the above code, but it hasn't helped.