Suppose that I have a table with the following two columns:

create table contacts (
    first_name varchar[],
    last_name varchar[]
);

And I have the following two rows:

INSERT INTO contacts (first_name, last_name)
VALUES (ARRAY['Samin'] , Array['Var']),
(ARRAY['Sara', 'pooya'] , Array['Rad', 'Mohammadi']);

select * from contacts;

I want to do a query that results in the following output:

#row1: {Samin-Var}
#row2: {Sara-Rad, pooya-Mohammadi}

CodePudding user response：

You can use a lateral join that turns the arrays into rows and aggregates them back into a the names you want:

select n.names
from contacts c
  cross join lateral (
     select array_agg(concat_ws('-', x.first_name, x.last_name) order by x.nr) as names
     from unnest(c.first_name, c.last_name) with ordinality as x(first_name, last_name, nr)
  ) as n

Online example

CodePudding user response：

This is can done using a self-defined function in PostgreSQL.

CREATE OR REPLACE FUNCTION merge_names(firsts varchar[], lasts varchar[])
RETURNS varchar[] AS
$$
DECLARE m varchar[];
    BEGIN
        FOR i IN 1..cardinality(firsts)
        LOOP
            m[i]:= firsts[i] || '-' || lasts[i];
        END LOOP;
        RETURN m;
    END;
$$ LANGUAGE plpgsql;

Here we assume that the lengths of firsts and lasts are the same.

Usage example:

SELECT merge_names(first_name, last_name)
FROM contacts;