Re: Problem calling stored procedure

On Mon, Aug 22, 2005 at 03:17:02PM +0200, neil.saunders@accenture.com wrote:
> 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
'1day') AND property_id = $3 LIMIT 1
 
> CONTEXT: PL/pgSQL function "insert_period" line 12 at select into variables

>       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;
 

Why did you write "DATE new_start_date" and "DATE new_end_date"?
That's not the correct syntax for casting, and those variables are
already of type DATE anyway.

> 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?

$N in the error message refers to a statement preparation argument,
not to a function argument.  For insight into what PL/pgSQL is
doing, see the PREPARE documentation:

http://www.postgresql.org/docs/8.0/static/sql-prepare.html

-- 
Michael Fuhr