Thread: Create index on subfield returned by function that returns base type with sub fields

Hello,

Is is possible to create an index on a field on a function that returns a data type that contains subfields?

It is possible to do this:
create index indx_test
on address
(sp_address_text_to_template(address_text))
where (sp_address_text_to_template(address_text)).city_name = 'some_city_on_some_planet';

,but, I would like to create the index without the partial clause to index results only from sp_address_text_to_template(address_text)).city_name.

Is this possible? How would I write the statement?

Thanks,

Desmond.

Hi,

On Thursday 23 December 2010 17:53:24 Desmond Coertzen wrote:
> Is is possible to create an index on a field on a function that returns a
> data type that contains subfields?
> Is this possible? How would I write the statement?
I am not sure I understood you correctly. Maybe you mean something like that:

test=# CREATE FUNCTION blub(IN int, OUT a int, OUT b int) RETURNS record
IMMUTABLE LANGUAGE sql AS $$SELECT $1+1, $1+2$$;
CREATE FUNCTION
Time: 1.665 ms

test=# CREATE INDEX foo__blub ON foo (((blub(data)).a));
CREATE INDEX
Time: 86.393 ms

Btw, this is the wrong list for this sort of question. The right place would
be -general.

Andres