Обсуждение: textpos() function
I just upgraded to postgresql 7.0 and was restoring my data when I noticed I had a function defined that used the builtin textpos() function. This function was available in 6.5.3 but seems to have dissapeared in 7.0.. Is there any way to restore this function or has it been renamed to something else (i found strpos() but i'm not sure if that does exactly the same thing). -- |---------------------------------|----------------------------| | Hitesh Patel | Voice: (541) 759-3126 | | | Fax: (541) 759-3214 | | Preferred Communications Inc. | Email: hitesh@pciwest.net | |---------------------------------|----------------------------|
Hitesh Patel <hitesh@presys.com> writes:
> I just upgraded to postgresql 7.0 and was restoring my data when I
> noticed I had a function defined that used the builtin textpos()
> function. This function was available in 6.5.3 but seems to have
> dissapeared in 7.0.. Is there any way to restore this function or has
> it been renamed to something else (i found strpos() but i'm not sure if
> that does exactly the same thing).
Looks like it's called position() now --- that function has the same OID
(849) as 6.5's textpos(), and even more damningly points to a C function
that's still called textpos():
regression=# select oid,* from pg_proc where oid=849;
oid | proname | proowner | prolang | proisinh | proistrusted | proiscachable |
pronargs | proretset | prorettype | proargtypes | probyte_pct | properbyte_cpu
| propercall_cpu | prooutin_ratio | prosrc | probin
-----+----------+----------+---------+----------+--------------+---------------+
----------+-----------+------------+-------------+-------------+----------------
+----------------+----------------+---------+--------
849 | position | 256 | 11 | f | t | t |
2 | f | 23 | 25 25 | 100 | 0
| 1 | 0 | textpos | -
(1 row)
We've made a number of changes in 7.0 to bring function and type names
into alignment with the SQL92 standard. The incompatibilities I knew
about were in date/time types and functions, but I guess this is another
one ...
If you really don't want to update your app's code just yet, you can
install a pg_proc entry that defines textpos() with a CREATE FUNCTION
command. But the long-term answer is to fix your code to conform with
the standard.
regards, tom lane
Tom Lane wrote:
>
\> Looks like it's called position() now --- that function has the same
OID
> (849) as 6.5's textpos(), and even more damningly points to a C function
> that's still called textpos():
>
> regression=# select oid,* from pg_proc where oid=849;
> oid | proname | proowner | prolang | proisinh | proistrusted | proiscachable |
> pronargs | proretset | prorettype | proargtypes | probyte_pct | properbyte_cpu
> | propercall_cpu | prooutin_ratio | prosrc | probin
> -----+----------+----------+---------+----------+--------------+---------------+
> ----------+-----------+------------+-------------+-------------+----------------
> +----------------+----------------+---------+--------
> 849 | position | 256 | 11 | f | t | t |
> 2 | f | 23 | 25 25 | 100 | 0
> | 1 | 0 | textpos | -
> (1 row)
>
> We've made a number of changes in 7.0 to bring function and type names
> into alignment with the SQL92 standard. The incompatibilities I knew
> about were in date/time types and functions, but I guess this is another
> one ...
>
> If you really don't want to update your app's code just yet, you can
> install a pg_proc entry that defines textpos() with a CREATE FUNCTION
> command. But the long-term answer is to fix your code to conform with
> the standard.
>
> regards, tom lane
I changed my code to use position() instead of textpos(), the only
problem is that I now get a parse error at the following line:
i:= position(stringa, ',');
The exact error message is:
ERROR: parser: parse error at or near ","
Trying to run a query like, 'select position(email, ',') from table'
fails with the same parse error, however 'select strpos(email, ',') from
table' runs fine...
any hints?
--
|---------------------------------|----------------------------|
| Hitesh Patel | Voice: (541) 759-3126 |
| Facilities Development Manager | Fax: (541) 759-3214 |
| Preferred Systems | Email: hitesh@presys.com |
|---------------------------------|----------------------------|
Hitesh Patel wrote:
>
> > If you really don't want to update your app's code just yet, you can
> > install a pg_proc entry that defines textpos() with a CREATE FUNCTION
> > command. But the long-term answer is to fix your code to conform with
> > the standard.
> >
> > regards, tom lane
>
> I changed my code to use position() instead of textpos(), the only
> problem is that I now get a parse error at the following line:
>
> i:= position(stringa, ',');
>
> The exact error message is:
>
> ERROR: parser: parse error at or near ","
>
> Trying to run a query like, 'select position(email, ',') from table'
> fails with the same parse error, however 'select strpos(email, ',') from
> table' runs fine...
>
> any hints?
This is from memory, so please verify with the docs, but I
believe the SQL92 standared is something like:
SELECT position(',' in stringa) ...
Hope that helps,
Mike Mascari
>
> This is from memory, so please verify with the docs, but I
> believe the SQL92 standared is something like:
>
> SELECT position(',' in stringa) ...
>
> Hope that helps,
>
> Mike Mascari
I knew it was something easy.. works great.. thanks for the help
--
|---------------------------------|----------------------------|
| Hitesh Patel | Voice: (541) 759-3126 |
| Facilities Development Manager | Fax: (541) 759-3214 |
| Preferred Systems | Email: hitesh@presys.com |
|---------------------------------|----------------------------|