Date: Mon, 22 Aug 2005 08:28:46 -0600
From: Michael Fuhr <mike@fuhr.org>
To: neil.saunders@accenture.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Problem calling stored procedure
Message-ID: <20050822142846.GA60111@winnie.fuhr.org>
References: 
 <B74B7433CDDD2C4AA41D94832E32E19D92EE91@EMEXM1112.dir.svc.accenture.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <B74B7433CDDD2C4AA41D94832E32E19D92EE91@EMEXM1112.dir.svc.accenture.com>

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