how to use order by array_position in union

Question

I've got a view with a union like this


CREATE OR REPLACE VIEW public.view
AS SELECT concat('B', bms.id::text) AS id,
    'sample1'::text AS sample_type,
    bms.id AS sample_id,
    NULL::bigint AS other_sample_id,
    bms.sample_type::text as biological_kind
   FROM sample bms
UNION
 SELECT concat('G', gms.id::text) AS id,
    'sample2'::text AS sample_type,
    NULL::bigint AS sample_id,
    gms.id AS other_sample_id,
    concat(gms.sample_type,'_', bms.sample_type)::text as biological_kind
   FROM other_sample gms
   join sample bms on bms.id = gms.source_sample
   order by array_position(array[values from biological_kind], biological_kind)

The idea is that I would like to have the result ordered by array posittion specified by me for example if I have values like "A", "B", "C", "Z" in biological_kind variable I would like to do something like

order by array_position(array["Z","A","C","B"], biological_kind)

but i got an error that it is not possible to use a function with union Is there any solution for this kind of situation? The other option would be to add additional column with a long switch case and then give some number based on the value of bliological_kind and use normal order by new_column(with numbers) but I would rather not use this solution as it doesn't seem professional to me

Does this solve Your problem? https://stackoverflow.com/questions/4088532/custom-order-by-explanation — Roman Hocke, Dec 01 '22 at 09:11
not really because there they are not using unions and the problem is with using array_position in Union. And the solution given there is to use array_position :) — mario, Dec 01 '22 at 09:13

score 0 · Answer 1 · answered Dec 01 '22 at 09:44

Ok I found the solution. The union should be wrapped as

CREATE OR REPLACE VIEW public.view
AS select * from (
SELECT concat('B', bms.id::text) AS id,
    'sample1'::text AS sample_type,
    bms.id AS sample_id,
    NULL::bigint AS other_sample_id,
    bms.sample_type::text as biological_kind
   FROM sample bms
UNION
 SELECT concat('G', gms.id::text) AS id,
    'sample2'::text AS sample_type,
    NULL::bigint AS sample_id,
    gms.id AS other_sample_id,
    concat(gms.sample_type,'_', bms.sample_type)::text as biological_kind
   FROM other_sample gms
   join sample bms on bms.id = gms.source_sample
) t
   order by array_position(array[values from biological_kind], t.biological_kind)

how to use order by array_position in union

1 Answers1