[PostgreSQL] integer 배열을 가져오는 서브쿼리로 조건 검색하기

PostgreSQL에서 integer 배열의 값을 서브쿼리로 가져와서 조건 검색하는 방법.



다음과 같은 테이블이 있다고 하자.
음식메뉴 테이블은 음식의 리스트만 가지고 있고, 주문테이블은 각 손님이 주문한 메뉴리스트를 가지고 있다.
주문은 order라는 필드에 menu_list에 있는 메뉴의 idx값을 배열로 저장하고 있는 구조다.


테이블 1. 음식 메뉴

CREATE TABLE menu_list (idx serial, menu_name text, cost integer);

테이블 2. 손님이 주문한 음식들

CREATE TABLE order_list (idx serial, order integer[]);

음식메뉴는 다음과 같이 구성.

idx   |   menu_name   |   cost
---------------------------------
1      |   설렁탕           |   6000
2      |   계란말이        |   5000
3      |   공기밥           |   1000
4      |   음료수           |   1000
5      |   소주              |   3000

어떤 손님이 설렁탕, 계란말이, 소주를 주문했다면 주문 테이블에는 다음과 같은 Row가 저장된다.

idx   |   order_menu
-----------------------
1      |   {1, 2, 5}

이걸 서브쿼리를 이용해서 한방에 가져오는 방법은 다음과 같다.
서브쿼리의 결과를 integer[]로 type cast 해야 한다.
웃기는 건 WHERE idx IN 은 안먹힌다.
PostgreSQL 8.4 에는 배열을 ROW로 반환하는 unnset 라는 함수가 있던데 그게 더 깔끔할라나...

-- 주문된 메뉴 리스트 (수량 같은 자세한 사항은 제외했음...)
SELECT menu_name FROM menu_list
    WHERE idx = ANY ((SELECT order_menu FROM order_list WHERE idx=1)::integer[]);

-- 주문된 메뉴의 가격 합계
SELECT SUM(cost) FROM menu_list
    WHERE idx = ANY ((SELECT order_menu FROM order_list WHERE idx=1)::integer[]);