Split string with two delimiters and convert type

plpgsql, postgresql, set-returning-functions

Solution

If you need the intermediary step:

SELECT string_to_table(a, ' ')::float8
       -- or do something else with the derived table
FROM   string_to_table('3.584731 60.739211,3.590472 60.738030', ',') a;

This is faster than `regexp_split_to_table()`. Regular expressions are powerful but expensive. Test with `EXPLAIN ANALYZE`.

I first split at `','`, and next at `' '` - the reversed sequence of what you describe seems adequate.

If need be, you can wrap this into a PL/pgSQL function:

CREATE OR REPLACE FUNCTION public.split_string(_str text
                                             , _delim1 text = ','
                                             , _delim2 text = ' ')
  RETURNS SETOF float8
  LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE AS
$func$
BEGIN
   RETURN QUERY
   SELECT string_to_table(a, _delim2)::float8
   FROM   string_to_table(_str, _delim1) a;
END
$func$;

Or as plain SQL function:

CREATE OR REPLACE FUNCTION public.split_string(_str text
                                             , _delim1 text = ','
                                             , _delim2 text = ' ')
  RETURNS SETOF float8
  LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$func$
SELECT string_to_table(a, _delim2)::float8
FROM   string_to_table(_str, _delim1) a;
$func$;

Declare it `IMMUTABLE` (which it is) to allow performance optimization and other uses. Declare it `PARALLEL SAFE` (which it is) to not disable parallel query plans.

Call (using the provided defaults for `_delim1` and `_delim2`):

SELECT * FROM split_string('3.584731 60.739211,3.590472 60.738030');

Or with custom delimiters:

SELECT * FROM split_string('3.584731 60.739211,3.590472 60.738030', ',', ' ');

Fastest

For top performance, combine `translate()` with `string_to_table(...)`:

SELECT string_to_table(
          translate('3.584731 60.739211,3.590472 60.738030', ' ', ',')
        , ','
          ))::float8;

In Postgres 13 or older use `unnest(string_to_array())` instead of `string_to_table()`.

Problem

I have a PL/pgSQL function like this (thanks to the guy who made this possible): ``` CREATE OR REPLACE FUNCTION public.split_string(text, text) RETURNS SETOF text LANGUAGE plpgsql AS $function$ DECLARE pos int; delim_length int := length($2); BEGIN WHILE $1 <> '' LOOP pos := strpos($1,$2); IF pos > 0 THEN RETURN NEXT substring($1 FROM 1 FOR pos - 1); $1 := substring($1 FROM pos + delim_length); ELSE RETURN NEXT $1; EXIT; END IF; END LOOP; RETURN; END; $function$ ``` It splits a string with a delimiter. Like this: ``` select * from split_string('3.584731 60.739211,3.590472 60.738030,3.592740 60.736220', ' '); "3.584731" "60.739211,3.590472" "60.738030,3.592740" "60.736220" ``` How can I save the results in a temp_array or temp_table. So I can get the the results in temp_x and split up these points again. Like: ``` "3.584731" "60.739211" "3.590472" "60.738030" "3.592740" "60.736220" ``` and return the values as `double precision`. And all of this should be done in the function.

Original source