Request to add feature to the Position function
<div dir='rtl'><div>
<div>Hi,</div>
<div>position(<tt>substring</tt> in <tt>string</tt>)</div>
<div>as listed here:</div>
<div><a href="https://www.postgresql.org/docs/9.1/static/functions-string.html">https://www.postgresql.org/docs/9.1/static/functions-string.html</a></div>
<div>locates sub string in a string.</div>
<div> </div>
<div>It doesn't support locateing the substring from the back.</div>
<div> </div>
<div>For example:</div>
<div> </div>
<div>position('om' in 'Tomomas')</div>
<div>gives 2</div>
<div> </div>
<div>But if I want to locate the first occurance from the back of the string it's impossible/</div>
<div> </div>
<div>My suggestion is to create a function </div>
<div>position(<tt>substring</tt> in <tt>string,order</tt>)</div>
<div>where order can be: begin, end</div>
<div> </div>
<div>and it will find the string according to this parameter.</div>
<div>This is pretty easy to implement and should be a part of the PostgreSQL tools.</div>
<div> </div>
<div>similar fuctionality exists in trim function where user can specify leading or taling parameter</div>
</div></div>
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 2But 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, endand 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)
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 2But 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
----------
4My suggestion is to create a function
position(substring in string,order)
where order can be: begin, endand 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Mon, Mar 27, 2017 at 9:03 AM, Brian Dunavant <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.
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 2But 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
----------
4My suggestion is to create a function
position(substring in string,order)
where order can be: begin, endand 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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?
----------
4On 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 2But 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
----------
4My suggestion is to create a function
position(substring in string,order)
where order can be: begin, endand 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Mon, Mar 27, 2017 at 9:16 AM, 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
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
<div><div dir="rtl"><div>
<div>Here is a refer to the stackoverflow question:<br><br></div>
<div><a href="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</a></div>
<div> </div>
<div>it carry another example and the solution however I still think that this fuctionality should be avliable in a build-in postgresql function.</div>
<div> </div>
<br><br></div></div><section class="cust_msg_end"></section><blockquote style="margin:0;margin-bottom:20px;border-top:1px solid #e0e0e0;"><br>ב מרץ 27, 2017 19:58, Adrian Klaver כתב:<blockquote style="margin:0;margin-bottom:20px;border-top:1px solid #e0e0e0">On 03/27/2017 09:15 AM, David G. Johnston wrote:<br>> On Mon, Mar 27, 2017 at 9:03 AM, Brian Dunavant <brian@omniti.com<br>> <mailto:brian@omniti.com>>wrote:<br>><br>> That does not return the correct answer for the original poster's<br>> request.<br>><br>> flpg=#<br>> <br>> select position('om' in reverse('Tomomasaaaaaaaaaaa'));<br>> position<br>> ----------<br>> 15<br>> (1 row)<br>><br>><br>> Easy oversight to make - if you reverse the string being searched you<br>> must also reverse the string that you are looking for.<br>><br>> That said the OP hasn't actually defined what they mean by "locate" when<br>> considered in a backward sense - some length adjustments might be needed<br>> to compensate for the length of the term being searched for.<br><br>I think there is also a RTL language involved, so we may need more <br>information to answer the question.<br><br>><br>> Patches are welcomed. Given the lack of any links to previous requests<br>> of this nature I'd say its not exactly a highly in-demand capability.<br>><br>> David J.<br>><br><br><br>-- <br>Adrian Klaver<br>adrian.klaver@aklaver.com<br><br></mailto:brian@omniti.com></brian@omniti.com<br><br></blockquote></blockquote><br></div>
Import Notes
Resolved by subject fallback
On 03/28/2017 12:29 AM, Ron Ben wrote:
Here is a refer to the stackoverflow question:
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Tue, Mar 28, 2017 at 7:09 AM, Adrian Klaver <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-postgresqlit 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/
This is a poor recommendation given that its absence is not a bug.
or
post to the --hackers list:
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.
<div><div dir="rtl"><div>I never expected it to be implemented now.. I understand that there are policies and priorities<br><br></div>
<div>I tried to find a feature request chanle but there is none...</div>
<div>I don't know how the postresql team decied what is on the "to do list" and what is not.</div>
<div>This is a feature which I think people will find usefull and it doesn't seem like a lot of work to implement it.</div></div><section class="cust_msg_end"></section><blockquote style="margin:0;margin-bottom:20px;border-top:1px solid #e0e0e0;"><br>ב מרץ 28, 2017 19:42, David G. Johnston כתב:<blockquote style="margin:0;margin-bottom:20px;border-top:1px solid #e0e0e0"><div dir="ltr"><div style="font-family:arial,helvetica,sans-serif"><span style="font-family:arial,sans-serif">On Tue, Mar 28, 2017 at 7:09 AM, Adrian Klaver </span><span dir="ltr" style="font-family:arial,sans-serif"><<a href="mailto:adrian.klaver@aklaver.com" target="_blank">adrian.klaver@aklaver.com</a>></span><span style="font-family:arial,sans-serif"> wrote:</span><br></div><div><div><blockquote style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><span class="">On 03/28/2017 12:29 AM, Ron Ben wrote:<br>
<blockquote style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Here is a refer to the stackoverflow question:<br>
<br>
<a href="http://stackoverflow.com/questions/42974822/find-sub-string-position-from-the-end-of-string-in-postgresql" rel="noreferrer" target="_blank">http://stackoverflow.com/quest<wbr>ions/42974822/find-sub-string-<wbr>position-from-the-end-of-<wbr>string-in-postgresql</a><br>
<br>
it carry another example and the solution however I still think that<br>
this fuctionality should be avliable in a build-in postgresql function.<br>
<br>
</blockquote>
<br></span>
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).</blockquote><div><br></div><div><div style="font-family:arial,helvetica,sans-serif">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.</div></div><div> </div><blockquote style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"> 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:<br>
<br>
<a href="https://www.postgresql.org/account/login/?next=/account/submitbug/" rel="noreferrer" target="_blank">https://www.postgresql.org/acc<wbr>ount/login/?next=/account/subm<wbr>itbug/</a><br>
<br></blockquote><div><br></div><div><div style="font-family:arial,helvetica,sans-serif">This is a poor recommendation given that its absence is not a bug.</div></div><div><br></div><blockquote style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
or<br>
<br>
post to the --hackers list:<br>
<br>
<a href="https://www.postgresql.org/list/pgsql-hackers/" rel="noreferrer" target="_blank">https://www.postgresql.org/lis<wbr>t/pgsql-hackers/</a></blockquote><div><br></div><div style="font-family:arial,helvetica,sans-serif">Maybe not ideal but we don't have any kind of "feature requests" lists so its either -hackers or -general.</div><div style="font-family:arial,helvetica,sans-serif"><br></div><div style="font-family:arial,helvetica,sans-serif">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.</div><div style="font-family:arial,helvetica,sans-serif"><br></div><div style="font-family:arial,helvetica,sans-serif">David J.</div><div style="font-family:arial,helvetica,sans-serif"><br></div></div></div></div>
<br></blockquote></blockquote><br></div>
Import Notes
Resolved by subject fallback
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 prioritiesI 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general