submitting jsonb via an SQL function

Hello. I would like to have a query that inserts data in mulitple tables at once. My understanding is that the best way to do that is via the "functions" tool in Supabase.

Some context: the user is creating a deck of flashcards. The deck is associated with a lesson, which is associated with a subject. The idea is that the user specifies the subject name, lesson name, and creates a list of cards (or tells OpenAI to do it for them) in the interface before clicking save.

Here is the function on the client side:

const { data, error } = await supabase.rpc('create_subject_lesson_cards', {
  _creator_id: user.id,
  _subject_name: language,
  _current_level: level,
  _lesson_title: lessonTitle,
  _cards: JSON.stringify(cards)
        });

const { data, error } = await supabase.rpc('create_subject_lesson_cards', {
  _creator_id: user.id,
  _subject_name: language,
  _current_level: level,
  _lesson_title: lessonTitle,
  _cards: JSON.stringify(cards)
        });

Here is the function in Supabase:

DECLARE
    _subject_id UUID;
    _lesson_id UUID;
BEGIN
    -- Insert subject
    INSERT INTO subjects (creator_id, name, current_level)
    VALUES (_creator_id, _subject_name, _current_level)
    RETURNING id INTO _subject_id;

    -- Insert lesson
    INSERT INTO lessons (creator_id, title, subject_id)
    VALUES (_creator_id, _lesson_title, _subject_id)
    RETURNING id INTO _lesson_id;

    -- Insert cards
    INSERT INTO cards (lesson_id, creator_id, side1, side2)
    SELECT _lesson_id, _creator_id, card->>'side1', card->>'side2'
    FROM jsonb_array_elements(_cards::jsonb) AS card;

EXCEPTION WHEN OTHERS THEN
    -- If there is any error, rollback the transaction
    RAISE;
END;

DECLARE
    _subject_id UUID;
    _lesson_id UUID;
BEGIN
    -- Insert subject
    INSERT INTO subjects (creator_id, name, current_level)
    VALUES (_creator_id, _subject_name, _current_level)
    RETURNING id INTO _subject_id;

    -- Insert lesson
    INSERT INTO lessons (creator_id, title, subject_id)
    VALUES (_creator_id, _lesson_title, _subject_id)
    RETURNING id INTO _lesson_id;

    -- Insert cards
    INSERT INTO cards (lesson_id, creator_id, side1, side2)
    SELECT _lesson_id, _creator_id, card->>'side1', card->>'side2'
    FROM jsonb_array_elements(_cards::jsonb) AS card;

EXCEPTION WHEN OTHERS THEN
    -- If there is any error, rollback the transaction
    RAISE;
END;

I have specified each of the arguments in the Supabase function creator, with the

_cards

_cards

argument set to

jsonb

jsonb

Here is the format of the parameter being submitted

[{"side1":"hello","side2":"hallo"},{"side1":"goodbye","side2":"auf wiedersehen"}]

[{"side1":"hello","side2":"hallo"},{"side1":"goodbye","side2":"auf wiedersehen"}]

The error:

[2582] authenticator@postgres ERROR:  cannot extract elements from a scalar

[2582] authenticator@postgres ERROR:  cannot extract elements from a scalar

Why is the function reading the jsonb as a scalar?

submitting jsonb via an SQL function

const { data, error } = await supabase.rpc('create_subject_lesson_cards', {
  _creator_id: user.id,
  _subject_name: language,
  _current_level: level,
  _lesson_title: lessonTitle,
  _cards: JSON.stringify(cards)
        });

const { data, error } = await supabase.rpc('create_subject_lesson_cards', {
  _creator_id: user.id,
  _subject_name: language,
  _current_level: level,
  _lesson_title: lessonTitle,
  _cards: JSON.stringify(cards)
        });

Here is the function in Supabase:

DECLARE
    _subject_id UUID;
    _lesson_id UUID;
BEGIN
    -- Insert subject
    INSERT INTO subjects (creator_id, name, current_level)
    VALUES (_creator_id, _subject_name, _current_level)
    RETURNING id INTO _subject_id;

    -- Insert lesson
    INSERT INTO lessons (creator_id, title, subject_id)
    VALUES (_creator_id, _lesson_title, _subject_id)
    RETURNING id INTO _lesson_id;

    -- Insert cards
    INSERT INTO cards (lesson_id, creator_id, side1, side2)
    SELECT _lesson_id, _creator_id, card->>'side1', card->>'side2'
    FROM jsonb_array_elements(_cards::jsonb) AS card;

EXCEPTION WHEN OTHERS THEN
    -- If there is any error, rollback the transaction
    RAISE;
END;

DECLARE
    _subject_id UUID;
    _lesson_id UUID;
BEGIN
    -- Insert subject
    INSERT INTO subjects (creator_id, name, current_level)
    VALUES (_creator_id, _subject_name, _current_level)
    RETURNING id INTO _subject_id;

    -- Insert lesson
    INSERT INTO lessons (creator_id, title, subject_id)
    VALUES (_creator_id, _lesson_title, _subject_id)
    RETURNING id INTO _lesson_id;

    -- Insert cards
    INSERT INTO cards (lesson_id, creator_id, side1, side2)
    SELECT _lesson_id, _creator_id, card->>'side1', card->>'side2'
    FROM jsonb_array_elements(_cards::jsonb) AS card;

EXCEPTION WHEN OTHERS THEN
    -- If there is any error, rollback the transaction
    RAISE;
END;

I have specified each of the arguments in the Supabase function creator, with the

_cards

_cards

argument set to

jsonb

jsonb

Here is the format of the parameter being submitted

[{"side1":"hello","side2":"hallo"},{"side1":"goodbye","side2":"auf wiedersehen"}]

[{"side1":"hello","side2":"hallo"},{"side1":"goodbye","side2":"auf wiedersehen"}]

The error:

[2582] authenticator@postgres ERROR:  cannot extract elements from a scalar

[2582] authenticator@postgres ERROR:  cannot extract elements from a scalar

Why is the function reading the jsonb as a scalar?

submitting jsonb via an SQL function

submitting jsonb via an SQL function

Similar Threads

Similar Threads

Similar Threads