This is one of the bad ones hacked up to work like it should...
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@archonet.com 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 twolines of code perhaps. Oh, and how you are calling it too.-- Richard Huxton Archonet Ltd
