How to access the internal index of an array using postgreSQL?

Question

This is my (maybe usual for you) solution not optimized :

Workaround for PG problem with non-optimized internal function:

CREATE FUNCTION unnest_with_idx(anyarray) RETURNS TABLE(idx integer, val anyelement) AS $$ SELECT generate_series(1,array_upper($1,1)) as idx, unnest($1) as val; $$ LANGUAGE SQL IMMUTABLE; 

Test:

 SELECT idx,val from unnest_with_idx(array[1,20,3,5]) as t; 

But, as I said, not optimized. I cannot believe (!!) that PostgreSQL does not have an internal index for arrays ...? But in this case the question arises, how to get direct access to this index, where is the internal GIN-like counter?

NOTE 1. The solution above and the question does not coincide with " how do you create an index for each element of the array? ". Also doesn't match postgreSQL index column columns? "because the function is for an isolated array, not a table index for array fields.

---

NOTE 2 (edited after answers): “array indices” (the more popular term) or “array indices” or “array counter” are terms that we can use in the semantic path to refer to the “internal counter”, the next element of the array. I see that not the PostgreSQL team offers direct access to this counter . As the generate_series() function, the generate_subscripts() function is a sequence generator , and performance (best of all) is close to the same. On the other hand, the row_number() function offers direct access to the "internal row counter", but we are talking about strings, not arrays, and, unfortunately, performance is worse.

Answer 1

PostgreSQL provides special functions for creating array indices :

 WITH x(a) AS ( VALUES ('{1,20,3,5}'::int[]) ) SELECT generate_subscripts(a, 1) AS idx ,unnest(a) AS val FROM x; 

In fact, it is almost the same as the @Frank request, just without a subquery.
In addition, it works with indexes that do not start with 1 .

Or the solution works only for one-dimensional arrays! (It can be easily expanded to several dimensions.)

Functions:

 CREATE OR REPLACE FUNCTION unnest_with_idx(anyarray) RETURNS TABLE(idx integer, val anyelement) LANGUAGE SQL IMMUTABLE AS $func$ SELECT generate_subscripts($1, 1), unnest($1); $func$; 

Call:

 SELECT * FROM unnest_with_idx('{1,20,3,5}'::int[]); 

Also consider:

 SELECT * FROM unnest_with_idx('[4:7]={1,20,3,5}'::int[]); 

Read more about array indices in this related question .

If you really need normalized indexes (starting at 1), I would use:

 SELECT generate_series(1, array_length($1,1) ) ... 

This is almost a query that you already had, only with array_length() instead of array_upper() - which would not succeed with non-standard indexes.

Performance

I checked a quick test for an array of 1000 int with all the sentences requested here. They all do roughly the same thing (~ 3.5 ms) - with the exception of row_number() in the subquery (~ 7.5 ms) - as expected, due to the subquery.

Update: Postgres 9.4 +

If you are working with custom indexes, use the new WITH ORDINALITY :

• PostgreSQL unsest () with item number

Answer 2

row_number () works:

 SELECT row_number() over(), value FROM (SELECT unnest(array[1,20,3,5])) a(value); 

Then the optimized function will be

 CREATE OR REPLACE FUNCTION unnest_with_idx(anyarray) RETURNS table(idx integer, val anyelement) AS $$ SELECT (row_number() over())::integer as idx, val FROM (SELECT unnest($1)) a(val); $$ LANGUAGE SQL IMMUTABLE;