Iterating over jsonb array: operator does not exist: name ->> unknown

889 Views Asked by At

In a game for Android users can login via Google+, Facebook, Twitter:

app screenshot

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.

0

There are 0 best solutions below