From: Michael Fuhr <mike@fuhr.org>
To: neil.saunders@accenture.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Problem calling stored procedure
Date: Mon, 22 Aug 2005 08:28:46 -0600
Message-ID: <20050822142846.GA60111@winnie.fuhr.org> (raw)
In-Reply-To: <B74B7433CDDD2C4AA41D94832E32E19D92EE91@EMEXM1112.dir.svc.accenture.com>
References: <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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mike@fuhr.org, neil.saunders@accenture.com
Subject: Re: Problem calling stored procedure
In-Reply-To: <20050822142846.GA60111@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox