I'd like to know whether it is possible in PostgreSQL to output an array from multiple elements of an array by using another array indicating a series of positions for elements.
To be precise:
array1 = [1,2,1]
array_positions(array1,'1') = {1,3}
array2 = ['hello', 'bye', 'hello']
Desired result:
array2[array_positions(array1,'1')] = {'hello', 'hello'}
I receive ERROR: array subscript must have type integer
Any suggestions?
The missing part is a function/operator that returns array elements from specified positions (I really surprised that it is absent in the release, hope it will be fixed). Lets create them:
create or replace function array_from_positions(anyarray, int[]) returns anyarray
immutable language sql
as $$
select array_agg(a.x order by b.i)
from
unnest($1) with ordinality as a(x,i)
right join unnest($2) with ordinality as b(x,i) on (a.i=b.x)
$$;
Test:
select
array_from_positions('{a,b,c,d}'::text[], '{2,1,10}'),
array_from_positions('{1,2,3,4}'::int[], '{2,1,10}');
┌──────────────────────┬──────────────────────┐
│ array_from_positions │ array_from_positions │
├──────────────────────┼──────────────────────┤
│ {b,a,NULL} │ {2,1,NULL} │
└──────────────────────┴──────────────────────┘
create operator ## (
procedure = array_from_positions,
leftarg = anyarray,
rightarg = int[] );
Test:
select
'{a,b,c,d}'::text[] ## '{2,1,10}',
'{1,2,3,4}'::int[] ## '{2,1,10}';
┌────────────┬────────────┐
│ ?column? │ ?column? │
├────────────┼────────────┤
│ {b,a,NULL} │ {2,1,NULL} │
└────────────┴────────────┘
And final test for your example:
with your_table(array1,array2) as (values
('{1,2,1}'::int[], '{hello,bye,hello}'::text[]))
select array2 ## array_positions(array1, 1) from your_table;
┌───────────────┐
│ ?column? │
├───────────────┤
│ {hello,hello} │
└───────────────┘
This may or may not be too complicated to be useful, but it does prove it's possible:
SELECT vals.*
FROM (
VALUES(array['hello', 'bye', 'hello'])
) AS t (arr)
JOIN lateral unnest(t.arr) WITH ORDINALITY AS vals(element, idx) ON TRUE
WHERE vals.idx = ANY(array_positions(array[1,2,1], 1));
