2024.06.20 SQL문으로 기존 배열에 데이터 갈아끼우지 않고 추가하기

🚨  TroubleShotting

문제 발생의 배경 - As Is

Supabase에 quiz_like라는 테이블에 users라는 column을 user_id의 배열(text[])로 타입을 정해두었다.

이 users라는 column을 한 번의 통신으로 update하고 싶었으나,
기존 supabase의 update 메서드는 기존 걸 갈아바꾸는 메서드라서 
기존의 것을 select 한 뒤 update 하는 총 두 번의 네트워크 통신이 발생했다.

내가 원한 것은 한번의 통신으로 기존 것에 추가되도록 하고 싶은 와중에 구글에 찾아보니 이런 글이 있었다.
https://github.com/orgs/supabase/discussions/1570

Is there any way to add values to an array column with UPDATE? · supabase · Discussion #1570

 

---

How(과정) ?

시행착오 1

그래서 SQL을 이렇게 작성해 보았다.

create function append_user_id_to_quiz_like (quiz_id uuid, user_id uuid) returns quiz_like as $$

begin

  update quiz_like

  set users = case

                when array_position(users, user_id) is null then array_append(coalesce(users, '{}'), user_id)

                else users

              end

  where quiz_id = append_user_id_to_quiz_like.quiz_id

  returning *;

end;

$$ language plpgsql;

그런데 
{code: "42702", details: "It could refer to either a PL/pgSQL variable or a table column.", hint: null,
message: "column reference \"quiz_id\" is ambiguous" }

라는 오류가 발생했다.
함수의 매개변수와 vs 테이블의 칼럼 이름으로 동시에 사용되어 ambiguous 하다는 것이다.

---

시행착오 2

그래서 이렇게 바꿨다.

create function append_user_id_to_quiz_like (quiz_id uuid, user_id uuid) returns quiz_like as $$

begin

  update quiz_like

  set users = case

                when array_position(users, user_id) is null then array_append(coalesce(users, '{}'), user_id)

                else users

              end

  where quiz_like.quiz_id = append_user_id_to_quiz_like.quiz_id

  returning *;

end;

$$ language plpgsql;

모호하지 않게 quiz_like 테이블의 quiz_id vs 함수에 들어올 quiz_id
로 바꿨다.

그런데 그랬더니 이번에는 

{code: "42601", details: null, hint: null, message: "query has no destination for result data"}

라는 오류가 발생했다.
쿼리 결과를 반환하지만, 결과를 어디로 반환할지 명시하지 않았다는 뜻이다.

---

what(결과) - To Be

그래서 어떻게 해야하지.. 하다가 SQL문을 잘 모르는 난 chatGPT에 물어봤다.
chatGPT가 내 준 결과는 이러했다.

create function append_user_id_to_quiz_like (quiz_id uuid, user_id uuid) returns setof quiz_like as $$

begin

  return query

    update quiz_like

    set users = case

                  when array_position(users, user_id) is null then array_append(coalesce(users, '{}'), user_id)

                  else users

                end

    where quiz_like.quiz_id = append_user_id_to_quiz_like.quiz_id

    returning *;

end;

$$ language plpgsql;

이전 함수와 다른 점
1. 'returns quiz_like' -> 'returns setof quiz_like'
단일 행을 반환하려고 하지만 쿼리의 결과를 명시적으로 반환하지 않는 부분을 
quiz_like의 여러 행을 반환한다고 쿼리의 결과를 명시적으로 반환하도록 변경했다.

2. update quiz_like -> return query + update quiz_like
함수를 정의할 때 함수의 결과가 무엇일지를 명시해야 하는데
이전 SQL문은 정확히 어떤 결과를 return 하는지 함수를 설명하는 begin ... end에 작성하지 않았다.
정확히 query를 return 한다는 것을 명시해줬다.

이렇게 했더니 원하는 대로 기존 배열에 추가되도록 할 수 있었다.
만든 함수는 이렇게 사용한다.

  const submitQuizLike = async (quiz_id: string, user_id: string) => {
    const supabase = clientSupabase();
    await supabase.rpc('append_user_id_to_quiz_like', { quiz_id, user_id });
  };

---

💡 새롭게 알게된 점

단순히 GPT에 물어보고 끝나면 난 기술에 져버린 인간.. ㅋㅋ
한줄한줄 분석해서 배워야겠다는 생각에 정리했다.

-  ‘create function append_user_id_to_quiz_like (quiz_id uuid, user_id uuid):
append_user_id_to_quiz_like 라는 함수를 만드는데, 그 인자로는 quiz_id와 user_id 가 들어가고 각각의 type이 uuid다.

-  returns setof quiz_like:
쿼리의 결과로 quiz_like의 여러 행을 반환

-  as $$ ... $$ language plpgsql:
함수 본문은 $$ … $$ 사이에 둘러쌓여 있으며, 함수는 PL/SQL 언어로 작성된다.

-  ‘begin ... end;:
함수 본문의 시작과 끝을 뜻한다.

-  ‘return query:
함수의 결과로 쿼리를 반환

-  ‘update quiz_like:
quiz_like 테이블을 업데이트

-  set users = case ... end:
‘users’ 컬럼을 조건에 따라 업데이트 할 것이다.

-  array_position(users, user_id) is null:
users 컬럼에서 user_id가 있는지 확인
만약 없으면 (null 이면)

-  array_append(coalesce(users, '{}'), user_id)
+) coalesce(users, ‘{}’) : 인자들 중에 왼쪽부터 가면서 null이 아닌 것을 반환하는 함수
이번에는 users 배열이 null인지 확인
null이 아니면 users를 반환, null 이면 ‘{}’을 반환해서 결과적으로는 빈 배열을 반환 후,
Users가 null이 아니면 기존 users에 user_id를 추가하고 null이면 빈 배열에 user_id를 추가

-  else users:
근데 만약 users 컬럼에 user_id가 있으면 기존 users 배열을 반환

-  where quiz_like.quiz_id = append_user_id_to_quiz_like.quiz_id:
quiz_like 테이블의 quiz_id가 함수의 인자로 들어오는 quiz_id랑 일치하는 행을 업데이트 할 대상으로 하겠다.

-  returning *;:
업데이트된 행을 반환

---

비록.. GPT에 의존해서 알게된 지식이지만..
그래도 모르는 것 보다는 이렇게라도 아는 게 어딘가!:))