How can i write a postgres function that loops over array of arrays and concatenates all values into a string

Question

I am trying to write a postgres function that takes an array of arrays (e.g. [[65.8434183140001,38.905535066],[65.8433595220001,38.905479615],[65.843286031,38.9054103],[65.843108611,38.9055251450001],[65.8432114970001,38.905622185],[65.8434183140001,38.905535066]] ) and concatenates all values into one string to which will be used as input to postgis LINESTRING.

Desired output from the function is: 65.8434183140001 38.905535066, 65.8433595220001 38.905479615, 65.843286031 38.9054103, 65.843108611 38.9055251450001, 65.8432114970001 38.905622185, 65.8434183140001 38.905535066

after reading many answers on SO and reading through many articles this is what I have come up with (I am new to postgres functions and procedures):

create or replace 
function flatten_points (points text[])
  returns text
  language sql 
  immutable 
as $func$
  DECLARE 
    flattened varchar[];
    p text[];
  BEGIN
    FOREACH p SLICE 1 IN ARRAY points
      LOOP
        RAISE NOTICE 'CONCAT(flattened, %,%)', m[1], m[2];
      END LOOP;
  END
  
  RETURN flattened
$func$;

Unfortunately this function did not even get created and postgres errors with syntax error. I am not sure what I am doing wrong.

How can I write my desired function. Any answers or suggestions are welcome.

P.S. If there is a way to create polygons directly from arrays using POSTGIS that would be marvelous

The obvious issue is `language sql ` and then `DECLARE` and `BEGIN/END`. Those keywords are only available in `plpgsql`. Change the `LANGUAGE` name. — Adrian Klaver, May 23 '23 at 17:58

score 2 · Accepted Answer · answered May 23 '23 at 18:10

You can use string_agg:

create or replace function flatten_points (points numeric[][]) returns text
as $$
    select string_agg((select string_agg(p::text, ' ') 
       from jsonb_array_elements(a) p), ', '::text) 
    from jsonb_array_elements(array_to_json(points)::jsonb) a
$$ language sql

Usage:

select flatten_points(array[array[65.8434183140001,38.905535066],array[65.8433595220001,38.905479615], array[65.843286031,38.9054103],array[65.843108611,38.9055251450001],array[65.8432114970001,38.905622185],array[65.8434183140001,38.905535066]])

See fiddle

How can i write a postgres function that loops over array of arrays and concatenates all values into a string

1 Answers1