Search Postgresql Archives

Re: Select a column and then apply JSONB_ARRAY_ELEMENTS to it

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Sat, Jan 9, 2021 at 3:49 PM David G. Johnston <david.g.johnston@xxxxxxxxx> 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 :-)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux