My First Stored Procedure - Mailing list pgsql-novice

Hi Guys,

I'm trying to write my first stored procedure. The database keeps track of calendar entries for properties. I'm not a
DBexpert, 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
owntable, which is a child of the "calendar_entries" table. I'm writing a stored procedure to handle inserts of
availableand unavailable periods. The idea is that if a period of either of these types gets inserted adjacent to a
periodof the same type, the two are merged. If it gets inserted in between two periods of the same type, all three
periodsare merged. Bookings will be handled by a separate procedure since we obviously don't want to merge bookings. An
overlappingbooking 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
convertingit in to actual plpgsql. The problem I've hit is: How do I access specific rows returned from a query? I was
leaningtowards cursors, but would ROW_COUNT still work for opening cursors? At base level, I guess what I'm asking here
ishow 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 =
XLIMIT 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
useof the email by you is prohibited.