Search Postgresql Archives

Re: Datatypes in PL/PSQL functions with multiple arguments

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux