CODEWITHSUNDEEP
postgresqlplpgsql
Marcos Javier Alvarez Maestre
Marcos Javier Alvarez Maestre

Reputation: 3

Invalid input syntax using jsonb_to_recordset inside a function

I'm trying to create a function that iterates a jsonb array but I'm facing a weird error message that totally blocked me. This is my test case.

CREATE OR REPLACE
    FUNCTION public.questiontemplate_match_customattributes(
        id int,
        customattributes jsonb
) 
RETURNS boolean LANGUAGE plpgsql AS 
$function$
DECLARE
    group_attrs JSONB;
    attr JSONB;
    attr_name TEXT;
BEGIN
    RAISE NOTICE 'Test 1';
    FOR attr IN (select * from jsonb_to_recordset(customattributes) AS x("name" TEXT, "value" TEXT)) LOOP
        RAISE NOTICE 'Test 2';
    END LOOP;
    RETURN FALSE;
END $function$;


SELECT
    questiontemplate_match_customattributes(
        12,
        jsonb_build_array(
            jsonb_build_object('name', 'pack', 'value', 'box'),
            jsonb_build_object('name', 'brand', 'value', 'pepsi')
        )
    )

When I run the queries above I get the following message

SQL Error [22P02]: ERROR: invalid input syntax for type json Detail: Token "pack" is invalid. Where: JSON data, line 1: pack PL/pgSQL function questiontemplate_match_customattributes(integer,jsonb) line 8 at FOR over SELECT rows

and only Test 1 appear in my output but when I run

SELECT * FROM jsonb_to_recordset(
    jsonb_build_array(
            jsonb_build_object('name', 'pack', 'value', 'box'),
            jsonb_build_object('name', 'brand', 'value', 'pepsi')
    )
) AS x("name" TEXT, "value" TEXT)

I get the right recordset. Any advice?

Version: Postgres 12.5

Enviroment: Docker

Upvotes: 0

Views: 641

Answers (1)

Stefanov.sm
Stefanov.sm

Reputation: 13049

Either change the type of variable attr from JSONB to record or - if you need it as JSONB - change the loop query to produce JSONB as below.

FOR attr IN select to_jsonb(x) from jsonb_to_recordset(customattributes) AS x("name" TEXT, "value" TEXT) LOOP
   RAISE NOTICE '%', attr;
END LOOP;

BTW the loop query may be simplified as

FOR attr IN select jsonb_array_elements(customattributes) LOOP
   RAISE NOTICE '%', attr;
END LOOP;

Upvotes: 0

Related Questions