sql - 用于检查数组中的枚举值的 PostgreSQL 函数

Question

类型:

CREATE TYPE equipment AS ENUM ('projector','PAsystem','safe','PC','phone');
CREATE TYPE building_code AS ENUM ('IT','EMS','HSB','ENG');

表格:

CREATE TABLE venue (
   id INTEGER DEFAULT NEXTVAL('venue_id_seq')
 , building_code building_code
 , floorNo int
 , roomNo int
 , width int
 , length int
 );

CREATE TABLE lecture_room (
   id INTEGER DEFAULT NEXTVAL('lecture_id_seq')
 , seatCount int
 , equipment equipment[]
) INHERITS(venue);

功能:

CREATE or REPLACE FUNCTION hasProjector(_id int ) RETURNS boolean AS 
$$
code to check if there exists a projector in the equipment array of lecture_room
$$ LANGUAGE SQL;

我不是 100% 确定要放入函数的 SQL 代码以及如何获得 bool 值。

Answer 1

使用ANY检查数组是否包含某个元素:

SELECT TRUE
FROM   lecture_room
WHERE  id = _id
AND    'projector' = ANY (equipment)

返回 TRUE 或 NULL。如果您需要真/假，请使用 EXISTS :

SELECT EXISTS (
   SELECT 1
   FROM   lecture_room
   WHERE  id = _id
   AND    'projector' = ANY (equipment)
   )

顺便说一句，在这种情况下，您不需要显式转换('projector'::equipment)，但它也没有坏处。