create an index on unnest
От | Francois Payette |
---|---|
Тема | create an index on unnest |
Дата | |
Msg-id | 2C875257-053D-4C81-B3ED-2EC01DF38743@netmosphere.net обсуждение исходный текст |
Ответы |
Re: create an index on unnest
|
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: