current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

This is my (perhaps usual for you) non-optimized solution:

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, non-optimized. I can't believe (!!) that PostgreSQL doesn't have an internal index for arrays ... ? But in this case, the question is how to directly access this index, where the GIN-like internal counter?

NOTE1: the solution above and the question is not the same as "how do you create an index by each element of an array?". Also not the same as "Can PostgreSQL index array columns?" because the function is for an isolated array, not for a table index for array fields.

NOTE2 (edited after answers): "array indexes" (more popular term) or "array subscripts" or "array counter" are terms that we can use in a semantic path to refer the "internal counter", the accumulator to the next array item. I see that no PostgreSQL command offer a direct access to this counter. As generate_series() function, the generate_subscripts() function is a sequence generator, and the performance is (best but) near the same. By other hand row_number() function offers a direct access to a "internal counter of rows", but it is about rows, not about arrays, and unfortunately the performance is worse.

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

PostgreSQL does provide dedicated functions to generate array subscripts:

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

Effectively it does almost the same as @Frank's query, just without subquery.
Plus it works with subscripts that do not start with 1.

Either solution works for for 1-dimensional arrays only! (Can easily be expanded to multiple dimensions.)

Function:

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[]);

More about array subscripts in this related question.

If you actually want normalized subscripts (starting with 1), I'd use:

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

That's almost the query you had already, just with array_length() instead of array_upper() - which would fail with non-standard subscripts.

Performance

I ran a quick test on an array of 1000 int with all queries presented here so far. They all perform about the same (~ 3,5 ms) - except for row_number() on a subquery (~ 7,5 ms) - as expected, because of the subquery.

share|improve this answer
    
Thanks! Well, there are two new solutions after generate_series()... What is the fasted: row_number() or generate_subscripts()? –  Peter Krauss Sep 3 '12 at 15:45
    
@PeterKrauss It depends on your use case (I mean test it with your data). generate_subscripts() is much more readable for me. I won't expect much difference when used on arrays of normal size, however. –  dezso Sep 3 '12 at 16:03
    
@PeterKrauss: I added an alternative solution, plus results of a quick performance test. –  Erwin Brandstetter Sep 3 '12 at 16:33
    
Ok, comparing explain analyse times with the "SELECT 1, unnest($1)" time, I have subscripts=109%, series=111%, and row_number 150%. Your solution with generate_subscripts() is the best (!), and my solution with generate_series() is not so wrong. –  Peter Krauss Sep 3 '12 at 18:15
    
@PeterKrauss: You were actually very close to begin with. –  Erwin Brandstetter Sep 3 '12 at 19:15
add comment
up vote 1 down vote

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;
share|improve this answer
    
Is it guaranteed to keep the order? –  Quassnoi Sep 3 '12 at 12:30
    
@Quassnoi: I didn't test it, and without a sort order, there is no guarantee –  Frank Heikens Sep 3 '12 at 12:31
    
Yes! I am testing, and I think array order is always preserved. Thank a lot, it is the solution. –  Peter Krauss Sep 3 '12 at 13:53
    
@PeterKrauss: I've approved your edit. Note that you could also answer you own question (see this thread) –  JMax Sep 3 '12 at 14:16
    
Wouldn't this fail for arrays with non-standard subscripts like '[4:7]={1,20,3,5}'::int[]? –  Erwin Brandstetter Sep 3 '12 at 14:46
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.