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
