Re: Problem calling stored procedure - Mailing list pgsql-sql

Ok, the whole thing is done and dusted - Thank you everybody for your
input. Apologies if I sometimes missed the obvious. For the sake of
anyone having the same problems and happens across this post, I learned
that:

- The result of a cursor assignment cannot be row counted, unless you
iterate through it
- Fetching a row at the end of a cursor returns an empty set
- Fancy IDEs can be deceiving, especially when trying to debug a
function.

Included below is the finished procedure.

Thanks again,

Neil.

-----Original Message-----

DECLARE      cur_overlap refcursor;      new_id 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 (new_start_date - interval '1 day',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 EXECUTE 'SELECT *, pg_class.relname
AS table FROM calendar_entries, pg_class WHERE (start_date, end_date)
OVERLAPS (DATE ''' || new_start_date || ''' - interval ''2 days'', DATE
''' || new_end_date || ''' + interval ''2 days'') AND property_id = ' ||
prop_id || ' AND pg_class.oid = calendar_entries.tableoid ORDER BY
start_date';        FETCH cur_overlap INTO row_one;    FETCH cur_overlap INTO row_two;
       IF (row_two.id IS NULL) 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.           */
           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. */                 RETURN row_one.id;
              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 ''1 day'' WHERE id = ' ||
row_one.id;                  EXECUTE 'INSERT INTO ' || row_one.table || '
(property_id, start_date, end_date) VALUES (' || prop_id || ', DATE '''
|| new_end_date || ''' + interval ''1 day'', DATE ''' ||
row_one.end_date || ''')';                  EXECUTE 'INSERT INTO ' || into_table || '
(property_id, start_date, end_date) VALUES (' || prop_id || ', DATE '''
|| new_start_date || ''', DATE ''' || new_end_date || ''')';                  SELECT currval('calendar_id_seq') INTO
new_id;                 RETURN new_id;                                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
generatedID. */ 
                                   EXECUTE 'INSERT INTO ' || into_table ||
'(property_id, start_date, end_date) VALUES (' || prop_id || ', DATE '''
|| new_start_date || ''', DATE ''' || new_end_date || ''')';                SELECT
currval('calendar_id_seq') INTO new_id;                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 || ''' WHERE id = ' ||
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 ''1 day''';                EXECUTE 'INSERT INTO ' ||
into_table || ' (property_id, start_date, end_date) VALUES (' || prop_id
|| ', DATE ''' || new_start || ''',DATE ''' || new_end || ''')';                SELECT
currval('calendar_id_seq') INTO new_id;                RETURN new_id;
            END IF;
        END IF;
    ELSIF (row_one.id IS NOT NULL AND row_two.id IS NOT
NULL) THEN
           /* 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 = 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 ||
' (property_id, start_date, end_date) VALUES (' || prop_id || ', DATE
''' || new_start_date || ''', DATE ''' || new_end_date || ''')';            SELECT currval('calendar_id_seq') INTO
new_id;            RETURN new_id;
        END IF;       ELSE            /* Row one is null too! We have nothing to fear. Go ahead
and               insert it */            EXECUTE 'INSERT INTO ' || into_table ||
' (property_id, start_date, end_date) VALUES (' || prop_id || ', DATE
''' || new_start_date || ''', DATE ''' || new_end_date || ''')';            SELECT currval('calendar_id_seq') INTO
new_id;            RETURN new_id;       END IF;  ELSE
      RAISE EXCEPTION 'Operation aborted: Clashing booking detected';
  END IF;
END;


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.