SELECT an array of data from a table in the order I need

nakem 0
Viewed 32

Let 's imagine such a table

create table test
(
    main_id   uuid,
    first_id uuid,
    second_id uuid
);

there are such ids

           main_id                           first_id                 second_id

"00000000-0000-0000-0000-000000000011"       "...11"                    "...11"
           "...12"                           "...12"                    "...12"
           "...13"                           "...13"                    "...13"

i have a query

SELECT main_id
FROM test
WHERE (first_id, second_id) IN (SELECT UNNEST(@first_ids::uuid[]), UNNEST(@second_ids::uuid[]));

i'm sending first_ids = ("...13", "...11", "...12"), second_ids = ("...13", "...11", "...12")

Expected: main_id = ("...13", "...11", "...12")

Actual: I get the result in random order (in the same order as in the database)

database sql postgresql
1 Answers

You could do something like

SELECT (
SELECT main_id
FROM test
WHERE first_id = @first_ids[i]::uuid
AND
second_id = @second_ids[i]::uuid
)
FROM
generate_series(1,cardinality(@first_ids)) i
ORDER BY i

I don't know if my casting there is right, and I'm not sure if the ORDER BY is actually necessary when you're using generate_series, but the idea here is to map over your arrays using [i] so that you can keep your place.

histocrat0
Related