Skip site navigation (1) Skip section navigation (2)

My First Stored Procedure

From: <neil(dot)saunders(at)accenture(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: My First Stored Procedure
Date: 2005-08-18 15:31:10
Message-ID: B74B7433CDDD2C4AA41D94832E32E19D92EE8B@EMEXM1112.dir.svc.accenture.com (view raw or flat)
Thread:
Lists: 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 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.

Responses

pgsql-novice by date

Next:From: Stuart BishopDate: 2005-08-19 06:40:43
Subject: Re: how to rename an unnamed uniqueness constraint?
Previous:From: Oliver KullmannDate: 2005-08-17 16:40:28
Subject: Re: how to rename an unnamed uniqueness constraint?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group