Don't forget to cc: the list...
Benjamin Holmberg wrote:
> This is one of the "bad" ones...
>
> I would call it like the following:
> SELECT SIMPLE_date_used('5/11/06','5');
Well, you're trying to call it with two text-values here (or at least
two unknown values).
SELECT simple_date_used('5/11/06'::date, 5)
> beginning_date and ending_date are date columns in MyTable. The function is
> checking to see if given_date falls within a date range that has already
> been established in another row, with the exclusion of the row defined by
> arg_id.
>
> ==============
> CREATE FUNCTION SIMPLE_date_used (date,integer) RETURNS text AS '
> DECLARE
> given_date ALIAS for $1;
> arg_id ALIAS for $2;
You've got a column called arg_id below, so it's best to call this
something else (p_arg_id or something). That stops both me and plpgsql
from getting confused :-)
> result boolean;
> BEGIN
> IF arg_production_schedule_id != 0 THEN
> SELECT INTO result ((CAST(given_date AS date) >= beginning_date) AND
Now, these casts shouldn't be necessary. Are you saying you get errors
when you just use "given_date <= ending_date"?
> (CAST(given_date AS date) <= ending_date)) FROM MyTable WHERE
> ((((CAST(given_date AS date) >= beginning_date) AND (CAST(given_date AS
> date) <= ending_date)) = TRUE) AND MyTable.arg_id != (CAST(arg_id AS
> integer)));
> IF result = TRUE THEN
> RETURN TRUE;
> ELSE
> RETURN FALSE;
> END IF;
> END;
> ' LANGUAGE 'plpgsql';
I've got to say I'd write the function more like:
SELECT INTO result true FROM MyTable
WHERE p_given_date >= beginning_date AND p_given_date <= ending_date
AND arg_id <> p_arg_id
RETURN FOUND;
The "FOUND" variable gets set when a query returns results.
--
Richard Huxton
Archonet Ltd