I would call it like the following:
SELECT SIMPLE_date_used('5/11/06','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;
result boolean;
BEGIN
IF arg_production_schedule_id != 0 THEN
SELECT INTO result ((CAST(given_date AS date) >= beginning_date) AND (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';
This is how I would think it should work changed (CAST(arg_id AS integer)) TO MyTable.arg_id != ''arg_id'':
CREATE FUNCTION SIMPLE_date_used (date,integer) RETURNS text AS '
DECLARE
given_date ALIAS for $1;
arg_id ALIAS for $2;
result boolean;
BEGIN
IF arg_production_schedule_id != 0 THEN
SELECT INTO result ((CAST(given_date AS date) >= beginning_date) AND (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 != ''arg_id'');
IF result = TRUE THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
' LANGUAGE 'plpgsql';
This is the error message I'm getting by using: ''arg_id'' instead of: (CAST(arg_id AS integer))
SELECT production_scheduled_for_date('2005-05-12', '49');
ERROR: invalid input syntax for integer: "arg_id"
CONTEXT: PL/pgSQL function "SIMPLE_date_used" line 10 at select into variables
Any thoughts?
On 4/19/05, Richard Huxton <
dev@xxxxxxxxxxxx> wrote:
Benjamin Holmberg wrote:
> Hello-
>
> This is my first foray into pl/psql so forgive me if I sound totally
> incompetent.
>
> I've been writing a few functions, and have come across some screwing data
> typing issues.
>
> When creating a function which accepts a single argument, things work just
> fine, variable can be used throughout the function as expected with no
> modification.
> When creating functions containing two or more arguments, I have to
> explicity cast the arguments whenever I use them (loading/casting into
> another variable is an option, haven't tried though) to prevent runtime
> errors. The functions get called just fine, but then run into problems using
> any of the given arguments.
Could you perhaps give an example function? Something with one or two
lines of code perhaps. Oh, and how you are calling it too.
--
Richard Huxton
Archonet Ltd
