From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: neil.saunders@accenture.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Problem calling stored procedure
Date: Mon, 22 Aug 2005 07:12:59 -0700 (PDT)
Message-ID: <20050822070949.P87514@megazone.bigpanda.com> (raw)
In-Reply-To: <B74B7433CDDD2C4AA41D94832E32E19D92EE91@EMEXM1112.dir.svc.accenture.com>
References: <B74B7433CDDD2C4AA41D94832E32E19D92EE91@EMEXM1112.dir.svc.accenture.com>
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.
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: sszabo@megazone.bigpanda.com, neil.saunders@accenture.com
Subject: Re: Problem calling stored procedure
In-Reply-To: <20050822070949.P87514@megazone.bigpanda.com>
* 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