On Thu, Dec 25, 2008 at 7:15 AM, Charles.Hou <ivan....@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
