Re: Datatypes in PL/PSQL functions with multiple arguments

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(at)archonet(dot)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 two
> lines of code perhaps. Oh, and how you are calling it too.
>
> --
> Richard Huxton
> Archonet Ltd
>