Обсуждение: Request to add feature to the Position function
Hi,
position(substring in string)
as listed here:
locates sub string in a string.
It doesn't support locateing the substring from the back.
For example:
position('om' in 'Tomomas')
gives 2
But if I want to locate the first occurance from the back of the string it's impossible/
My suggestion is to create a function
position(substring in string,order)
where order can be: begin, end
and it will find the string according to this parameter.
This is pretty easy to implement and should be a part of the PostgreSQL tools.
similar fuctionality exists in trim function where user can specify leading or taling parameter
On 03/27/2017 08:05 AM, Ron Ben wrote: > Hi, > position(substring in string) > as listed here: > https://www.postgresql.org/docs/9.1/static/functions-string.html > locates sub string in a string. > > It doesn't support locateing the substring from the back. > > For example: > > position('om' in 'Tomomas') > gives 2 > > But if I want to locate the first occurance from the back of the string > it's impossible/ aklaver@test=> select position('om' in reverse('Tomomas')); position ---------- 4 > > My suggestion is to create a function > position(substring in string,order) > where order can be: begin, end > > and it will find the string according to this parameter. > This is pretty easy to implement and should be a part of the PostgreSQL > tools. > > similar fuctionality exists in trim function where user can specify > leading or taling parameter -- Adrian Klaver adrian.klaver@aklaver.com
That does not return the correct answer for the original poster's request.
flpg=# select position('om' in reverse('Tomomasaaaaaaaaaaa'));
position
----------
15
(1 row)
On Mon, Mar 27, 2017 at 11:43 AM, Adrian Klaver
<adrian.klaver@aklaver.com> wrote:
> On 03/27/2017 08:05 AM, Ron Ben wrote:
>>
>> Hi,
>> position(substring in string)
>> as listed here:
>> https://www.postgresql.org/docs/9.1/static/functions-string.html
>> locates sub string in a string.
>>
>> It doesn't support locateing the substring from the back.
>>
>> For example:
>>
>> position('om' in 'Tomomas')
>> gives 2
>>
>> But if I want to locate the first occurance from the back of the string
>> it's impossible/
>
>
> aklaver@test=> select position('om' in reverse('Tomomas'));
> position
> ----------
> 4
>
>
>>
>> My suggestion is to create a function
>> position(substring in string,order)
>> where order can be: begin, end
>>
>> and it will find the string according to this parameter.
>> This is pretty easy to implement and should be a part of the PostgreSQL
>> tools.
>>
>> similar fuctionality exists in trim function where user can specify
>> leading or taling parameter
>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
That does not return the correct answer for the original poster's request.
flpg=#select position('om' in reverse('Tomomasaaaaaaaaaaa'));
position
----------
15
(1 row)
Easy oversight to make - if you reverse the string being searched you must also reverse the string that you are looking for.
That said the OP hasn't actually defined what they mean by "locate" when considered in a backward sense - some length adjustments might be needed to compensate for the length of the term being searched for.
Patches are welcomed. Given the lack of any links to previous requests of this nature I'd say its not exactly a highly in-demand capability.
David J.
On 03/27/2017 09:03 AM, Brian Dunavant wrote:
> That does not return the correct answer for the original poster's request.
>
> flpg=# select position('om' in reverse('Tomomasaaaaaaaaaaa'));
> position
> ----------
> 15
> (1 row)
It shows the position counting back from the end. If you want counting
from the front:
aklaver@test=> select (length('Tomomasaaaaaaaaaaa')+ 1) - position('om'
in reverse('Tomomasaaaaaaaaaaa'));
?column?
----------
4
>
>
>
>
> On Mon, Mar 27, 2017 at 11:43 AM, Adrian Klaver
> <adrian.klaver@aklaver.com> wrote:
>> On 03/27/2017 08:05 AM, Ron Ben wrote:
>>>
>>> Hi,
>>> position(substring in string)
>>> as listed here:
>>> https://www.postgresql.org/docs/9.1/static/functions-string.html
>>> locates sub string in a string.
>>>
>>> It doesn't support locateing the substring from the back.
>>>
>>> For example:
>>>
>>> position('om' in 'Tomomas')
>>> gives 2
>>>
>>> But if I want to locate the first occurance from the back of the string
>>> it's impossible/
>>
>>
>> aklaver@test=> select position('om' in reverse('Tomomas'));
>> position
>> ----------
>> 4
>>
>>
>>>
>>> My suggestion is to create a function
>>> position(substring in string,order)
>>> where order can be: begin, end
>>>
>>> and it will find the string according to this parameter.
>>> This is pretty easy to implement and should be a part of the PostgreSQL
>>> tools.
>>>
>>> similar fuctionality exists in trim function where user can specify
>>> leading or taling parameter
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
--
Adrian Klaver
adrian.klaver@aklaver.com
Putting together Adrian Klaver's, and David Johnson's suggestions I
think gets to what he was asking for:
# select length('Tomomasaaaaomaaaaaa') - position(reverse('om') in
reverse('Tomomasaaaaomaaaaaa'));
?column?
----------
12
On Mon, Mar 27, 2017 at 12:16 PM, Adrian Klaver
<adrian.klaver@aklaver.com> wrote:
> On 03/27/2017 09:03 AM, Brian Dunavant wrote:
>>
>> That does not return the correct answer for the original poster's request.
>>
>> flpg=# select position('om' in reverse('Tomomasaaaaaaaaaaa'));
>> position
>> ----------
>> 15
>> (1 row)
>
>
> It shows the position counting back from the end. If you want counting from
> the front:
>
> aklaver@test=> select (length('Tomomasaaaaaaaaaaa')+ 1) - position('om' in
> reverse('Tomomasaaaaaaaaaaa'));
> ?column?
> ----------
> 4
>
>
>>
>>
>>
>>
>> On Mon, Mar 27, 2017 at 11:43 AM, Adrian Klaver
>> <adrian.klaver@aklaver.com> wrote:
>>>
>>> On 03/27/2017 08:05 AM, Ron Ben wrote:
>>>>
>>>>
>>>> Hi,
>>>> position(substring in string)
>>>> as listed here:
>>>> https://www.postgresql.org/docs/9.1/static/functions-string.html
>>>> locates sub string in a string.
>>>>
>>>> It doesn't support locateing the substring from the back.
>>>>
>>>> For example:
>>>>
>>>> position('om' in 'Tomomas')
>>>> gives 2
>>>>
>>>> But if I want to locate the first occurance from the back of the string
>>>> it's impossible/
>>>
>>>
>>>
>>> aklaver@test=> select position('om' in reverse('Tomomas'));
>>> position
>>> ----------
>>> 4
>>>
>>>
>>>>
>>>> My suggestion is to create a function
>>>> position(substring in string,order)
>>>> where order can be: begin, end
>>>>
>>>> and it will find the string according to this parameter.
>>>> This is pretty easy to implement and should be a part of the PostgreSQL
>>>> tools.
>>>>
>>>> similar fuctionality exists in trim function where user can specify
>>>> leading or taling parameter
>>>
>>>
>>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian.klaver@aklaver.com
>>>
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
On 03/27/2017 09:03 AM, Brian Dunavant wrote:That does not return the correct answer for the original poster's request.
flpg=# select position('om' in reverse('Tomomasaaaaaaaaaaa'));
position
----------
15
(1 row)
It shows the position counting back from the end. If you want counting from the front:
aklaver@test=> select (length('Tomomasaaaaaaaaaaa')+ 1) - position('om' in reverse('Tomomasaaaaaaaaaaa'));
?column?
----------
4
Choosing a word containing an embedded palindrome is not recommended when dealing with "reverse string" examples...
David J.
On 03/27/2017 09:15 AM, David G. Johnston wrote:
> On Mon, Mar 27, 2017 at 9:03 AM, Brian Dunavant <brian@omniti.com
> <mailto:brian@omniti.com>>wrote:
>
> That does not return the correct answer for the original poster's
> request.
>
> flpg=#
>
> select position('om' in reverse('Tomomasaaaaaaaaaaa'));
> position
> ----------
> 15
> (1 row)
>
>
> Easy oversight to make - if you reverse the string being searched you
> must also reverse the string that you are looking for.
>
> That said the OP hasn't actually defined what they mean by "locate" when
> considered in a backward sense - some length adjustments might be needed
> to compensate for the length of the term being searched for.
I think there is also a RTL language involved, so we may need more
information to answer the question.
>
> Patches are welcomed. Given the lack of any links to previous requests
> of this nature I'd say its not exactly a highly in-demand capability.
>
> David J.
>
--
Adrian Klaver
adrian.klaver@aklaver.com
De : pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] De la part de Ron Ben
Envoyé : Monday, March 27, 2017 11:05 AM
À : pgsql-general@postgresql.org
Objet : [GENERAL] Request to add feature to the Position function
<clip>
> position(substring in string)
> as listed here:
> https://www.postgresql.org/docs/9.1/static/functions-string.html
> locates sub string in a string.
>
> It doesn't support locateing the substring from the back.
<clip>
If what you mean by ‘from the back’ is ‘the last occurrence in a string read from left to right’, here is a quickie plpgsql function:
CREATE OR REPLACE FUNCTION rposition(substr text, str text)
RETURNS integer AS
$BODY$
declare
pos integer;
lastpos integer;
begin
pos := position(substr in str);
lastpos := 0;
while pos <> 0 loop
lastpos := pos;
pos := position(substr in substring(str from pos + 1));
if pos > 0 then pos := pos + lastpos ; end if;
end loop;
return lastpos;
end;
$BODY$
LANGUAGE plpgsql IMMUTABLE
On 03/28/2017 12:29 AM, Ron Ben wrote: > Here is a refer to the stackoverflow question: > > http://stackoverflow.com/questions/42974822/find-sub-string-position-from-the-end-of-string-in-postgresql > > it carry another example and the solution however I still think that > this fuctionality should be avliable in a build-in postgresql function. > This would be a new feature and Postgres only introduces new features in new major version releases. The next major release is 10 and it is the latter stages of development. Not sure if there is a feature freeze on yet or not. In any case it will not be released for production until fall sometime(best guess, I have nothing to do with release schedule). If it did not make it into that release you are looking another year out. So if this is something you need soon, then I would say the solution would be to create your own function using the suggestions provided. If you really want this as a built in, regardless of when it is done I would suggest either filing bug: https://www.postgresql.org/account/login/?next=/account/submitbug/ or post to the --hackers list: https://www.postgresql.org/list/pgsql-hackers/ -- Adrian Klaver adrian.klaver@aklaver.com
On 03/28/2017 12:29 AM, Ron Ben wrote:Here is a refer to the stackoverflow question:
http://stackoverflow.com/questions/42974822/find-sub-string- position-from-the-end-of- string-in-postgresql
it carry another example and the solution however I still think that
this fuctionality should be avliable in a build-in postgresql function.
This would be a new feature and Postgres only introduces new features in new major version releases. The next major release is 10 and it is the latter stages of development. Not sure if there is a feature freeze on yet or not. In any case it will not be released for production until fall sometime(best guess, I have nothing to do with release schedule).
Not technically frozen but if the implementing patch isn't already in the commit-fest system it isn't getting into 10. All feature freeze means at this point is that anything currently in the commit-fest system that doesn't get committed by that time it going to be punted to 11.
If it did not make it into that release you are looking another year out. So if this is something you need soon, then I would say the solution would be to create your own function using the suggestions provided. If you really want this as a built in, regardless of when it is done I would suggest either filing bug:
https://www.postgresql.org/account/login/?next=/account/subm itbug/
This is a poor recommendation given that its absence is not a bug.
or
post to the --hackers list:
https://www.postgresql.org/list/pgsql-hackers/
Maybe not ideal but we don't have any kind of "feature requests" lists so its either -hackers or -general.
I'd even go so far as to say "-general" is better because you get more eyes on the request for people to give it support and, apparently in this case, finding people to do the leg work of writing a patch.
David J.
On 03/29/2017 04:06 AM, Ron Ben wrote: > I never expected it to be implemented now.. I understand that there are > policies and priorities > > I tried to find a feature request chanle but there is none... This list and and/or --hackers would be that channel. > I don't know how the postresql team decied what is on the "to do list" > and what is not. If you scan the Todo list: https://wiki.postgresql.org/wiki/Todo most of the items reference messages to --general or --hackers, where enough people expressed interest in the feature. To go from there to done will require someone willing to do the coding. I will start the ball rolling by saying I am interested in the feature. Unfortunately I do not have the coding skills to make it happen. > This is a feature which I think people will find usefull and it doesn't > seem like a lot of work to implement it. > > > ב מרץ 28, 2017 19:42, David G. Johnston כתב: > > On Tue, Mar 28, 2017 at 7:09 AM, Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>wrote: > > On 03/28/2017 12:29 AM, Ron Ben wrote: > > Here is a refer to the stackoverflow question: > > http://stackoverflow.com/questions/42974822/find-sub-string-position-from-the-end-of-string-in-postgresql > <http://stackoverflow.com/questions/42974822/find-sub-string-position-from-the-end-of-string-in-postgresql> > > it carry another example and the solution however I > still think that > this fuctionality should be avliable in a build-in > postgresql function. > > > This would be a new feature and Postgres only introduces new > features in new major version releases. The next major > release is 10 and it is the latter stages of development. > Not sure if there is a feature freeze on yet or not. In any > case it will not be released for production until fall > sometime(best guess, I have nothing to do with release > schedule). > > > Not technically frozen but if the implementing patch isn't > already in the commit-fest system it isn't getting into 10. All > feature freeze means at this point is that anything currently in > the commit-fest system that doesn't get committed by that time > it going to be punted to 11. > > > If it did not make it into that release you are looking > another year out. So if this is something you need soon, > then I would say the solution would be to create your own > function using the suggestions provided. If you really want > this as a built in, regardless of when it is done I would > suggest either filing bug: > > https://www.postgresql.org/account/login/?next=/account/submitbug/ > <https://www.postgresql.org/account/login/?next=/account/submitbug/> > > > This is a poor recommendation given that its absence is not a bug. > > or > > post to the --hackers list: > > https://www.postgresql.org/list/pgsql-hackers/ > <https://www.postgresql.org/list/pgsql-hackers/> > > > Maybe not ideal but we don't have any kind of "feature requests" > lists so its either -hackers or -general. > > I'd even go so far as to say "-general" is better because you > get more eyes on the request for people to give it support and, > apparently in this case, finding people to do the leg work of > writing a patch. > > David J. > > > -- Adrian Klaver adrian.klaver@aklaver.com