Accessing composite type columns in indexes - Mailing list pgsql-general

Michael Fuhr's example of using composite types for date intervals/
ranges/periods prompted me to explore this a little further. While
doing so, it appears that one can't directly access the columns of a
composite type when creating an index, i.e., neither UNIQUE (foo.bar)
nor UNIQUE ((foo).bar) work. I was able to create indexes including
composite columns by creating functions that returned values from a
composite type parameter.

Is this expected?

Here are the details:

begin;
BEGIN
-- closed-open date interval [from_date, to_date)
create type date_co_interval as
(
     from_date date
     , to_date date
);
CREATE TYPE

create function co_begin(date_co_interval) returns date
strict
immutable
security definer
language plpgsql as '
declare
     i alias for $1;
begin
     return i.from_date;
end;
';
CREATE FUNCTION

-- convenience function
create function prior(date) returns date
strict
immutable
security definer
language plpgsql as '
declare
     p alias for $1;
begin
     return p - 1;
end;
';
CREATE FUNCTION

create function co_end(date_co_interval) returns date
strict
immutable
security definer
language plpgsql as '
declare
     i alias for $1;
begin
     return prior(i.to_date);
end;
';
CREATE FUNCTION

savepoint composite_dot;
SAVEPOINT

create table employment_history
(
     company text not null
     , during date_co_interval not null
     , unique (company, during.from_date, during.to_date)
);
ERROR:  syntax error at or near "." at character 129
LINE 5:     , unique (company, during.from_date, during.to_date)
                                      ^
rollback to savepoint composite_dot;
ROLLBACK

savepoint composite_parens;
SAVEPOINT

create table employment_history
(
     company text not null
     , during date_co_interval
     , unique (company, (during).from_date, (during).to_date)
);
ERROR:  syntax error at or near "(" at character 114
LINE 5:     , unique (company, (during).from_date, (during).to_date)
                                ^
rollback to savepoint composite_parens;
ROLLBACK

savepoint function_on_composite;
SAVEPOINT

create table employment_history
(
     company text not null
     , during date_co_interval not null
     , unique (company, co_begin(during), co_end(during))
);
ERROR:  syntax error at or near "(" at character 131
LINE 5:     , unique (company, co_begin(during), co_end(during))
                                        ^
rollback to savepoint function_on_composite;
ROLLBACK

savepoint parens_function_on_composite;
SAVEPOINT

create table employment_history
(
     company text not null
     , during date_co_interval not null
     , unique (company, (co_begin(during)), (co_end(during)))
);
ERROR:  syntax error at or near "(" at character 123
LINE 5:     , unique (company, (co_begin(during)), (co_end(during)))
                                ^
rollback to savepoint parens_function_on_composite;
ROLLBACK

create table employment_history
(
     company text not null
     , during date_co_interval not null
);
CREATE TABLE

savepoint composite_idx;
SAVEPOINT

create unique index employment_history_pkey_idx
on employment_history (company, during.from_date, during.to_date);
ERROR:  syntax error at or near "," at character 98
LINE 2: on employment_history (company, during.from_date, during.to_...
                                                         ^
rollback to savepoint composite_idx;
ROLLBACK

savepoint composite_parens_idx;
SAVEPOINT

create unique index employment_history_pkey_idx
on employment_history (company, (during).from_date, (during).to_date);
ERROR:  syntax error at or near "." at character 89
LINE 2: on employment_history (company, (during).from_date, (during)...
                                                 ^
rollback to savepoint composite_parens_idx;
ROLLBACK

create unique index employment_history_pkey_idx
on employment_history (company, co_begin(during), co_end(during));
CREATE INDEX

rollback;
ROLLBACK
select version();

version
------------------------------------------------------------------------
----------------------------------------------------------------------
PostgreSQL 8.1.3 on powerpc-apple-darwin8.4.0, compiled by GCC
powerpc-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc.
build 5247)
(1 row)

Michael Glaesemann
grzm myrealbox com