How to use for-loop to feed array element into postgresql query?

Question

I would like to loop my query based on each element of an array. And I just want one result from each element out of the query.

Something like this:

array = ['a', 'b', 'c']

For i in array LOOP

SELECT *
FROM table_xyz
WHERE id = i
LIMIT 1

END LOOP;

So that I will have a table result like this:

id     | value
-------+--------
a      |  123
b      |  456
c      |  789

A cursor is more often than not a bad choice. It's a slow row-by-row processing whereas relational databases are optimized against doing set based operations. — user330315
– user330315, Commented Aug 26, 2019 at 5:26
@a_horse_with_no_name Thanks for the comments, I am kinda new to sql stuff. I am still trying to figuring those concepts here. — Nein
– Nein, Commented Aug 26, 2019 at 6:49

daShier · Accepted Answer · 2019-08-26 03:01:53Z

1

Why not get the results all at once:

    SELECT *
    FROM table_xyz
    WHERE id in ('a', 'b', 'c')

answered Aug 26, 2019 at 3:01

daShier

2,1062 gold badges10 silver badges15 bronze badges

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

4 Comments

Nein Over a year ago

Thanks. Because I have thousands of id, and there are a lot of data from each id in the table. So it will cost a lot of time in the query and a lot of work to filter the result.

daShier Over a year ago

Well running thousands of separate queries will cost quite a bit of time as well. Without more understanding of the actual application, it's hard to make a suggestion, but you might want to consider putting your thousands of ids in a table (even a temporary table) and perform a JOIN with that table. getting a single results set is generally preferable to running thousands of queries.

Nein Over a year ago

make sense, I will give it a try. Thank you.

user330315 Over a year ago

Or use the array directly: where id = any(array_variable)

Collectives™ on Stack Overflow

How to use for-loop to feed array element into postgresql query?

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related