Thread: 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
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.
Hi all,
After some more work I've managed to answer my original question - And now have a fully fleshed out function that I
thinkshould do everything I want - Except I'm having trouble calling it.
I am calling using:
SELECT insert_period(1::INTEGER,'2005-09-13'::DATE,'2005-09-15'::DATE,'unavailable_periods');
But am getting the error message:
-----
ERROR: syntax error at or near "$1" at character 70
QUERY: SELECT * FROM bookings WHERE (start_date, end_date) OVERLAPS (DATE $1 - interval '1 day', DATE $2 + interval '1
day')AND property_id = $3 LIMIT 1
CONTEXT: PL/pgSQL function "insert_period" line 12 at select into variables
------
I've used EMS PostgreSQL Manager to write the function, and have successfully used the debugger to step through the
functionusing various calling arguments without issues - I only get this problem when trying to call the function
througha client.
Research on this revealed problems when variable names are named after existing postgres functions/tables/columns, but
Ito my knowledge there is nothing in the database named the same of my arguments. I've tried renaming them all to
randomnames, but to no avail. I've also tried declaring the variables as ALIAS FOR in the DECLARE section, but again no
luck.The other thing that concerns me is that the error shows $1 being used as a DATE argument, I would have thought
'prop_id'(See below) would have been $1?
I have included the function below - Anyone have any ideas?
Cheers,
Neil.
-----
CREATE OR REPLACE FUNCTION "public"."insert_period" (prop_id integer, new_start_date date, new_end_date date,
into_tablevarchar) RETURNS integer AS
$body$
DECLARE
cur_overlap refcursor;
new_id INTEGER;
num_entries INTEGER;
row_one record;
row_two record;
clashes record;
BEGIN
LOCK TABLE calendar_entries IN EXCLUSIVE MODE;
SELECT INTO clashes * FROM bookings WHERE (start_date, end_date) OVERLAPS (DATE new_start_date - interval '1
day',DATE new_end_date + interval '1 day') AND property_id = prop_id LIMIT 1;
IF NOT FOUND THEN
DELETE FROM calendar_entries WHERE property_id = prop_id AND (start_date >= new_start_date) AND (end_date
<=new_end_date);
OPEN cur_overlap FOR SELECT *, pg_class.relname AS table FROM calendar_entries WHERE (start_date, end_date)
OVERLAPS(new_start_date - interval '2 days', new_end_date + interval '2 days') AND property_id = prop_id AND
pg_class.oid= tableoid ORDER BY start_date;
GET DIAGNOSTICS num_entries = ROW_COUNT;
IF (num_entries = 1) THEN
/* We're overlapping one row. Either we're enveloped by a single row,
or we have one row overlapping either the start date or the end
date.
*/
FETCH cur_overlap INTO row_one;
IF (row_one.start_date <= new_start_date) AND (row_one.end_date >= new_end_date) THEN
/* We're enveloped. The enveloping row needs to be split in to
two so that we can insert ourselves. */
IF row_one.table = into_table THEN
/* This period is already marked appropriately. Do nothing. */
ELSE
/* We need to perform a split/insert.
1. Adjust the end date of the enveloping row to the new
start - 1 day.
2. Insert a new row as the same type as the enveloping row
from new_end_date + 1 to the existing end date.
3. Insert the new row in to the required table */
EXECUTE 'UPDATE ' || row_one.table || ' SET end_date = DATE ''' || new_start_date || ''' - interval
''1day'' WHERE id = ' || row_one.id;
EXECUTE 'INSERT INTO ' || row_one.table || ' (start_date, end_date) VALUES (DATE ''' || new_end_date
||''' + interval ''1 day'', DATE ''' || row_one.end_date || ''')';
EXECUTE 'INSERT INTO ' || into_table || ' (start_date, end_date) VALUES (DATE ''' || new_start_date
||''', DATE ''' || new_end_date || ''')';
END IF;
ELSIF row_one.start_date <= new_start_date THEN
/* This row is earlier than the proposed period - It's overlapping
our start date - But is it of the same type? */
IF row_one.table = into_table THEN
/* A single row overlapping the start only and of the same
type - Update the end date and return the existing row ID */
EXECUTE 'UPDATE ' || into_table || ' SET end_date = ' || DATE || '''' || new_end_date ||
'''WHERE id = ' || row_one.id;
RETURN row_one.id;
ELSE
/* Single row, overlapping the start, and of a different type.
Trim back the existing row and Insert and return
newly generated ID. */
EXECUTE 'INSERT INTO ' || into_table || '(start_date, end_date) VALUES (DATE ''' || new_start_date
||''', DATE ''' || new_end_date || ''')';
new_id = EXECUTE('SELECT currval(''calendar_entry_id_seq'')');
RETURN new_id;
END IF;
ELSIF row_one.start_date > new_end_date THEN
/* This period is after the proposed period */
IF row_one.table = into_table THEN
/* Single row, overlapping the end, and of the same type.
Update the start_date of the existing row, and return it's
id. */
EXECUTE 'UPDATE ' || into_table || ' SET start_date = DATE ''' || new_start_date || '''
WHEREid = ' || row_one.id;
RETURN row_one.id;
ELSE
/* Single row, overlapping the end, and of a different type.
Update the existing rows start_date, insert a new row,
and return the new ID. */
EXECUTE 'UPDATE ' || into_table || ' SET start_date = DATE ''' || new_end_date || ''' + interval
''1day''';
EXECUTE 'INSERT INTO ' || into_table || ' (start_date, end_date) VALUES (DATE ''' ||
new_start|| ''',DATE ''' || new_end || ''')';
new_id = EXECUTE('SELECT currval(''calendar_entry_id_seq'')');
RETURN new_id;
END IF;
END IF;
ELSIF (num_entries = 2) THEN
FETCH cur_overlap INTO row_one;
FETCH cur_overlap INTO row_two;
/* Two rows */
IF (row_one.table = into_table) AND (row_two.table = into_table) THEN
/* Two overlapping/adjactent rows, both of the same type as the
new entry. Delete the second row, and adjust the end_date of
the first to stretch across until the end o the old second
row.
*/
EXECUTE 'DELETE FROM ' || into_table || ' WHERE id = ' || row_two.id;
EXECUTE 'UPDATE ' || into_table || ' SET end_date = ' || row_two.end_date || ' WHERE id = ' ||
row_one.id;
RETURN row_one.id;
ELSIF row_one.table = into_table THEN
/* Two overlapping/adjacent rows, only the row overlapping the
start is of the same type. Adjust the end date of this row to
be the end date of the new row.*/
EXECUTE 'UPDATE ' || into_table || ' SET end_date = DATE''' || new_end_date || ''' WHERE id = '
||row_one.id;
RETURN row_one.id;
ELSIF row_two.table = into_table THEN
/* Two overlapping/adjacent rows, only the row overlapping the
end is of the same type. Adjust the start date of this row
to be the start date of the new row.*/
EXECUTE 'UPDATE ' || into_table || ' SET start_date = DATE ''' || new_start_date || ''' WHERE
id= ' || row_two.id;
RETURN row_two.id;
ELSE
/* Two overlapping/adjacent rows, neither of which are of the
same type as the new row */
EXECUTE 'INSERT INTO ' || into_table || ' (start_date, end_date) VALUES (DATE ''' ||
new_start_date|| ''', DATE ''' || new_end_date || ''')';
new_id = EXECUTE("SELECT currval('calendar_entry_id_seq')");
RETURN new_id;
END IF;
END IF;
ELSE
RAISE EXCEPTION 'Operation aborted: Clashing booking detected';
END IF;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
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.
I'm trying to set up postgresql to work with postfix MTA and dovecot IMAP server. I am editing the pg_hba.conf file to get postgresql to accept password authentication for localhost. i have entered the following into pg_hba.conf -- host all all 127.0.0.1/32 password But this is not working and I'm getting the following error --- root@charlie /etc/init.d # /etc/init.d/postgresql restart * Restarting PostgreSQL... su: Authentication service cannot retrieve authentication info. (Ignored) postmaster stopped Has anyone dealt with this authentication issue and can lend a hand. Thank you for your time and patience. Mike
On Mon, 2005-08-22 at 12:53 -0400, Mike wrote: > I'm trying to set up postgresql to work with postfix MTA and dovecot > IMAP server. > I am editing the pg_hba.conf file to get postgresql to accept password > authentication for localhost. > > i have entered the following into pg_hba.conf -- > > host all all 127.0.0.1/32 password > > But this is not working and I'm getting the following error --- That does not seem to be relevant to your problem. > root@charlie /etc/init.d # /etc/init.d/postgresql restart > * Restarting PostgreSQL... > su: Authentication service cannot retrieve authentication info. This appears to be saying that the init script is calling su (presumably to change identity to postgres) but is failing for some reason - perhaps the postgres user has not been created in /etc/passwd? > (Ignored) > postmaster stopped > > Has anyone dealt with this authentication issue and can lend a hand. > > Thank you for your time and patience. > > Mike > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq -- Oliver Elphick olly@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA ======================================== Do you want to know God? http://www.lfix.co.uk/knowing_god.html