How select random items by given categories in postgres

Question

I have a table with items with category id-s and I want to create a function that gets category ids array and returns an array of random items with corresponding categories. Here is what I've tried

CREATE OR REPLACE FUNCTION public.get_random_items(IN args uuid[])
    RETURNS SETOF items
    LANGUAGE 'plpgsql'
    VOLATILE
    PARALLEL UNSAFE
    COST 100    ROWS 1000 
    
AS $BODY$
DECLARE 
    cat_id uuid;
    output items[];
BEGIN
 FOREACH cat_id IN ARRAY args LOOP
    EXECUTE (
        SELECT *
        FROM items
        WHERE category_id = cat_id
        ORDER BY random()
        LIMIT 1
    ) INTO output;
    END LOOP;
    RETURN QUERY SELECT * FROM output;
END $BODY$;

And when running - SELECT * FROM get_random_items('{d59f2779-4868-439c-96eb-b5a735135379}'). I get this error - ERROR: subquery must return only one column. Any ideas on how can I achieve this?

score 1 · Answer 1 · answered Apr 09 '21 at 14:51

If you want one random item per category, you can use:

SELECT DISTINCT ON (i.category_id) i.*
FROM items
ORDER BY i.category_id, random();

To put this in an array, use aggregation:

SELECT ARRAY_AGG(i)
FROM (SELECT DISTINCT ON (i.category_id) i.*
      FROM items i
      ORDER BY i.category_id, random()
     ) i;

I'm not sure if you really need to put this in a function, but you can if you like.

How select random items by given categories in postgres

1 Answers1