Re: Problem calling stored procedure

On Mon, 22 Aug 2005 neil(dot)saunders(at)accenture(dot)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.