Re: Select a column and then apply JSONB_ARRAY_ELEMENTS to it

[Alexander Farber]

On Sat, Jan 9, 2021 at 3:49 PM David G. Johnston <david.g.johns...@gmail.com>
wrote:

> Subqueries must be enclosed in parentheses.  The parentheses that are part
> of the function call do not count.
>
>
Ah! Thank you David, this has worked now -

CREATE OR REPLACE FUNCTION words_solve_puzzle(
                in_mid       bigint,
                in_uid       int,
                in_guess     text,
                OUT out_json jsonb
        ) RETURNS jsonb AS
$func$
DECLARE
        _tile        jsonb;
        _letter      char;
        _value       integer;
BEGIN
        in_guess := UPPER(in_guess);
        FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS((SELECT tiles FROM
words_moves WHERE mid = in_mid))
        LOOP
                _letter :=  _tile->>'letter';
                _value  := (_tile->>'value')::int;
                -- verify that all played tiles except wildcard are found
in the suggested answer
                IF _value > 0 AND POSITION(_letter IN in_guess) = 0 THEN
                    out_json := json_build_object(
                            'label',    '&#x1f44e; Keep guessing!'
                    );
                    RETURN;
                END IF;
        END LOOP;

        -- check if the in_guess is one of the played words in that move
        IF NOT EXISTS(SELECT 1 FROM words_scores WHERE mid = in_mid AND
word = in_guess) THEN
                out_json := json_build_object(
                        'label',    '&#x1f44e; Wrong!'
                );
                RETURN;
        END IF;

        -- the solution already submitted, just ack, but do not award coins
        IF EXISTS (SELECT 1 FROM words_puzzles WHERE mid = in_mid AND uid =
in_uid) THEN
                out_json := json_build_object(
                        'label',    '&#x1f44d; Correct!',
                        'url',      '/ws/puzzle2?mid=' || in_mid ||
'&secret=' || MD5(in_mid || 'my secret')
                );
                RETURN;
        END IF;

        -- save the puzzle solution and award coins to the user
        INSERT INTO words_puzzles (mid, uid, solved)
        VALUES (in_mid, in_uid, CURRENT_TIMESTAMP);

        UPDATE words_users
        SET coins = coins + 1
        WHERE uid = in_uid;

        out_json := json_build_object(
                'label',    '&#x1f44d; Correct, +1 coin!',
                'url',      '/ws/puzzle2?mid=' || in_mid || '&secret=' ||
MD5(in_mid || 'my secret')
        );
END
$func$ LANGUAGE plpgsql;

P.S. 'my secret' is not my real secret passphrase :-)