Thread: 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)

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';

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:

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

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








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

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.

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

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\]

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/

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:

> 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
>
>> ERROR:  invalid regular expression: brackets [] not balanced.
>>
>>
>>
>> Thanks for your help
>> /David
>>
>>
>>
>>
>>
>>
>>
>>
>>>> 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)
>>>
>>>
>>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 7: don't forget to increase your free space map settings
>>
>>
>

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

> ERROR:  invalid regular expression: brackets [] not balanced.
>
>
>
> Thanks for your help
> /David
>
>
>
>
>
>
>
>
>>> 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)
>>
>>
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
>

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/