Re: create an index on unnest

well sorry for bothering this list and answering myself,  I just came across the array operators.

this does the trick
drop table  if exists test;
create table test (id serial, data text );
insert into test VALUES(DEFAULT, 'testdata');
drop function if exists testfct();
CREATE OR REPLACE FUNCTION testfct(text) RETURNS text[] AS $$
DECLARE
result text[];
BEGIN
result = array_fill(''::text, ARRAY[1]);
result[0] = $1 || '-one';
result[1] = $1 || '-two';
RETURN result;
END;

$$ LANGUAGE plpgsql IMMUTABLE;
select unnest(testfct(data)) from test;
create index test_idx on test USING GIN ((testfct(data)));

SET enable_seqscan TO off;
EXPLAIN ANALYZE select * from test where testfct(data) @> ARRAY['testdata-one'];

this does the trick.
cheers,
F


On 2013-02-06, at 1:15 PM, Francois Payette wrote:

> Greetings!
>
> I need the following: create an index on multiple values for a single row. My other solution consists of a lot of
duplicatedrows in seperate tables and triggers and indexes, resulting in slower performance. 
> The following fails on 9.2, it says ERROR:  index expression cannot return a set.
>
> drop table  if exists test;
> create table test (id serial, data text );
> insert into test VALUES(DEFAULT, 'testdata');
> drop function if exists testfct();
> CREATE OR REPLACE FUNCTION testfct(text) RETURNS text[] AS $$
> DECLARE
> result text[];
> BEGIN
> result = array_fill(''::text, ARRAY[1]);
> result[0] = $1 || '-one';
> result[1] = $1 || '-two';
> RETURN result;
> END;
>
> $$ LANGUAGE plpgsql;
>
> create index test_idx on test (unnest(testfct(data)));
>
> any suggestions?
> TIA,
> F
>