BUG #10889: Cannot add 2 floats from regular expression

jakub.vrbas wrote

The following bug has been logged on the website:

Bug reference: 10889
Logged by: Jakub Vrbas
Email address:

jakub.vrbas@

PostgreSQL version: 9.1.13
Operating system: Debian
Description:

I have test_column (of type character varying). If I parse a float by
regular expression, it isn't possible to add it to another float from
regular expression.

Example:

SELECT
(regexp_matches(test_column, '([0-9\.]*)'))[1]::float
+
(regexp_matches(test_column, '([0-9\.]*)'))[1]::float
FROM test_table

Results in "ERROR: functions and operators can take at most one set
argument"

Example 2 is OK:
SELECT
float_column
+
float_column
FROM (
SELECT
(regexp_matches(test_column, '([0-9\.]*)'))[1]::float AS float_column
FROM test_table
) foo

regexp_matches() returns a SETOF text[]

If it is in a FROM then each row of the set gets doubled and a single row
per input is output.

If it is in the SELECT-list that doesn't work. You have to use a scalar
subquery to make it work.

SELECT
(SELECT (regexp_matches('1.3', '([0-9\.]*)'))[1])::float
+
(SELECT (regexp_matches('2.3', '([0-9\.]*)'))[1])::float
;

Suggest wrapping it in a function - or making a "regexp_matches_single"
function that behaves similarly but returns a single text[] instead of a
SETOF text[]

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-10889-Cannot-add-2-floats-from-regular-expression-tp5810748p5810751.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

David G Johnston <david.g.johnston@gmail.com> writes:

Suggest wrapping it in a function - or making a "regexp_matches_single"
function that behaves similarly but returns a single text[] instead of a
SETOF text[]

I wonder if we should have such a thing built-in. This isn't the first
complaint we've heard about the SETOF API being awkward to use, and it's
only needed if you specify the 'g' flag. Perhaps we could have a variant
that forbids 'g' and returns a non-set (either the single match, or NULL).

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Tom Lane-2 wrote

David G Johnston &lt;

david.g.johnston@

&gt; writes:

Suggest wrapping it in a function - or making a "regexp_matches_single"
function that behaves similarly but returns a single text[] instead of a
SETOF text[]

I wonder if we should have such a thing built-in. This isn't the first
complaint we've heard about the SETOF API being awkward to use, and it's
only needed if you specify the 'g' flag. Perhaps we could have a variant
that forbids 'g' and returns a non-set (either the single match, or NULL).

regards, tom lane

+1

Given that regexp_matches is in core having this most useful API makes
perfect sense to me. Parsing out components from data is an extremely
common use-case for regexp - one that I personally encounter much more often
than needing global matching behavior.

I agree that the presence of the 'g' flag when calling regexp_matches_single
(_once?) should emit an error.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-10889-Cannot-add-2-floats-from-regular-expression-tp5810748p5810756.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On 7/7/14 5:25 PM, David G Johnston wrote:

SELECT
(SELECT (regexp_matches('1.3', '([0-9\.]*)'))[1])::float
+
(SELECT (regexp_matches('2.3', '([0-9\.]*)'))[1])::float
;

Suggest wrapping it in a function - or making a "regexp_matches_single"
function that behaves similarly but returns a single text[] instead of a
SETOF text[]

In this case, it would be easier to use substring(string from pattern).

.marko

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs