X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 39837D6E7E
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 22 Aug 2005 11:12:57 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 79173-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 22 Aug 2005 14:12:55 +0000 (GMT)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210])
	by svr1.postgresql.org (Postfix) with ESMTP id 291B6D6E2F
	for <pgsql-sql@postgresql.org>; Mon, 22 Aug 2005 11:12:54 -0300 (ADT)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 956A535623; Mon, 22 Aug 2005 07:12:59 -0700 (PDT)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 93CFD3561F; Mon, 22 Aug 2005 07:12:59 -0700 (PDT)
Date: Mon, 22 Aug 2005 07:12:59 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: neil.saunders@accenture.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Problem calling stored procedure
In-Reply-To: 
 <B74B7433CDDD2C4AA41D94832E32E19D92EE91@EMEXM1112.dir.svc.accenture.com>
Message-ID: <20050822070949.P87514@megazone.bigpanda.com>
References: 
 <B74B7433CDDD2C4AA41D94832E32E19D92EE91@EMEXM1112.dir.svc.accenture.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.005 required=5 tests=[AWL=0.005]
X-Spam-Level: 
X-Archive-Number: 200508/185
X-Sequence-Number: 22413

On Mon, 22 Aug 2005 neil.saunders@accenture.com wrote:

> 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 function using
> various calling arguments without issue - I only get this problem when
> trying to call the function through a client.
>
> Research on this revealed problems when variable names are named after
> existing postgres functions/tables/columns, but I to my knowledge there
> is nothing in the database named the same of my arguments. I've tried
> renaming them all to random names, 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?

Me too, however in any case, DATE <blah> is for date literals so I don't
believe it's what you want in this case anyway since you're using a
variable.  I think you'd just want new_start_date, etc, since they're
already dates.