| From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> | 
|---|---|
| To: | pgsql-general <pgsql-general(at)postgresql(dot)org> | 
| Subject: | Select a column and then apply JSONB_ARRAY_ELEMENTS to it | 
| Date: | 2021-01-09 13:31:57 | 
| Message-ID: | CAADeyWi4TaxUfzKYLmMXAiqY5db-ZUzec=r8f64Nn_m4FN4YHw@mail.gmail.com | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-general | 
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Markhof, Ingolf | 2021-01-09 14:22:25 | RE: How to keep format of views source code as entered? | 
| Previous Message | Thiemo Kellner | 2021-01-09 11:38:23 | Re: Suggestion: provide a "TRUNCATE PARTITION" command |