pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcreate an index on unnest
2+ messages / 1 participants
[nested] [flat]
* create an index on unnest
@ 2013-02-06 18:15 Francois Payette <francoisp@netmosphere.net>
2013-02-06 19:37 ` Re: create an index on unnest Francois Payette <francoisp@netmosphere.net>
0 siblings, 1 reply; 2+ messages in thread
From: Francois Payette @ 2013-02-06 18:15 UTC (permalink / raw)
To: 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: create an index on unnest
2013-02-06 18:15 create an index on unnest Francois Payette <francoisp@netmosphere.net>
@ 2013-02-06 19:37 ` Francois Payette <francoisp@netmosphere.net>
0 siblings, 0 replies; 2+ messages in thread
From: Francois Payette @ 2013-02-06 19:37 UTC (permalink / raw)
To: pgsql-sql
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 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2013-02-06 19:37 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-02-06 18:15 create an index on unnest Francois Payette <francoisp@netmosphere.net>
2013-02-06 19:37 ` Francois Payette <francoisp@netmosphere.net>
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