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

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/

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?

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

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

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?

Hi all,

I have a web interface with offers a search field. This search field
will look for the string X in 12 different columns. If the string is
found anywhere I return the row.

The problem is that the user is eable to put spacial character like : [*
This create invalid regular expression and make my sql crash.
ICNUM~* #descriptionOrKeyword#

Is there a way to disable all meta-character. I found this in the
manual .. but haven't found example :-(:
: ....with ***=, the rest of the RE is taken to be a literal string,
with all characters
considered ordinary characters.

I know my question si basic . but I search around and haven't found so
far .. please help :-)

Thanks
/David

\Richard Huxton wrote:

On Wed, Apr 20, 2005 at 11:28:28AM -0400, David Gagnon wrote:

I have a web interface with offers a search field. This search field
will look for the string X in 12 different columns. If the string is
found anywhere I return the row.

The problem is that the user is eable to put spacial character like : [*
This create invalid regular expression and make my sql crash.
ICNUM~* #descriptionOrKeyword#

Is there a way to disable all meta-character.

Why are you doing a regular expression search if you don't want to
allow regular expressions?

I found this in the manual .. but haven't found example :-(:
: ....with ***=, the rest of the RE is taken to be a literal string,
with all characters considered ordinary characters.

Read again the entire sentence, especially the first few words:

If an RE begins with ***=, the rest of the RE is taken to be a
literal string, with all characters considered ordinary characters.

Here are some examples:

SELECT 'test string' ~ 'test[*';
ERROR: invalid regular expression: brackets [] not balanced

SELECT 'test string' ~ '***=test[*';
?column?
----------
f
(1 row)

SELECT 'test[* string' ~ '***=test[*';
?column?
----------
t
(1 row)

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Michael Fuhr wrote:

On Wed, Apr 20, 2005 at 11:28:28AM -0400, David Gagnon wrote:

I have a web interface with offers a search field. This search field
will look for the string X in 12 different columns. If the string is
found anywhere I return the row.

The problem is that the user is eable to put spacial character like : [*
This create invalid regular expression and make my sql crash.
ICNUM~* #descriptionOrKeyword#

Is there a way to disable all meta-character.

Why are you doing a regular expression search if you don't want to
allow regular expressions?

Maybe there is a simple way to to this but I want find string X in
different column. The search must not be case sensitive.

So that searching "aBc" in "abcDef" return true. I don't want
META-CHaracter. Or at least I don't want meta-character to cause errors
(i.e.: No

ERROR: invalid regular expression: brackets [] not balanced.

Thanks for your help
/David

On Wed, 2005-04-20 at 12:36, David Gagnon wrote:

Michael Fuhr wrote:

On Wed, Apr 20, 2005 at 11:28:28AM -0400, David Gagnon wrote:

I have a web interface with offers a search field. This search field
will look for the string X in 12 different columns. If the string is
found anywhere I return the row.

The problem is that the user is eable to put spacial character like : [*
This create invalid regular expression and make my sql crash.
ICNUM~* #descriptionOrKeyword#

Is there a way to disable all meta-character.

Why are you doing a regular expression search if you don't want to
allow regular expressions?

Maybe there is a simple way to to this but I want find string X in
different column. The search must not be case sensitive.

So that searching "aBc" in "abcDef" return true. I don't want
META-CHaracter. Or at least I don't want meta-character to cause errors
(i.e.: No

ERROR: invalid regular expression: brackets [] not balanced.

I would generally scrub the input before it go to postgresql. Basically
do a simple string_replace type function that replaces anything that
ISN'T alphanum with nothing.

On Wed, 2005-04-20 at 13:05, David Gagnon wrote:

Hi Scott,

I would generally scrub the input before it go to postgresql. Basically
do a simple string_replace type function that replaces anything that
ISN'T alphanum with nothing.

If I change the original string the user may not get what he expects as
result. abc[d] is not the samething than abcd... am I right?

Then replace it with properly escaped strings:

abc[d] becomes abc\[d\]

Hi Scott,

I would generally scrub the input before it go to postgresql. Basically
do a simple string_replace type function that replaces anything that
ISN'T alphanum with nothing.

If I change the original string the user may not get what he expects as
result. abc[d] is not the samething than abcd... am I right?

Thanks for your help
/David

David Gagnon wrote:

Maybe there is a simple way to to this but I want find string X in
different column. The search must not be case sensitive.

So that searching "aBc" in "abcDef" return true. I don't want
META-CHaracter. Or at least I don't want meta-character to cause
errors (i.e.: No

Ok, how about a better way to do this?

select * from mytable where lower(mycol) LIKE lower("%" || lower(X) || "%");

Does this work? It seems that this may be the best way to handle this
sort of thing.

Best Wishes,
Chris Travers
Metatron Technology Consulting

Thanks for your help!

Just want to share the solution I got to solve my problem. I wanted to
be eable to search a string (say X) (non case sensitive) without having
meta-character involved. The X string come directy from the web so any
[%]* may cause error in regular expression (because they form non valid
expression)

1) Using like: select * from mytable where lower(mycol) LIKE lower("%"
|| lower(X) || "%");
Mostly perfect solution. Don't crash but % still have a special
meaning. Wich means anything
2)Using regular expression: select * from mytable where mycol ~*
('***=' || X)
For the test I did it doesn't, fit all my need. No meta character and
no escaping to do on X before launching the SQL request.

Thanks for your help!!!
Have a great day
/David

Chris Travers wrote:

On Thu, Apr 21, 2005 at 09:03:43AM -0400, David Gagnon wrote:

Just want to share the solution I got to solve my problem. I wanted to
be eable to search a string (say X) (non case sensitive) without having
meta-character involved. The X string come directy from the web so any
[%]* may cause error in regular expression (because they form non valid
expression)

1) Using like: select * from mytable where lower(mycol) LIKE lower("%"
|| lower(X) || "%");
Mostly perfect solution. Don't crash but % still have a special
meaning. Wich means anything

Have you considered using position() or strpos()? They do simple
substring searches without any metacharacters.

SELECT position(lower('AbC') in lower('aBcDeF'));
position
----------
1
(1 row)

SELECT position(lower('xYz') in lower('aBcDeF'));
position
----------
0
(1 row)

You might also want to look at the contrib/pg_trgm module to see
if it would be useful.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/