From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
---|---|
To: | |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Select a column and then apply JSONB_ARRAY_ELEMENTS to it |
Date: | 2021-01-09 16:49:29 |
Message-ID: | CAADeyWjdVAt60-kF_WA4nX7KR+hGdGE5fxN=79niWfqbHOfjsw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sat, Jan 9, 2021 at 3:49 PM David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)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', '👎 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', '👎 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', '👍 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', '👍 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 :-)
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2021-01-09 17:17:59 | Re: Static memory, shared memory |
Previous Message | Jack Orenstein | 2021-01-09 15:50:24 | Static memory, shared memory |