PostgreSQL sub-query array return full rows instead of single column

Question

I have a query that looks like this:

SELECT
    s.*,
    array(
        SELECT m.id
        FROM swarm_machine sm
        JOIN machine m
        ON sm.machine_id = m.id
        WHERE swarm_id = s.id
    ) as machines
FROM swarm s
WHERE group_id = 48

Instead of returning just the machine ids SELECT m.id, i would like to return all the columns in the row instead SELECT m.*. PostgreSQL doesn't seem to allow this, but I was hoping that the community could help be figure it out.

I can always do this with 2 queries, but if PostgreSQL can do it for me I'd rather avoid the round trip.

SQL Fiddle: http://sqlfiddle.com/#!17/d7481/1

Gordon Linoff · Accepted Answer · 2019-11-13 15:32:10Z

2

Postgres allows it. Just refer to the record:

SELECT s.*,
       ARRAY(SELECT m
             FROM swarm_machine sm JOIN
                  machine m
                 ON sm.machine_id = m.id
             WHERE swarm_id = s.id
            ) as machines
FROM swarm s;

Here is a SQL Fiddle.

answered Nov 13, 2019 at 15:32

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL sub-query array return full rows instead of single column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related