| From: | Francois Payette <francoisp(at)netmosphere(dot)net> |
|---|---|
| To: | pgsql-sql(at)postgresql(dot)org |
| Subject: | create an index on unnest |
| Date: | 2013-02-06 18:15:42 |
| Message-ID: | 2C875257-053D-4C81-B3ED-2EC01DF38743@netmosphere.net |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | 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 duplicated rows 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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Dev Kumkar | 2013-02-06 19:19:21 | Re: Facing authentication error on postgres 9.2 -> dblink functions |
| Previous Message | Dev Kumkar | 2013-02-06 17:59:08 | Re: Facing authentication error on postgres 9.2 -> dblink functions |