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:

1. id1 name1 email1 photos1
2. 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.

Answer 1

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