[GENERAL] Datatypes in PL/PSQL functions with multiple arguments
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. Has anyone had any experience with this? Please advise! Thanks! Benjamin select version(); version PostgreSQL 7.4.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.1 (Mandrakelinux 10.1 3.4.1-4mdk)
Re: [GENERAL] Datatypes in PL/PSQL functions with multiple arguments
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Datatypes in PL/PSQL functions with multiple arguments
On Tue, Apr 19, 2005 at 10:01:26AM -0500, Benjamin Holmberg wrote: 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. Please post an example of what you're doing: a simple function, how you're invoking it, and the error message(s). -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] 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@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
Re: [GENERAL] 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
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Datatypes in PL/PSQL functions with multiple arguments
Benjamin Holmberg wrote:
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
That's because ''arg_id'' is the string value arg_id, those six
characters rather than the value of any variable.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Datatypes in PL/PSQL functions with multiple arguments
Then I guess I need to know how one can encapsulate variables in quotes, yet let the pl/pgsql interpreter interpolate.
In the case of my SELECT INTO, are the quotes even needed to avoid potential confusion with column names?On 4/19/05, Richard Huxton
dev@archonet.com wrote:Benjamin Holmberg wrote: 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_idThat's because ''arg_id'' is the string value arg_id, those six
characters rather than the value of any variable.-- Richard Huxton Archonet Ltd
