Skip to content
Advertisement

Postgresql SELECT sample with FOR loop

I have a task where I need to create a 1000 sample row limit query for each attribute selected.

So for example if I have:

SELECT * FROM data_table WHERE atribut in ('atrib1', 'atrbi2','atrib3');

I would need to turn this in to:

(SELECT * FROM data_table WHERE atribut = 'atrib1'  
ORDER BY random() 
LIMIT 1000) 

UNION   

(SELECT * FROM data_table WHERE atribut = 'atrib2' 
ORDER BY random() 
LIMIT 1000)

UNION

(SELECT * FROM data_table WHERE atribut = 'atrib3' 
ORDER BY random() 
LIMIT 1000);

Problem is that I have a lot of these attributes and that I might be adding more attributes later. Is there a better way of writing this? Like maybe with a FOR loop that iterates through an array of strings to get everything at once? I tried the following but I don’t really know how to do it properly:

DO
$do$
DECLARE
   atribut_array text[] := array['atrib1', 'atrib2', 'atrib3'];
   atr text;
BEGIN
    FOREACH atr IN ARRAY atribut_array 
    LOOP    
        SELECT  *
        FROM data_table 
        WHERE atribut LIKE atr
        ORDER BY random() 
        LIMIT 1000  
    END LOOP;
END
$do$

Advertisement

Answer

You can use row_number():

SELECT t.*
FROM (SELECT t.*,
             ROW_NUMBER() OVER (PARTITION BY attribute ORDER BY random()) as seqnum
      FROM data_table t
      WHERE attribute in ('atrib1', 'atrbi2', 'atrib3')
     ) t
WHERE seqnum <= 1000;

You could also use a lateral join:

SELECT t1000.*
FROM (VALUES ('atrib1'), ('atrbi2'), ('atrib3')
     ) v(attribute) CROSS JOIN LATERAL
     (SELECT t.*
      FROM data_table t
      WHERE t.attribute = v.attribute
      ORDER BY random()
      LIMIT 1000
     ) t1000
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement