Terry Carlin wrote:
>
> I was wrong about the locate() function in the odbc driver. Thomas
> Lockhart pointed out that it mapped locate in the driver to strpos in
> postgres. This tweaked a memory. What happens with the locate() odbc
> function in the odbc test suite is that it calls locate with the
> following arguments: locate(string, string, position). When this maps
> over to the strpos function, it fails as the strpos function only expects
> arguments like: strpos(string, string).
>
> benchmark=# \df strpos
> List of functions
> Result | Function | Arguments
> ---------+----------+------------
> integer | strpos | text, text
> (1 row)
>
> I had added a piece of functionality to the odbc.sql script that
> overloaded the strpos function like this:
>
> CREATE FUNCTION strpos(text,text,integer)
> RETURNS integer
> AS 'SELECT position($1 in substring($2 from $3))'
> LANGUAGE 'SQL';
>
Oh that explains it.
However the spec of locate() isn't clear to me.
LOCATE(string_exp1, string_exp2[, start]) (ODBC 1.0)
Returns the starting position of the first occurrence of string_exp1
within string_exp2. The search for the first occurrence of string_exp1
begins with the first character position in string_exp2 unless the
optional argument, start, is specified. If start is specified, the
search begins with the character position indicated by the value of
start. The first character position in string_exp2 is indicated by the
value 1. If string_exp1 is not found within string_exp2, the value 0 is
returned.
regards,
Hiroshi Inoue