Problem calling stored procedure - Mailing list pgsql-sql

Hi all,

I've written a stored procedure but am having trouble calling it.

The procedure name is called "insert_period" and 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 issue - 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',
DATEnew_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
overlappingeither 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
enddate 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
existingrow 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
ofthe 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
existingrows 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
secondrow, 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;
RETURNrow_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.
Adjustthe 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. 

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to      choose an index scan if your joining column's
datatypesdo not      match 




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.