Thread: get the array value?

name[] = { JOHN , ALEX , TEST ,""}

SQL : select name from table1 where 'TEST' = any (name)

return: { JOHN , ALEX , TEST }

in this sql command, how can i get the index of 'TEST' is 3 ?

On Thu, Dec 25, 2008 at 7:15 AM, Charles.Hou <ivan.hou@msa.hinet.net> wrote:
> name[] = { JOHN , ALEX , TEST ,""}
>
> SQL : select name from table1 where 'TEST' = any (name)
>
> return: { JOHN , ALEX , TEST }
>
> in this sql command, how can i get the index of 'TEST' is 3 ?

First of all. I assume the code above is meant to be pseudo-code, otherwise
this makes not much sense to me.

But if I understand you correctly, that you want to find the index (or position)
of a specific item in an array, then you have to write a function that iterates
over the array and returns the index.

This is a bad design however and it doesn't scale up well with large arrays.

A better approach is storing the array elements as rows in a table with an
index, which can be queried more efficiently.

For example:

CREATE TABLE records (
 id SERIAL PRIMARY KEY
);

CREATE TABLE names (
 record_id INTEGER REFERENCES records,
 position INTEGER NOT NULL,
 name TEXT NOT NULL,
 UNIQUE (record_id, position)
);

This way you can easily search by doing something like

SELECT position FROM names
WHERE name = 'TEST';

Regards,
Victor Nawothnig

2008/12/25 Victor Nawothnig <victor.nawothnig@gmail.com>:
> On Thu, Dec 25, 2008 at 7:15 AM, Charles.Hou <ivan.hou@msa.hinet.net> wrote:
>> name[] = { JOHN , ALEX , TEST ,""}
>>
>> SQL : select name from table1 where 'TEST' = any (name)
>>
>> return: { JOHN , ALEX , TEST }
>>
>> in this sql command, how can i get the index of 'TEST' is 3 ?
>
> First of all. I assume the code above is meant to be pseudo-code, otherwise
> this makes not much sense to me.
>
> But if I understand you correctly, that you want to find the index (or position)
> of a specific item in an array, then you have to write a function that iterates
> over the array and returns the index.
>
> This is a bad design however and it doesn't scale up well with large arrays.
>
> A better approach is storing the array elements as rows in a table with an
> index, which can be queried more efficiently.
>
> For example:
>
> CREATE TABLE records (
>  id SERIAL PRIMARY KEY
> );
>
> CREATE TABLE names (
>  record_id INTEGER REFERENCES records,
>  position INTEGER NOT NULL,
>  name TEXT NOT NULL,
>  UNIQUE (record_id, position)
> );
>
> This way you can easily search by doing something like
>
> SELECT position FROM names
> WHERE name = 'TEST';
>
> Regards,
> Victor Nawothnig

I absolutely agree with Victor, arrays doesn't supply normalization
(but in some cases arrays are very useful). You can write SQL function
IndexOf (for small arrays):

postgres=# create or replace function indexof(anyarray, anyelement)
                   returns integer as $$
                      select i
                         from
generate_series(array_lower($1,1),array_upper($1,1)) g(i)
                        where $1[i] = $2 limit 1;
                   $$ language sql immutable;
CREATE FUNCTION
postgres=# select indexof(array['Pavel','Jirka'],'Jirka');
 indexof
---------
       2
(1 row)

Regards
Pavel Stehule


>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>