Re: Problem calling stored procedure

On Mon, Aug 22, 2005 at 03:17:02PM +0200, neil(dot)saunders(at)accenture(dot)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 '1 day') 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