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