Create array of values from sub-table

Question

For example I have table users(id, name, email). There is table photos (id, value) There is a table userphotos which consists of relations: user_id, photo_id.

I need a function to fetch all users with their photos in such a way:

id1 name1 email1 photos1

id2 name2 email2 photos2

etc.

Number of photos for a user is not known.

How to create plpgsql function to create such results?

CREATE OR REPLACE FUNCTION test_function()
  RETURNS TABLE(id int, name character varying, email character varying,  photos int[]) AS $
DECLARE
    u RECORD;
BEGIN
    FOR u IN SELECT * FROM users LOOP
        return NEXT u; -- will return user without photos
    -- but how to populate u with photos as array
    -- got from query:
    --          SELECT photo_id FROM userphotos WHERE user_id = u.id
    END LOOP;
END;
$ LANGUAGE plpgsql

PostgreSQL ver. 9.0.

Define "photos1" in the result. Do you one row per userphotos in the result or one list of photos per user? Represented by id or value?. Where does partners in the function come from? Add your version of PostgreSQL. — Erwin Brandstetter
– Erwin Brandstetter, Commented Jan 3, 2013 at 1:12
Sorry, 'partners' was from another example. I've corrected and added posgresql version — Eugene
– Eugene, Commented Jan 3, 2013 at 7:43

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

Might look something like this:

CREATE OR REPLACE FUNCTION test_function()
  RETURNS TABLE(id int, name varchar, email varchar, photos int[]) AS
$func$
BEGIN

RETURN QUERY
SELECT u.id, u.name, u.email, array_agg(up.photo_id)  -- AS photo_ids
FROM   users u
LEFT   JOIN userphotos up ON  up.user_id = u.id
GROUP  BY u.int, u.name, u.email;

END
$func$ LANGUAGE plpgsql

Use proper quoting. Read the manual about dollar-quoting.
Use RETURN QUERY as simplest way to return rows from a plpgsql function. No LOOP necessary.
The LEFT JOIN includes users who have no photos at all.
Beware of naming conflicts. All OUT parameters in the RETURNS TABLE clause are visible in the body everywhere.

Addendum after comment

If you want to join multiple 1:n tables, you can't join them all at once. This is what'd happen:
https://stackoverflow.com/a/12464135/939860

Another closely related answer with a solution for this problem:
https://stackoverflow.com/a/14110017/939860

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jan 3, 2013 at 1:24

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

2 Comments

Eugene Over a year ago

Thank you. It works. But I have problems if I have to do more than one JOIN. For example I also have to fetch user groups for every user from table usergroups. I add one more LEFT JOIN and I got duplicating results in array_agg(up.photo_id) column

Eugene Over a year ago

Thank you very much! Grouping inside JOIN it's what I needed

Collectives™ on Stack Overflow

Create array of values from sub-table

1 Answer 1

Addendum after comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Addendum after comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related