PostgreSQL function to check for an enum value in an array

Question

Types:

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

Tables:

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);

Function:

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;

I am not 100% sure on the SQL code to put in the function and how to get a boolean as a result.

Erwin Brandstetter · Accepted Answer · 2013-08-14 21:26:07Z

3

Use ANY to check whether the array contains a certain element:

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

Returns TRUE or NULL. If you need TRUE / FALSE use EXISTS:

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

BTW, in this case, you don't need an explicit cast ('projector'::equipment), but it wouldn't hurt either.

edited Aug 14, 2013 at 21:26

answered Aug 14, 2013 at 21:21

Erwin Brandstetter

668k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL function to check for an enum value in an array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related