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}
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
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;
