Thread: 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