Postgresql SELECT sample with FOR loop

Question

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$

Gordon Linoff · Accepted Answer · 2021-06-01 13:03:12Z

1

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

edited Jun 1, 2021 at 13:03

answered Jun 1, 2021 at 11:59

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

kranj doo Over a year ago

I don't think the second one works. Seems to be missing ON statement for JOIN

Gordon Linoff Over a year ago

@kranjdoo . . . Actually, I intended CROSS JOIN LATERAL but typed the wrong thing.

Collectives™ on Stack Overflow

Postgresql SELECT sample with FOR loop

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related