How can I join an array with a table in PostgreSQL-CodePudding

I created the following array and I want to join this with CTE of a totally different table

   CREATE TABLE places (
    name            text,
    labels         text[]
);

INSERT INTO places
    VALUES ('Places',
    ARRAY['store', 'hospital', 'home']);

I want to pass these constant values into my select statement.

    SELECT 
        e.address

    FROM env e

so It can be like

SELECT 
'Cashier.' ||places[1] ||'.'|| e.address,
'Doctor.' ||places[2] || '.'||e.address,
'Wife.' ||places[3] ||'.'||e.address

FROM env e

and it will be shown as

Cashier.store.NY
Doctor.hospital.DC
Wife.house.CA

in the resulting table

I couldn't find anything in the documentation. There are no mutual ids I can join this array on the table with.

I was wondering how this can be done/ or is it even possible or do I have to alter the table and add a column instead of using an array?

CodePudding user response：

You can use CROSS JOIN, try something like :

SELECT 
'Cashier.' || p.labels[1] ||'.'|| e.address,
'Doctor.' || p.labels[2] || '.'|| e.address,
'Wife.' || p.labels[3] ||'.'|| e.address
FROM env e
CROSS JOIN places AS p
WHERE p.name = 'Places'

CodePudding user response：

Maybe something like this?

CREATE TABLE env (
    name   varchar(30) primary key,
    label  text[]
);

INSERT INTO env (name, label) VALUES
('Places', ARRAY['store', 'hospital', 'home']);

CREATE TABLE data (
    id      serial primary key,
    address text[]
);

INSERT INTO data (address) VALUES (ARRAY['store 1', 'hospital 1', 'home 1'])

SELECT id, 
CONCAT_WS('.', 'Cashier', place.label[1], e.address[1]) AS place1,
CONCAT_WS('.', 'Doctor' , place.label[2], e.address[2]) AS place2,
CONCAT_WS('.', 'Husband', place.label[3], e.address[3]) AS place3
FROM data AS e
JOIN env AS place ON place.name = 'Places'

id | place1                | place2                     | place3             
-: | :-------------------- | :------------------------- | :------------------
 1 | Cashier.store.store 1 | Doctor.hospital.hospital 1 | Husband.home.home 1

db<>fiddle here

But it's normally not done via arrays.