From: Francois Payette <francoisp@netmosphere.net>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Subject: create an index on unnest
Date: Wed, 6 Feb 2013 13:15:42 -0500
Message-Id: <2C875257-053D-4C81-B3ED-2EC01DF38743@netmosphere.net>
To: pgsql-sql@postgresql.org
Mime-Version: 1.0 (Apple Message framework v1283)
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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 a=
nd 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 =3D array_fill(''::text, ARRAY[1]);
result[0] =3D $1 || '-one';
result[1] =3D $1 || '-two';
RETURN result;
END;

$$ LANGUAGE plpgsql;

create index test_idx on test (unnest(testfct(data)));

any suggestions?
TIA,
F


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