From: Francois Payette <francoisp@netmosphere.net>
To: pgsql-sql@postgresql.org
Subject: create an index on unnest
Date: Wed, 6 Feb 2013 13:15:42 -0500
Message-ID: <2C875257-053D-4C81-B3ED-2EC01DF38743@netmosphere.net> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: francoisp@netmosphere.net
Subject: Re: create an index on unnest
In-Reply-To: <2C875257-053D-4C81-B3ED-2EC01DF38743@netmosphere.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox