Обсуждение: BUG #10889: Cannot add 2 floats from regular expression

The following bug has been logged on the website:

Bug reference:      10889
Logged by:          Jakub Vrbas
Email address:      jakub.vrbas@inspire.cz
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

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.

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

Tom Lane-2 wrote
> David G Johnston <

> david.g.johnston@

> > 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.

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