current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.