Select a column and then apply JSONB_ARRAY_ELEMENTS to it
Hello,
for a word puzzle using PostgreSQL 13.1:
https://wortefarbers.de/ws/puzzle2?mid=138&secret=c6f469786df7e8d44461381b62b2ce7d
I am trying to improve a stored function -
CREATE OR REPLACE FUNCTION words_solve_puzzle(
in_mid bigint,
in_uid int,
in_answer text,
OUT out_json jsonb
) RETURNS jsonb AS
$func$
DECLARE
_tile jsonb;
_letter char;
_value integer;
_answer text;
BEGIN
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;
RAISE NOTICE 'Tile % letter % value', _tile, _letter,
_value;
END LOOP;
However this results in the error message -
ERROR: 42601: syntax error at or near "SELECT"
LINE 24: ... FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS(SELECT til...
^
LOCATION: scanner_yyerror, scan.l:1180
Could you please help me, how to combine SELECT query and the LOOP
through JSONB_ARRAY_ELEMENTS here?
Thank you
Alex
P.S: Here the table:
words_de=> \d words_moves
Table "public.words_moves"
Column | Type | Collation | Nullable |
Default
---------+--------------------------+-----------+----------+------------------------------------------
mid | bigint | | not null |
nextval('words_moves_mid_seq'::regclass)
action | text | | not null |
gid | integer | | not null |
uid | integer | | not null |
played | timestamp with time zone | | not null |
tiles | jsonb | | |
score | integer | | |
str | text | | |
hand | text | | |
letters | character(1)[] | | |
values | integer[] | | |
Indexes:
"words_moves_pkey" PRIMARY KEY, btree (mid)
"words_moves_gid_played_idx" btree (gid, played DESC)
"words_moves_uid_action_played_idx" btree (uid, action, played)
"words_moves_uid_idx" btree (uid)
Check constraints:
"words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
"words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON
DELETE CASCADE
"words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON
DELETE CASCADE
Referenced by:
TABLE "words_puzzles" CONSTRAINT "words_puzzles_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE
On Saturday, January 9, 2021, Alexander Farber <alexander.farber@gmail.com>
wrote:
BEGIN
FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS(SELECT tiles FROM
words_moves WHERE mid = in_mid)
Subqueries must be enclosed in parentheses. The parentheses that are part
of the function call do not count.
David J.
On Sat, Jan 9, 2021 at 3:49 PM David G. Johnston <david.g.johnston@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', '👎 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 :-)