On 03/02/2018 06:14 AM, Alexander Farber wrote:
Hi Adrian, thank you for the reply -
# select mid, jsonb_array_length(tiles) from words_moves where
gid=609;
ERROR: 22023: cannot get array length of a scalar
LOCATION: jsonb_array_length, jsonfuncs.c:1579
What am I doing wrong here please?
Are you sure all the values in tiles are correctly formatted because
when I use jsonb_array_length with the provided data:
test=# select jsonb_array_length( '[{"col": 3, "row": 7, "value": 2,
"letter": "С"}, {"col": 4, "row": 7, "value": 1, "letter": "О"},
{"col": 5, "row": 7, "value": 2, "letter": "П"}, {"col": 6, "row":
7, "value": 0, "letter": "Л"}, {"col": 7, "row": 7, "value": 3,
"letter": "Я"}]');
jsonb_array_length
--------------------
5
I fill that table with the following stored function (please pardon the
huge listing):
The little gray cells are not awake enough to work through the below:)
If it where me I would first confirm there was malformed data by looking
at the data itself. If there are not that many records for gid = 609
maybe a simple select of tiles would be sufficient. Otherwise maybe a
simple plpgsql function that loops through the records applying
jsonb_array_length and raising a notice on the error. In any case the
point is to identify the presence of malformed data and if present the
nature of the malformation. That would help reverse engineer any issues
with below.
CREATE OR REPLACE FUNCTION words_play_game(
in_uid integer,
in_gid integer,
in_tiles jsonb
) RETURNS table (
out_uid integer, -- the player to be notified
out_fcm text,
out_apns text,
out_adm text,
out_body text
) AS
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
