My First Stored Procedure

Hi Guys,

I'm trying to write my first stored procedure. The database keeps track of calendar entries for properties. I'm not a DB expert, so if you notice any flaws or improvements that could be made along the way, please do chip in.

There are 3 types of calendar entries for a property: booked, available, and unavailable. Each type of entry has it's own table, which is a child of the "calendar_entries" table. I'm writing a stored procedure to handle inserts of available and unavailable periods. The idea is that if a period of either of these types gets inserted adjacent to a period of the same type, the two are merged. If it gets inserted in between two periods of the same type, all three periods are merged. Bookings will be handled by a separate procedure since we obviously don't want to merge bookings. An overlapping booking is a show stopper - They shouldn't ever be deleted or resized.

I've included what I've got up to now. Please note that I've sketched it out in pseudo code, and am only 10% through converting it in to actual plpgsql. The problem I've hit is: How do I access specific rows returned from a query? I was leaning towards cursors, but would ROW_COUNT still work for opening cursors? At base level, I guess what I'm asking here is how to I write 'ROW[1]' - as the query that detects overlapping periods should never return more than 2 rows.

As a side question, this implementation requires that no 'calendar_entries' for the same property overlap each other - ever. Is there any easy way of implementing this as a constraint? I've racked my brains but couldn't think of a way.

Thanks in advance for any help you are able to give me!

Yours Sincerely,

Neil Saunders

----------------

CREATE FUNCTION insert_period(PERIOD_START DATE, PERIOD_END DATE, table TEXT) RETURNS INTEGER AS $$

RAISE NOTICE 'Adding a period to %', table;

BEGIN

LOCK ALL CALENDAR TABLES FROM WRITING

SELECT INTO clashes id FROM bookings WHERE (start_date, end_date) OVERLAP (PERIOD_START,PERIOD_END) AND property_id = X LIMIT 1

IF NOT FOUND THEN

EXECUTE DELETE FROM calendar_entries WHERE property_id = X AND start_date >= PERIOD_START AND end_date <= PERIOD_END

SELECT * FROM calendar_entries WHERE (start_date, end_date) OVERLAP (PERIOD_START,PERIOD_END) AND property_id = X

GET DIAGNOSTICS num_entries = ROW_COUNT;

IF (num_entries = 1)

IF ROW.end_date >= PERIOD_START

IF ROW.table = table
UPDATE ROW.end_date = PERIOD_END
COMMIT
RETURN ROW.id
ELSE
UPDATE ROW.end_date = PERIOD_START -1
INSERT PERIOD
COMMIT
RETURN NEWROWID
END IF

ELSE IF ROW.start_date <= PERIOD_END

IF ROW.table = table
UPDATE ROW.start_date = PERIOD_START
COMMIT
RETURN ROW.id
ELSE
UPDATE ROW.start_date = PERIOD_END + 1
INSERT PERIOD
COMMIT
RETURN NEWROWID
END IF
END IF

ELSEIF (ROWS = 2)

IF (ROW[1].table = table) AND (ROW[2].table = table) THEN
UPDATE ROW1.end_date = ROW2.end_date
DELETE ROW2
COMMIT
RETURN ROW1.id

ELSE IF ROW[1].table = table
UPDATE ROW1.end_date = PERIOD_END
UPDATE ROW2.start_date = PERIOD_END + 1
COMMIT
RETURN ROW1.id
ELSE IF ROW[2].table = table
UPDATE ROW2.start_date = START_DATE
UPDATE ROW1.end_date = PERIOD_START - 1
COMMIT
RETURN ROW2.id
ELSE
UPDATE ROW1.end_date = PERIOD_START - 1
UPDATE ROW2.start_date = PERIOD_END + 1
INSERT NEW_PERIOD
COMMIT
RETURN NEW ROWID
END IF
END IF

ELSE

RAISE EXCEPTION 'Operation aborted: Clashing booking detected';

END IF

END;

$$ LANGUAGE plpgsql;

This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited.