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
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)
