Re: Datatypes in PL/PSQL functions with multiple arguments

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